I’d love to have a Data Explorer Query for all of the topics in a category and the number of votes on each. I am new to SQL queries and couldn’t find anything existing that looked close enough for me to tweak. Ideally, it would look like this: topic votes replies tags closed example topic 1 85 62 web, ios, android FALSE example topic 2 79 45 web TRUE Can someone help me with this? Apologies if this is the wrong place. It seemed like the closest existing topic.

Hello and welcome @CarrieSeltzer :slight_smile: Would something like this work for you? -- [params] -- int :category_id WITH tag_names AS ( SELECT t.id topic_id, string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags" FROM topics t JOIN topic_tags tt ON tt.topic_id = t.id JOIN tags ON tags.id = tt.tag_id GROUP BY t.id ) SELECT t.created_at::date, dvtvc.topic_id, votes_count, t.posts_count, tn.tags, t.closed FROM discourse_voting_topic_vote_count dvtvc JOIN topics t ON t.id = dvtvc.topic_id LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id WHERE t.category_id = :category_id AND votes_count > 0 ORDER BY t.created_at DESC [image]

Yes, thank you! This is great! I just need one minor tweak—how do I get it to display the name of the topic in the csv export (not just the topic id)?

I think adding t.title to the SELECT should include that: -- [params] -- int :category_id WITH tag_names AS ( SELECT t.id topic_id, string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags" FROM topics t JOIN topic_tags tt ON tt.topic_id = t.id JOIN tags ON tags.id = tt.tag_id GROUP BY t.id ) SELECT t.created_at::date, dvtvc.topic_id, t.title, votes_count, t.posts_count, tn.tags, t.closed FROM discourse_voting_topic_vote_count dvtvc JOIN topics t ON t.id = dvtvc.topic_id LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id WHERE t.category_id = :category_id AND votes_count > 0 ORDER BY t.created_at DESC There is also an option to include a working link to the topic in the export too if you fancy it, using eg: 'https://meta.discourse.org/t/' || t.slug || '/' || t.id AS topic_url,

[image] JammyDodger: There is also an option to include a working link to the topic in the export too if you fancy it, using eg: 'https://meta.discourse.org/t/' || t.slug || '/' || t.id AS topic_url, I feel silly asking this, but I honestly can’t figure out where I’d add this part in the code above. Where would it go?

That’s okay. :slight_smile: It would go in this bit here: (just change it to your own site address instead of Meta) -- [params] -- int :category_id WITH tag_names AS ( SELECT t.id topic_id, string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags" FROM topics t JOIN topic_tags tt ON tt.topic_id = t.id JOIN tags ON tags.id = tt.tag_id GROUP BY t.id ) SELECT t.created_at::date, dvtvc.topic_id, t.title, 'https://meta.discourse.org/t/' || t.slug || '/' || t.id AS topic_url, votes_count, t.posts_count, tn.tags, t.closed FROM discourse_voting_topic_vote_count dvtvc JOIN topics t ON t.id = dvtvc.topic_id LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id WHERE t.category_id = :category_id AND votes_count > 0 ORDER BY t.created_at DESC

A summary of votes by topic, including tags

Community Building Data & reporting

CarrieSeltzer (Carrie Seltzer) January 31, 2023, 4:21am 5

I feel silly asking this, but I honestly can’t figure out where I’d add this part in the code above. Where would it go?

Topic		Replies	Views
Data Explorer - Vote count Data & reporting topic-voting , sql-query	2	731	May 28, 2023
Need data explorer query to export an entire topic Data & reporting sql-query	2	550	November 12, 2023
Understanding what our users are posting about Data & reporting sql-query	9	543	June 20, 2024
Number of topics in a category Product Support	5	1160	September 20, 2020
Show total votes next to total replies, views and activity columns Feature topic-voting	10	3340	December 18, 2025

A summary of votes by topic, including tags

Related topics