대시보드 보고서 - 낮은 신뢰 등급 사용자에게서 더 많은 '좋아요'를 받은 상위 사용자

[SaraDev (Sara Devlaeminck)]

이것은 신뢰 등급이 낮은 사용자로부터 받은 ‘좋아요’ 수 기준 상위 사용자 대시보드 리포트의 SQL 버전입니다.

이 대시보드 리포트는 지정된 기간 동안 신뢰 등급이 낮은 회원들로부터 가장 많은 '좋아요’를 받은 사용자를 식별하는 것을 목표로 합니다. 이 리포트는 서로 다른 신뢰 등급을 가진 사용자 간의 상호작용에 초점을 맞추며, 포럼에서 낮은 권한이나 경험을 가진 사용자들로부터 '좋아요’를 받아 커뮤니티 참여도에서 두각을 나타내는 계정을 강조합니다.

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

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

SQL 쿼리 설명

• 날짜 파라미터:
  • 쿼리는 리포트의 날짜 범위를 정의하는 :start_date와 :end_date라는 두 가지 파라미터를 받습니다. 두 날짜 파라미터 모두 YYYY-MM-DD 날짜 형식을 사용합니다.
• 공용 테이블 표현식 (CTE):
  • CTE user_liked_tl_lower는 메인 쿼리 내에서 재사용을 위해 시작 부분에 정의됩니다.
  • :start_date와 :end_date로 정의된 주어진 기간 내에서, 각 사용자가 자신의 게시물에서 신뢰 등급이 낮은 사용자들로부터 받은 ‘좋아요’ 총수를 계산합니다.
• 내부 조인 (Inner Joins):
  • users 테이블과 posts, user_actions 테이블 간의 조인을 수행하여 ‘좋아요’ 동작(action_type = 1으로 표시됨)을 필터링합니다.
  • users 테이블(u_liked)에 대한 추가 조인을 수행하여 '좋아요’를 준 사용자의 신뢰 등급이 게시물 작성자의 신뢰 등급보다 낮은지 확인합니다.
• 랭킹:
  • 결과는 사용자의 신뢰 등급별로 분할(partition)되고, ‘좋아요’ 수 기준으로 내림차순으로 정렬됩니다.
  • ‘좋아요’ 수를 기준으로 랭킹이 부여되며, 동점인 경우 동일한 랭킹을 받고 다음 랭킹은 숫자를 건너뛰어 부여됩니다(이것은 표준 랭킹이며, dense ranking이 아닙니다).
• CTE 내 결과 필터링:
  • 지정된 기간 내에 주어진 '좋아요’만 집계됩니다.
• 최종 선택:
  • 메인 쿼리는 CTE에서 rank가 10 이하인 모든 컬럼을 선택합니다.
  • 이를 통해 각 신뢰 등급 카테고리에서 신뢰 등급이 낮은 사용자들로부터 가장 많은 '좋아요’를 받은 상위 10명의 사용자만 결과에 포함됩니다.

결과 예시

user	username	trust_level	likes	rank
user1	user1	4	323	1
user2	user2	4	164	2
user3	user3	4	143	3
…	…	…	…
user11	user11	3	45	1
user12	user12	3	34	2
…	…	…	…