Dashboard Report - Likes

Community Building Data & reporting
, ,
4

My forum isn’t very big, and most of our like reactions come from “staff” (admins, moderators, TL=4). I wanted to see how likes from regular users vs “staff” compared, and list the number of posts/day for a better sense of what’s going on and where we need to concentrate efforts to improve use of reactions.

Me and my pal ChatGPT came up with this:

-- [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

Changes to @SaraDev’s original query (thanks, Sara!):
SQL Changes Summary

  1. Generate Staff Group:
    Added a staff_users CTE to identify staff users from the users table. A staff user is defined as any of:
  • admin = true
  • moderator = true
  • trust_level = 4
  1. Separate Staff Likes:
    Added a subquery to calculate the likes count from staff users (staff_likes_count) by filtering post_actions for user_id in the staff_users group.
  2. Rename Non-Staff Likes Column:
    Changed the output label for non-staff likes from likes_count to regular_likes_count.
  3. Add Total Likes:
    Introduced a total_likes column to sum regular_likes_count and staff_likes_count.
  4. Add Posts Per Day:
    Added a subquery to calculate the number of posts per day (posts_per_day) and joined it to the date range.
    (Yeah ChatGPT made this list of changes for me too.)

Example results:

date 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
Same query reported by weeks to smooth 
-- [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
In case it's interesting, here's the final prompts that modified Sara's query:

I have an SQL query that reports the daily likes count (likes_count) between two dates, but I need to make the following enhancements to produce a final output that aggregates data by weeks and includes additional details:

  1. Define a Staff Group:
  • Create a staff_users group from the users table. A user should be considered staff if they meet any of the following criteria:
    • admin = true
    • moderator = true
    • trust_level = 4
  1. Separate Likes by Staff and Non-Staff:
  • Add two separate columns:
    • regular_likes_count: Count likes from non-staff users.
    • staff_likes_count: Count likes from staff users.
  • Ensure the regular_likes_count column excludes likes generated by staff users.
  1. Add Total Likes:
  • Include a total_likes column that sums regular_likes_count and staff_likes_count.
  1. Add Posts Per Period:
  • Add a posts_per_week column that counts the number of posts created during each week.
  1. Aggregate by Weeks:
  • Modify the query to group all data by weekly intervals instead of daily.
  • Include a week_start column that represents the start date of each week.
  1. Limit by Weeks Ago:
  • Introduce a parameter :weeks_ago to limit the results to the past N weeks. The default value should be 52 weeks (1 year).
  1. Order and Final Columns:
  • Ensure the output is ordered by week_start and includes the following columns in this order:
    1. week_start: The start date of the week.
    2. regular_likes_count: The count of likes from non-staff users.
    3. staff_likes_count: The count of likes from staff users.
    4. total_likes: The sum of regular_likes_count and staff_likes_count.
    5. posts_per_week: The number of posts created during the week.