Отчет по панели управления - лайки

NiceOldGuy · 22.Январь.2025 22:03:34

Мой форум не очень большой, и большинство наших реакций «лайк» поступают от «сотрудников» (администраторы, модераторы, 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 (спасибо, Сара!):
Сводка изменений SQL

Генерация группы сотрудников:
Добавлен CTE staff_users для идентификации сотрудников из таблицы users. Сотрудником считается любой пользователь, который:

admin = true
moderator = true
trust_level = 4

Разделение лайков сотрудников:
Добавлен подзапрос для подсчета количества лайков от сотрудников (staff_likes_count) путем фильтрации post_actions по user_id из группы staff_users.
Переименование столбца лайков не-сотрудников:
Изменена метрика вывода для лайков не-сотрудников с likes_count на regular_likes_count.
Добавление общего количества лайков:
Введен столбец total_likes для суммирования regular_likes_count и staff_likes_count.
Добавление количества постов в день:
Добавлен подзапрос для подсчета количества постов в день (posts_per_day) и его соединение с диапазоном дат.
(Да, этот список изменений тоже составил для меня ChatGPT.)

Пример результатов:

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

Тот же запрос, агрегированный по неделям для сглаживания

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

На всякий случай, если это интересно, вот финальные промпты, которые модифицировали запрос Сары:

У меня есть SQL-запрос, который отчетливо показывает количество лайков (likes_count) в день между двумя датами, но мне нужно внести следующие улучшения, чтобы получить итоговый вывод, агрегирующий данные по неделям и включающий дополнительные детали:

Определение группы сотрудников:

Создать группу staff_users из таблицы users. Пользователь считается сотрудником, если он соответствует любому из следующих критериев:
- admin = true
- moderator = true
- trust_level = 4

Разделение лайков по сотрудникам и не-сотрудникам:

Добавить два отдельных столбца:
- regular_likes_count: количество лайков от не-сотрудников.
- staff_likes_count: количество лайков от сотрудников.
Убедиться, что столбец regular_likes_count исключает лайки, созданные сотрудниками.

Добавление общего количества лайков:

Включить столбец total_likes, который суммирует regular_likes_count и staff_likes_count.

Добавление количества постов за период:

Добавить столбец posts_per_week, который подсчитывает количество постов, созданных за каждую неделю.

Агрегация по неделям:

Изменить запрос так, чтобы все данные группировались по недельным интервалам вместо ежедневных.
Включить столбец week_start, представляющий дату начала каждой недели.

Ограничение по прошедшим неделям:

Ввести параметр :weeks_ago для ограничения результатов последними N неделями. Значение по умолчанию должно быть 52 недели (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: количество постов, созданных за неделю.

Тема		Ответов	Просм.
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	223	31.12.2019
Dashboard Report - Reactions Data & reporting reactions , sql-query , dashboard-reports , dashboard-sql	2	557	19.01.2024
Dashboard Report - Top Users by Likes Received Data & reporting sql-query , dashboard-reports , dashboard-sql	0	332	01.02.2024
Dashboard Report - Top Users by Likes Received From a Variety of People Data & reporting sql-query , dashboard-reports , dashboard-sql	0	310	01.02.2024
Top 50 likers Data & reporting sql-query	0	185	12.02.2018

Отчет по панели управления - лайки

Связанные темы