# Looking for some good badges related to Voting
**URL:** https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086
**Category:** Data & reporting
**Tags:** badges, topic-voting, sql-query
**Created:** [26 april 2019 om 18:29 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086 "2019-04-26T18:29:13Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Marc\_Montecalvo](https://meta.discourse.org/u/Marc_Montecalvo)
#### Post date: [26 april 2019 om 18:29 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/1 "2019-04-26T18:29:13Z")
I have a few ideas for badges but I’m unsure how to write the queries (I don’t know the table structure well enough). The numbers are just to use as a base. I’ll be extending them for additional badges.
1. Someone got 10 votes on a single topic they wrote
2. Someone got 20 votes total between all topics they wrote
3. Someone voted 20 times
Anyone already have some badges like this and want to share? If not does anyone want to take a thwack at writing the queries?
> [@Looking for some good badges related to Voting](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/7):
>
> I think I got it! This will give the topic with the most votes per person that has at least 5 votes on it: WITH query as ( select t.user\_id, ucf.value, count(\*) as num, t.created\_at, row\_number() OVER (PARTITION BY t.user\_id order by count(\*) desc) as rank from topics t join user\_custom\_fields ucf on t.id = cast(ucf.value as int) where ucf.name = 'votes' group by ucf.value, t.user\_id, t.created\_at having count(\*) \>= 5 order by t.user\_id) select user\_id, value as…
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [26 april 2019 om 18:35 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/2 "2019-04-26T18:35:06Z")
> [@Marc\_Montecalvo](#):
>
> Someone voted 20 times
The second query in this topic might do the trick: [Badge ideas for Topic Voting](https://meta.discourse.org/t/badge-ideas-for-feature-voting/58483). The query grants the badge to users who have cast a single vote, but there is a suggestion at the bottom of the query for how to require more votes to grant the badge.
---
### Author:  [@Marc\_Montecalvo](https://meta.discourse.org/u/Marc_Montecalvo)
#### Post date: [26 april 2019 om 18:48 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/3 "2019-04-26T18:48:53Z")
Thanks @simon. I actually read over that post already but the query didn’t return the results I was expecting. It was the Limit 1 at the end messing me up. After removing that it works perfect for the “Someone voted x times”.
Any idea how to see how many votes a person got on a topic they wrote? Or how many likes they got total?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [26 april 2019 om 18:55 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/4 "2019-04-26T18:55:00Z")
> [@Marc\_Montecalvo](#):
>
> Any idea how to see how many votes a person got on a topic they wrote? Or how many likes they got total?
You might find some [Data Explorer](https://meta.discourse.org/t/32566?silent=true) queries that you can modify to create badge queries. Have a look at the [data explorer queries for topic voting](https://meta.discourse.org/tags/c/data-reporting/148/topic-voting) and other #sql-query and #sql-triggered-badge topics.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [26 april 2019 om 21:02 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/5 "2019-04-26T21:02:48Z")
> [@Marc\_Montecalvo](#):
>
> Anyone already have some badges like this and want to share?
Aren’t these rather similar to badges that already exist in Discourse?
---
### Author:  [@Marc\_Montecalvo](https://meta.discourse.org/u/Marc_Montecalvo)
#### Post date: [29 april 2019 om 12:45 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/6 "2019-04-29T12:45:01Z")
That got me quite a ways towards my target:
```plaintext
select
t.user_id,
ucf.value,
count(*)
from
topics t
join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes'
group by ucf.value, t.user_id
order by t.user_id, count(*) desc
```
That returns:
The two things I’m trying to get are total votes someone got and the topic that has the most votes for each person. You can see mmontecalvo has 2 topics showing. I’m fairly certain I need to use `row_number()` to get just the highest post but I don’t know how.
@codinghorror These may be similar to a default badge but none of the defaults are built off topics getting votes.
---
### Author:  [@Marc\_Montecalvo](https://meta.discourse.org/u/Marc_Montecalvo)
#### Post date: [29 april 2019 om 14:06 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/7 "2019-04-29T14:06:37Z")
I think I got it! This will give the topic with the most votes per person that has at least 5 votes on it:
```plaintext
WITH query as (
select
t.user_id,
ucf.value,
count(*) as num,
t.created_at,
row_number() OVER (PARTITION BY t.user_id order by count(*) desc) as rank
from
topics t
join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes'
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at
from query where rank = 1
```
I ended up going with this instead so we could give a badge for each topic someone puts in that gets at least X votes in the correct category:
```plaintext
WITH query as (
select
t.user_id,
ucf.value,
count(*) as num,
t.created_at
from
topics t
join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes' and category_id = (
SELECT id FROM categories WHERE name ilike 'UI Experience Requests'
)
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at
from query
```
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [18 januari 2023 om 11:28 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/9 "2023-01-18T11:28:52Z")