# 사용자별 해결된 통계 목록

**URL:** https://meta.discourse.org/t/list-of-solved-stats-per-user/275391
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [4월 16, 2023, 4:05오후 UTC](https://meta.discourse.org/t/list-of-solved-stats-per-user/275391 "2023-04-16T16:05:00Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [4월 16, 2023, 4:05오후 UTC](https://meta.discourse.org/t/list-of-solved-stats-per-user/275391/1 "2023-04-16T16:05:00Z")

</div>

사용자별 통계를 포함하는 목록을 생성하려면 시간 간격을 변수로 사용할 수 있습니다(기본값: 1년):

```sql
-- [params]
-- string :interval = 1 year

SELECT  
    ua.acting_user_id, 
    COUNT(CASE t.user_id WHEN ua.acting_user_id THEN 1 ELSE NULL END) as is_op, 
    COUNT(CASE t.user_id WHEN ua.acting_user_id THEN NULL ELSE 1 END) as not_op,
    COUNT(*) as total
FROM user_actions ua
LEFT JOIN topics t ON target_topic_id = t.id
WHERE action_type=15
AND ua.created_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY ua.acting_user_id
ORDER BY total DESC

```