Привет всем, кто играет и исследует плагин Data Explorer!
Спасибо за помощь со всеми предыдущими вопросами, @michebs. У меня есть ещё один вопрос по запросам Data Explorer.
Кто-нибудь создавал запрос, который показывает процент активных пользователей по месяцам?
Активным считается тот, кто хотя бы раз лайкнул или ответил в некоторых темах в течение месяца — неважно сколько раз, главное, чтобы было хотя бы одно действие. Процент рассчитывается на основе общего числа участников сообщества. Например, если в каком-то месяце участвовали два человека: один лайкнул пост, а другой ответил — это два человека, и мы делим это число на общее количество участников нашего сообщества.
Надеюсь, это поможет.
Стоит отметить, что общее количество пользователей со временем меняется, поэтому в запросе учитывается кумулятивное количество пользователей до месяца и года, в котором было выполнено действие.
WITH tt_users_by_month AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "new_users_month"
FROM users
WHERE id > 0
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
total_users AS (
SELECT
year,
month,
SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
FROM tt_users_by_month ORDER BY year, month
)
SELECT
date_part('year', ua.created_at) AS year,
date_part('month', ua.created_at) AS month,
TRUNC(COUNT(DISTINCT user_id)::decimal/tu.total*100,2) AS "%"
FROM
user_actions ua
INNER JOIN total_users tu ON (date_part('year', ua.created_at) = tu.year AND date_part('month', ua.created_at) = tu.month)
WHERE action_type IN (1,5)
GROUP BY date_part('year', created_at), date_part('month', created_at), total