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

[SaraDev (Sara Devlaeminck)]

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

Этот отчёт панели управления показывает топ пользователей сайта по количеству полученных лайков во всех публикациях. Отчёт помогает администраторам выявлять наиболее активных пользователей, подсчитывая количество полученных лайков за определённый период, что способствует признанию активных членов сообщества, оценке качества контента, мониторингу тенденций вовлечённости пользователей и принятию обоснованных решений по управлению сообществом, например, при выборе модераторов.

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

SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC

Объяснение SQL-запроса

Запрос работает следующим образом:

• Параметры даты: Запрос принимает два параметра — :start_date и :end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Выборка данных: Запрос выбирает идентификаторы пользователей и имена пользователей из таблицы user_actions (ua) и таблицы users (u), которые объединяются с помощью команды SQL INNER JOIN при условии совпадения ua.user_id с u.id.
• Фильтрация данных: Ключевое слово WHERE используется для фильтрации действий, попадающих в указанный диапазон дат, и учитываются только действия типа was_liked, который обозначается как action_type = 2.
• Агрегация: Функция COUNT(*) в сочетании с ключевым словом GROUP BY группирует результаты по user_id, username и uploaded_avatar_id, а также подсчитывает количество лайков, полученных каждым пользователем.
• Сортировка результатов: Ключевое слово ORDER BY сортирует агрегированные данные в порядке убывания по полю likes_received, чтобы пользователь с наибольшим количеством полученных лайков оказался в верхней части отчёта.

Запрос формирует список пользователей вместе с количеством полученных ими лайков за указанный диапазон дат, отсортированный от наибольшего к наименьшему.

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

user	username	likes_received
user1	user1	748
user2	user2	324
user3	user3	308
…	…	…