Отчет дашборда: топ пользователей по количеству полученных лайков от разных людей

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета панели управления по топ-пользователям по количеству полученных лайков от различных пользователей.

Этот отчет панели управления показывает топ-пользователей, получивших лайки от широкого круга пользователей сайта за указанный период времени. Запрос предназначен для выявления пользователей, чьи публикации получили наибольшее количество лайков, при этом каждое значение в столбце «likes» представляет количество уникальных пользователей, поставивших лайк их постам.

--[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, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Выборка: Основные столбцы, выбираемые для вывода:
  • p.user_id: ID пользователя, опубликовавшего пост.
  • u.username: Имя соответствующего пользователя.
  • likes: Количество уникальных ID пользователей (ua.user_id), поставивших лайк постам, что обеспечивает подсчет уникальных лайков от каждого пользователя.
• Соединения: Выполняются два inner join:
  • user_actions ua с posts p: Связывает действия с соответствующими постами.
  • posts p с users u: Связывает посты с их авторами, обеспечивая получение информации о пользователях.
• Фильтрация:
  • Условие WHERE применяет несколько фильтров:
    • ua.created_at::date BETWEEN :start_date AND :end_date ограничивает рассматриваемые действия теми, что попадают в указанный диапазон.
    • ua.action_type = 1 выделяет действия типа «лайк» из других типов действий пользователей.
    • p.user_id > 0 исключает любые лайки, связанные с незарегистрированными пользователями (например, системными или анонимными).
• Группировка:
  • Результат группируется по p.user_id и u.username. Это обеспечивает агрегацию лайков по каждому пользователю и соответствующему имени.
• Сортировка:
  • Результаты сортируются по количеству лайков (likes DESC), выводя наиболее оцененных пользователей в начале списка.

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

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