# 投票に関連する良いバッジを探しています

**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:** [2019 年 4 月 26 日午後 6: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:** 1
**Showing post:** 7

<div class="post-metadata">

### Author: ![Marc\_Montecalvo](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/marc_montecalvo/32/160134_2.png) [@Marc\_Montecalvo](https://meta.discourse.org/u/Marc_Montecalvo)
#### Post date: [2019 年 4 月 29 日午後 2:06 UTC](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086/7 "2019-04-29T14:06:37Z")

</div>

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

```

---

_[View the full topic](https://meta.discourse.org/t/looking-for-some-good-badges-related-to-voting/116086)._