이것은 '좋아요 수 기준 상위 사용자 대시보드 보고서’의 SQL 버전입니다.
이 대시보드 보고서는 사이트 내 모든 게시물의 좋아요 수 합계를 기준으로 상위 사용자를 보여줍니다. 이 보고서는 특정 기간 내에 받은 좋아요 수를 집계하여 높은 참여도를 보이는 사용자를 식별하는 데 도움을 주며, 활성 멤버를 인정하고, 콘텐츠 품질을 평가하고, 사용자 참여 추세를 모니터링하며, 중개자 선정과 같은 커뮤니티 관리 결정을 내리는 데 활용됩니다.
--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01
SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC
SQL 쿼리 설명
쿼리는 다음과 같이 작동합니다:
- 날짜 파라미터: 쿼리는 보고서의 날짜 범위를 정의하는
:start_date와:end_date라는 두 개의 파라미터를 받습니다. 두 날짜 파라미터 모두YYYY-MM-DD형식의 날짜를 받습니다. - 데이터 선택: 쿼리는
user_actions테이블(ua)과users테이블(u)에서 사용자 ID와 사용자 이름을 선택합니다. 이 두 테이블은ua.user_id가u.id와 일치하는 조건으로 SQL의INNER JOIN명령을 사용하여 연결됩니다. - 데이터 필터링:
WHERE절은 지정된 날짜 범위 내에 해당하는 동작만 필터링하고,was_liked유형의 동작만 고려합니다. 이 동작은action_type = 2로 표현됩니다. - 집계:
COUNT(*)함수와GROUP BY절을 조합하여 결과를user_id,username,uploaded_avatar_id별로 그룹화하고 각 사용자가 받은 좋아요 수를 집계합니다. - 결과 정렬:
ORDER BY절은likes_received기준으로 집계된 데이터를 내림차순으로 정렬하여, 가장 많은 좋아요를 받은 사용자가 보고서 상단에 위치하도록 합니다.
이 쿼리는 주어진 날짜 범위 내에서 받은 좋아요 수를 함께 나열한 사용자 목록을 생성하며, 가장 많은 좋아요부터 가장 적은 좋아요 순으로 정렬됩니다.
결과 예시
| user | username | likes_received |
|---|---|---|
| user1 | user1 | 748 |
| user2 | user2 | 324 |
| user3 | user3 | 308 |
| … | … | … |