Это SQL-версия отчета панели управления по основным реферерам
Этот отчет покажет основных пользователей, которые генерировали наибольшее количество кликов по входящим ссылкам на публикации в указанном диапазоне дат.
Отчет показывает не только общее количество кликов, полученных каждым пользователем, но и количество уникальных тем, на которые распространяются эти клики. Это поможет понять, какие пользователи привлекают трафик на форум из внешних источников и вовлекают аудиторию в различные обсуждения.
--[params]
-- date :start_date
-- date :end_date
SELECT
il.user_id,
COUNT(*) Clicks,
COUNT(DISTINCT p.topic_id) Topics
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
AND il.user_id > 0
GROUP BY il.user_id
ORDER BY 2 DESC
Объяснение SQL-запроса
SQL-запрос, используемый для этого отчета, выполняет следующие действия:
- Фильтрует записи входящих ссылок (
incoming_links), оставляя только те, которые были созданы в указанном диапазоне дат (:start_date–:end_date). - Обеспечивает, чтобы входящие ссылки были связаны с валидными пользователями (исключая системных или анонимных пользователей с
user_id < 0). - Объединяет таблицу
incoming_linksс таблицейposts, чтобы связать каждый клик по ссылке с соответствующей темой. - Группирует результаты по
user_idдля агрегации данных по каждому пользователю. - Подсчитывает общее количество кликов по входящим ссылкам (
Clicks) и количество уникальных тем (Topics) для каждого пользователя. - Сортирует результаты по общему количеству кликов в порядке убывания, чтобы выявить основных участников.
Параметры
:start_date: Начало диапазона дат, для которого генерируется отчет.:end_date: Конец диапазона дат, для которого генерируется отчет.
Оба параметра даты принимают формат ГГГГ-ММ-ДД.
Пример результатов
| Пользователь | Клики | Темы |
|---|---|---|
| username_1 | 5241 | 97 |
| username_2 | 1256 | 14 |
| username_3 | 1242 | 12 |
| … | … | … |