대시보드 보고서 - 다양한 사용자로부터 받은 '좋아요' 수 상위 사용자

[SaraDev (Sara Devlaeminck)]

이것은 다양한 사용자들로부터 받은 좋아요 수 기준으로 상위 사용자를 보여주는 대시보드 리포트의 SQL 버전입니다.

이 대시보드 리포트는 지정된 날짜 범위 내에서 사이트의 다양한 사용자들로부터 좋아요를 많이 받은 상위 사용자를 보여줍니다. 이 쿼리는 각 사용자의 게시물이 얼마나 많은 고유한 사용자로부터 좋아요를 받았는지, 즉 게시물에 대한 고유 사용자 수를 기준으로 가장 많은 좋아요를 받은 사용자를 찾는 데 설계되었습니다.

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username,
ORDER BY
    likes DESC

SQL 쿼리 설명

• 날짜 매개변수:
  • 이 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 매개변수인 :start_date와 :end_date를 받습니다. 두 날짜 매개변수 모두 YYYY-MM-DD 형식의 날짜를 받습니다.
• 선택(Selection): 출력으로 선택되는 주요 열은 다음과 같습니다:
  • p.user_id: 게시물 작성자의 사용자 ID.
  • u.username: 해당 사용자의 이름.
  • likes: 게시물을 ‘좋아요’한 고유한 사용자 ID(ua.user_id)의 개수로, 사용자가 중복되지 않도록 고유한 좋아요만 계산되도록 보장합니다.
• 조인(Joins): 두 가지 내부 조인이 있습니다:
  • user_actions ua와 posts p: 동작을 해당 게시물과 매칭합니다.
  • posts p와 users u: 게시물을 작성자와 연결하여 사용자 정보를 가져오도록 합니다.
• 필터링(Filtering):
  • WHERE 절에는 여러 가지 필터가 적용됩니다:
    • ua.created_at::date BETWEEN :start_date AND :end_date는 고려할 동작을 매개변수 범위 내로 제한합니다.
    • ua.action_type = 1은 다른 유형의 사용자 동작에서 ‘좋아요’ 동작을 분리합니다.
    • p.user_id > 0은 등록되지 않은 사용자(시스템 사용자나 익명 사용자 등)와 관련된 좋아요를 제외합니다.
• 그룹핑(Grouping):
  • 결과 집합은 p.user_id와 u.username별로 그룹화됩니다. 이를 통해 좋아요가 사용자별 및 해당 사용자 이름별로 집계됩니다.
• 정렬(Ordering):
  • 결과는 좋아요 수(likes DESC) 기준으로 정렬되어, 가장 많은 관심을 받은 사용자가 먼저 표시됩니다.

예시 결과

user	username	likes
user1	user1	100
user2	user2	90
user3	user3	80
…	…	…