Olá a todos que estão jogando e explorando o plugin Data Explorer!
Obrigado por ajudar com todas as perguntas anteriores, @michebs. Tenho mais uma sobre consultas do Data Explorer.
Alguém já criou uma consulta que busca a porcentagem de usuários ativos por mês?
Ativo significa que a pessoa deu like ou respondeu em algum tópico naquele mês; não importa quantas vezes, mas precisa ser pelo menos uma. A porcentagem é calculada com base no número total de membros da comunidade. Então, digamos que tenhamos duas pessoas que interagiram em um mês: uma deu like em algum post e a outra respondeu — então são duas pessoas, e dividimos esse número pela soma de todos os membros que temos na nossa comunidade.
Espero que isso ajude.
Vale mencionar que o número total de usuários varia ao longo do tempo, portanto, o que está sendo considerado na consulta é o número cumulativo de usuários até o mês e o ano em que a ação foi realizada.
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