Отчет панели управления: Топ пользователей по количеству лайков от пользователя с более низким уровнем доверия

[SaraDev (Sara Devlaeminck)]

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

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

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

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

Пояснение к SQL-запросу

• Параметры даты:
  • Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Общее табличное выражение (CTE):
  • CTE user_liked_tl_lower определено в начале для повторного использования в основном запросе.
  • Оно вычисляет общее количество лайков, полученных каждым пользователем на своих постах от пользователей с более низким уровнем доверия в течение заданного периода, определенного параметрами :start_date и :end_date.
• Внутренние соединения (Inner Joins):
  • Выполняются соединения между таблицами users, posts и user_actions для фильтрации действий, являющихся «лайками» (обозначается action_type = 1).
  • Дополнительное соединение с таблицей users (u_liked) используется для обеспечения того, чтобы пользователь, поставивший лайк, имел уровень доверия ниже, чем автор поста.
• Ранжирование:
  • Результаты разбиваются на группы по уровню доверия пользователя и сортируются по количеству лайков в порядке убывания.
  • Присваивается ранг на основе количества лайков; при равенстве ранги одинаковы, а следующий ранг пропускает соответствующие числа (это стандартное ранжирование, а не плотное).
• Фильтрация результатов в CTE:
  • Учитываются только лайки, поставленные в указанный период времени.
• Финальный выбор:
  • Основной запрос выбирает все столбцы из CTE, где rank меньше или равен 10.
  • Это ограничивает результаты топ-10 пользователей в каждой категории уровня доверия, получивших наибольшее количество лайков от пользователей с более низким уровнем доверия.

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

user	username	trust_level	likes	rank
user1	user1	4	323	1
user2	user2	4	164	2
user3	user3	4	143	3
…	…	…	…
user11	user11	3	45	1
user12	user12	3	34	2
…	…	…	…"}