Calcular momentos totais de participação por mês usando o Data Explorer

[Konrad_Sopala]

Olá, mestres do Data Explorer!

Alguém já tentou criar uma consulta que retorne a contagem total de momentos de participação por mês usando as consultas do Data Explorer?

Ou seja, o número total de todas as publicações, soluções e curtidas por mês.

Obrigado desde já!

[Rotblut (Bernhard)]

Veja se você gosta disso.

querys.md

[Konrad_Sopala]

Não vejo exatamente o que estou procurando

[simon]

@Konrad_Sopala

E aí, mestres do Data Explorer!

Eu não sou um especialista em Data Explorer, mas gosto de escrever consultas quando tenho tempo. Antes de me empolgar demais com isso, estou assumindo que você está procurando uma divisão mês a mês das estatísticas. Se for esse o caso, algo assim pode funcionar:

--[params]
-- date :start_date

WITH month_starts AS (
SELECT generate_series(date_trunc('month', :start_date::date), CURRENT_DATE, interval '1 month')::date AS month_start
),
monthly_posts AS (
SELECT
month_start,
COUNT(1) AS posts_count
FROM posts p
JOIN month_starts
ON p.created_at::date >= month_start AND p.created_at::date <= month_start + interval '1 month - 1 day'
WHERE p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= :start_date
GROUP BY month_start
),
monthly_total_users AS (
SELECT
month_start,
COUNT(1) AS total_users_count
FROM users u
JOIN month_starts
ON u.created_at::date <= month_start + interval '1 month - 1 day'
WHERE u.id > 0
GROUP BY month_start
),
monthly_active_users AS (
SELECT
month_start,
COUNT(DISTINCT user_id) AS active_users_count
fROM user_visits uv
JOIN month_starts
ON uv.visited_at >= month_start AND uv.visited_at <= month_start + interval '1 month - 1 day'
WHERE uv.visited_at >= :start_date
GROUP BY month_start
),
monthly_solutions AS (
SELECT
month_start,
COUNT(1) AS solutions_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 15
AND ua.created_at >= :start_date
GROUP BY month_start
),
monthly_likes AS (
SELECT
month_start,
COUNT(1) AS likes_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 2
AND ua.created_at >= :start_date
GROUP BY month_start
)

SELECT
ms.month_start,
COALESCE(posts_count, 0) AS posts_count,
COALESCE(total_users_count, 0) AS total_users_count,
COALESCE(active_users_count, 0) AS active_users_count,
COALESCE(solutions_count, 0) AS solutions_count,
COALESCE(likes_count, 0) AS likes_count
FROM month_starts ms
LEFT JOIN monthly_posts mp ON mp.month_start = ms.month_start
LEFT JOIN monthly_total_users mtu ON mtu.month_start = ms.month_start
LEFT JOIN monthly_active_users mau ON mau.month_start = ms.month_start
LEFT JOIN monthly_solutions mts ON mts.month_start = ms.month_start
LEFT JOIN monthly_likes ml ON ml.month_start = ms.month_start
ORDER BY month_start DESC

Antes de executar a consulta, você precisa fornecer um valor para o parâmetro start_date. Ele deve estar no formato yyyy-mm-dd. A consulta apenas extrai a parte do mês dessa data. A razão para precisar de um parâmetro de data inicial é que a consulta pode exceder o tempo limite em um site grande se for executada contra todo o período em que o site está online. Usando o parâmetro de data inicial, consigo executá-lo no Meta por um período de alguns anos sem que ocorra o tempo limite.

Uma coisa a notar na consulta é que a coluna active_users_count retorna o número de usuários únicos que fizeram login no site durante um mês — ela não verifica se esses usuários realizaram alguma ação no site (por exemplo, curtiram uma postagem ou criaram uma postagem). Possivelmente, a consulta poderia fazer isso, mas estou preocupado com problemas de tempo limite.

Me avise se isso não for o tipo de dado que você está procurando. Se a ideia geral estiver correta, me avise se precisar que dados adicionais sejam adicionados à consulta ou se notar algo que pareça errado nos resultados.

[Konrad_Sopala]

Obrigado @simon por compartilhar isso, é realmente útil e é exatamente o que estou procurando! Posso perguntar se é possível fazer uma alteração na consulta? Não gosto de especificar a data de início. Gostaria de somar todos esses valores (posts, usuários, soluções, curtidas) e ter o resultado da consulta assim, por mês:

[simon]

@Konrad_Sopala

Eu não gosto de especificar a data de início. Gostaria de somar todos esses valores (posts, usuários, soluções, curtidas) e ter o resultado da consulta assim, por mês:

Isso pode ser possível. Vou tentar. O parâmetro de data de início foi adicionado à consulta no último minuto, quando descobri que a consulta daria timeout se fosse executada no Meta para todo o período em que o site está online. Pode haver maneiras de melhorar a eficiência da consulta para eliminar o problema de timeout. Caso contrário, a consulta deve permitir a definição de um intervalo de tempo em vez de apenas solicitar uma data de início. Dessa forma, você poderá obter todos os dados de um site executando a consulta algumas vezes com diferentes intervalos de tempo.

[Konrad_Sopala]

Claro! Aguardo então por você assim que tiver tempo de fazer esse ajuste.

[michebs (Michelle)]

Olá Konrad,

Abaixo está a consulta ajustada.

WITH monthly_users 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)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "posts_count"
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	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)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS "active_users_count"
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "solutions_count"
	FROM user_actions ua
	WHERE ua.action_type = 15
	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)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "likes_count"
	FROM user_actions ua
	WHERE ua.action_type = 2
	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)
)

SELECT
    mu.year,
    mu.month,
    SUM(new_users_month) over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS total_users,
    posts_count,
    COALESCE(active_users_count, 0) AS active_users_count,
    COALESCE(solutions_count, 0) AS solutions_count,
    COALESCE(likes_count, 0) AS solutions_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

Se precisar adicionar todas as colunas, para ficar exatamente igual à imagem, esta é a consulta:

Detalhes do SQL

WITH monthly_users 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)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS posts_count
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	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)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS active_users_count
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS solutions_count
	FROM user_actions ua
	WHERE ua.action_type = 15
	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)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS likes_count
	FROM user_actions ua
	WHERE ua.action_type = 2
	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)
)

SELECT
    mu.year,
    mu.month,   
    SUM(new_users_month + COALESCE(posts_count,0) + 
        COALESCE(active_users_count, 0) + 
        COALESCE(solutions_count, 0) + 
        COALESCE(likes_count, 0)) 
        over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS sum_total
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

[Konrad_Sopala]

Isso é exatamente o que eu estava procurando! Obrigado novamente, @michebs! Vocês já pensaram em criar algum tipo de coleção de consultas do Data Explorer de código aberto e compartilhá-la com os usuários do Discourse?

[codinghorror (Jeff Atwood)]

Sim, essa é a lista integrada de consultas incluída no Data Explorer

[Konrad_Sopala]

Sim, eu sei, mas é algo que você expande? Por exemplo, com base nas perguntas aqui no fórum.

[rishabh]

Podemos aceitar PRs para adicionar consultas; aqui está um exemplo:

FEATURE: Add a poll results query (#43)

Além disso, (Superseded) What cool data explorer queries have you come up with? e a lista do @SidV em discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub também têm um monte de consultas úteis!

[Konrad_Sopala]

Ótimo! Obrigado por compartilhar isso!