Похоже, что указанные вами входные данные не соответствуют ожидаемому результату. Если вы хотите получить данные для одного пользователя, в качестве отправной точки может подойти следующий запрос. Он использует общие табличные выражения (CTE), чтобы сделать запрос более читаемым и простым в написании. Тот же шаблон можно использовать для добавления дополнительных данных в результат.
Один момент, в котором я не уверен: хотите ли вы получить сумму данных за определенный день или количество действий, совершенных в каждый из дней. Например, хотите ли вы узнать, что на 2019-10-31 у пользователя было всего 20 постов за все время его пребывания на сайте, или вы хотите узнать, что на 2019-10-31 пользователь создал ровно 3 поста? В текущей структуре запрос возвращается именно второй вариант.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::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 = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
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 days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
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
ORDER BY d.day
Чтобы повысить активность пользователей, мы планируем провести конкурс на звание самого активного участника нашего форума, победитель которого получит награду.
Поэтому мы хотели бы получить дополнительные данные, чтобы сделать конкурс ещё интереснее.
Данные, которые у нас есть (используя запрос от @simon): темы, сообщения, количество слов и полученные лайки.
Некоторые дополнительные данные включают:
Отданные лайки: сколько лайков было отдано пользователем.
Посещения: сколько раз пользователь открывал форум.
Время чтения: сколько времени пользователь провёл на форуме.
Подписки и подписчики.
Просмотры тем: сколько раз были просмотрены все темы, созданные пользователем.
Ответы в темах: сколько ответов (сообщений) получили все темы, созданные пользователем.
Пожалуйста, помогите нам получить эти дополнительные данные.
Для взвешивания каждого показателя мы планируем использовать Excel. Есть ли у вас советы, как расставить веса для каждого показателя, чтобы выбрать победителя?
Мы хотим, чтобы этот конкурс был серьёзным, но при этом весёлым. Лучшие пользователи — это сочетание максимальной активности, наибольшей полезности и популярности.
Ещё раз большое спасибо за вашу помощь.
Примечание: Можно ли получать данные группами, чтобы нам нужно было указывать только ID группы? Наш план — поместить всех участников в одну группу. В данный момент нам приходится вводить ID каждого участника по отдельности.
Я тоже из Манчестера
Моя компания рассматривает возможность проведения аналогичного конкурса внутри организации, чтобы повысить вовлечённость сотрудников в жизнь нашего сообщества.
Вам удалось провести конкурс? Как вы в итоге извлекали данные? И как вы взвешивали различные метрики вовлечённости?
