Добрый день,
Я хотел бы запросить данные по конкретным именам пользователей: количество их лайков, полученных лайков, постов и решений за определённый диапазон дат с разбивкой по дням. Есть ли какие-либо предложения по такому типу отчётности? Подозреваю, что для этого потребуется использовать плагин Data Explorer. Есть ли у кого-нибудь уже готовый запрос для такой задачи? Буду признателен за любые комментарии!
У меня был запрос, очень похожий на то, что вы ищете, на моём локальном сервере разработки. Я немного его изменил, чтобы он соответствовал вашим требованиям. Одна из вещей, которую я постарался сделать, — это позволить запускать запрос как по активности в обычных темах, так и по активности в личных сообщениях (ЛС). Частично это было продиктовано желанием проанализировать свою собственную активность за определённый период. Возможность выбирать между обычными темами и ЛС имеет смысл для моего случая. То, как запрос обрабатывает решения, когда отмечен чекбокс personal_messages, не идеально: колонка solutions_provided будет возвращать NULL в этом случае. Возможно, переключатель между обычными темами и ЛС стоит убрать из запроса.
Этот запрос не слишком сложный, но довольно длинный. Если вы заметите какие-либо проблемы с его результатами, пожалуйста, дайте мне знать.
Для запуска запроса необходимо указать три параметра: username, start_date и end_date. Параметры даты должны быть в формате ‘yyyy-mm-dd’. Например, 2020-03-13. Параметр personal_messages по умолчанию равен false. Отметьте чекбокс personal_messages в запросе, если вы хотите увидеть результаты активности, произошедшей в личных сообщениях.
--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 15
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC
Это отлично! Спасибо @simon!