대시보드 보고서 - 좋아요

커뮤니티 구축 데이터 및 보고
, ,
4

우리 포럼은 크지 않고, 대부분의 좋아요 반응은 “스태프”(관리자, 모더레이터, TL=4)에서 옵니다. 일반 사용자와 "스태프"의 좋아요를 비교해 보고, 일일 게시물 수를 나열하여 어떤 일이 일어나고 있는지, 그리고 반응 사용 개선을 위해 어디에 노력을 집중해야 하는지 파악하고 싶었습니다.

나와 내 친구 ChatGPT가 이것을 만들었습니다:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2 
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

@SaraDev의 원래 쿼리에 대한 변경 사항 (고맙습니다, Sara!):
SQL 변경 사항 요약

  1. 스태프 그룹 생성:
    users 테이블에서 스태프 사용자를 식별하기 위해 staff_users CTE를 추가했습니다. 스태프 사용자는 다음 중 하나에 해당하면 됩니다:
  • admin = true
  • moderator = true
  • trust_level = 4
  1. 스태프 좋아요 분리:
    staff_users 그룹의 user_id로 post_actions를 필터링하여 스태프 사용자의 좋아요 수(staff_likes_count)를 계산하는 서브쿼리를 추가했습니다.
  2. 비(非)스태프 좋아요 컬럼 이름 변경:
    비(非)스태프 좋아요의 출력 라벨을 likes_count에서 regular_likes_count로 변경했습니다.
  3. 총 좋아요 추가:
    regular_likes_count와 staff_likes_count를 합산하는 total_likes 컬럼을 도입했습니다.
  4. 일일 게시물 수 추가:
    일일 게시물 수(posts_per_day)를 계산하는 서브쿼리를 추가하고 날짜 범위에 조인했습니다.
    (네, ChatGPT가 이 변경 사항 목록도 저를 위해 만들어 주었습니다.)

결과 예시:

날짜 regular_likes_count staff_likes_count posts_per_day
1/1/24 0 6 7
1/2/24 0 5 3
1/3/24 1 0 4
1/4/24 1 2 5
1/5/24 9 9 30
1/6/24 0 1 11
1/7/24 2 4 11
1/8/24 0 5 18
1/9/24 0 0 2
1/10/24 0 0 7
1/11/24 0 4 5
1/12/24 4 0 4
1/13/24 6 0 10
1/14/24 1 7 18
1/15/24 2 4 7
주 단위로 보고하여 부드럽게 하기 위해 동일한 쿼리 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
관심있을 경우를 위해, Sara의 쿼리를 수정한 최종 프롬프트는 다음과 같습니다:

두 날짜 사이의 일일 좋아요 수(likes_count)를 보고하는 SQL 쿼리가 있습니다. 주 단위로 데이터를 집계하고 추가 세부 정보를 포함하는 최종 출력을 생성하기 위해 다음과 같은 강화가 필요합니다:

  1. 스태프 그룹 정의:
  • users 테이블에서 staff_users 그룹을 생성합니다. 사용자가 다음 기준 중 하나를 충족하면 스태프로 간주해야 합니다:
    • admin = true
    • moderator = true
    • trust_level = 4
  1. 스태프/비(非)스태프별 좋아요 분리:
  • 두 개의 별도 컬럼을 추가합니다:
    • regular_likes_count: 비(非)스태프 사용자의 좋아요 수.
    • staff_likes_count: 스태프 사용자의 좋아요 수.
  • regular_likes_count 컬럼이 스태프 사용자가 생성한 좋아요를 제외하도록 보장합니다.
  1. 총 좋아요 추가:
  • regular_likes_count와 staff_likes_count를 합산하는 total_likes 컬럼을 포함합니다.
  1. 기간별 게시물 수 추가:
  • 각 주 동안 생성된 게시물 수를 세는 posts_per_week 컬럼을 추가합니다.
  1. 주 단위 집계:
  • 쿼리를 수정하여 모든 데이터를 일 단위 대신 주 간격으로 그룹화합니다.
  • 각 주의 시작 날짜를 나타내는 week_start 컬럼을 포함합니다.
  1. 주 단위 제한:
  • 결과를 지난 N주로 제한하기 위해 :weeks_ago 매개변수를 도입합니다. 기본값은 52주(1년)여야 합니다.
  1. 정렬 및 최종 컬럼:
  • 출력이 week_start 기준으로 정렬되고 다음 컬럼이 이 순서로 포함되도록 보장합니다:
    1. week_start: 주의 시작 날짜.
    2. regular_likes_count: 비(非)스태프 사용자의 좋아요 수.
    3. staff_likes_count: 스태프 사용자의 좋아요 수.
    4. total_likes: regular_likes_count와 staff_likes_count의 합.
    5. posts_per_week: 주 동안 생성된 게시물 수.