Calcular momentos totales de participación por mes usando Data Explorer

[Konrad_Sopala]

¡Hola, magos del Explorador de Datos!

¿Alguien ha intentado crear una consulta que proporcione el recuento total de momentos de participación por mes mediante consultas del Explorador de Datos?

Me refiero al número total de todas las publicaciones, soluciones y reacciones por mes.

¡Gracias de antemano!

[Rotblut (Bernhard)]

Mira si te gusta esto.

querys.md

[Konrad_Sopala]

No veo exactamente el que estoy buscando

[simon]

@Konrad_Sopala

¡Hola, magos del Explorador de Datos!

No soy un experto en el Explorador de Datos, pero me gusta escribir consultas cuando tengo tiempo. Antes de perderme del todo en esto, asumo que buscas un desglose mensual de las estadísticas. Si es así, algo como esto podría 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 ejecutar la consulta, debes proporcionar un valor para el parámetro start_date. Debe tener el formato yyyy-mm-dd. Sin embargo, la consulta solo extrae la parte del mes de esa fecha. La razón por la que se necesita un parámetro de fecha de inicio es porque la consulta se agotará en el tiempo de ejecución en un sitio grande si se ejecuta contra todo el período de tiempo en el que el sitio ha estado en línea. Utilizando el parámetro de fecha de inicio, puedo ejecutarlo en Meta durante un período de un par de años sin que se agote el tiempo.

Una cosa a tener en cuenta con la consulta es que la columna active_users_count devuelve el número de usuarios únicos que iniciaron sesión en el sitio durante un mes; no verifica si estos usuarios realizaron alguna acción en el sitio (por ejemplo, dieron me gusta a una publicación o crearon una publicación). Posiblemente la consulta podría hacer eso, pero me preocupa que surjan problemas de tiempo de espera.

Hazmelo saber si esto no es el tipo de datos que buscas. Si la idea general es correcta, avísame si necesitas agregar datos adicionales a la consulta o si notas algo que parezca incorrecto en sus resultados.

[Konrad_Sopala]

¡Gracias @simon por compartir eso, es realmente útil y es casi exactamente lo que estoy buscando! ¿Podría preguntarte si es posible hacer un cambio en la consulta? No me gusta especificar la fecha de inicio. Me gustaría sumar todos esos valores (publicaciones, usuarios, soluciones, me gusta) y obtener el resultado de la consulta de esa manera, por mes:

[simon]

@Konrad_Sopala

No me gusta especificar la fecha de inicio. Me gustaría sumar todos esos valores (publicaciones, usuarios, soluciones, me gusta) y obtener el resultado de la consulta de esa manera, por mes:

Esto podría ser posible. Lo intentaré. El parámetro de fecha de inicio se añadió a la consulta en el último momento, ya que descubrí que la consulta se agotaría por tiempo de espera si la ejecutaba en Meta para todo el tiempo que el sitio ha estado en línea. Podría haber formas de mejorar la eficiencia de la consulta para eliminar el problema del tiempo de espera. De lo contrario, la consulta debería permitir establecer un intervalo de tiempo en lugar de solo solicitar una fecha de inicio. De esa manera, podrías obtener todos los datos de un sitio ejecutando la consulta varias veces con diferentes intervalos de tiempo.

[Konrad_Sopala]

¡Claro! Te estaré esperando cuando tengas tiempo para hacer ese ajuste.

[michebs (Michelle)]

Hola Konrad,

A continuación se muestra la 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 

Si necesitas agregar todas las columnas, para que sea exactamente igual a la imagen, esta es la consulta:

Detalles 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]

¡Eso es exactamente lo que estaba buscando! ¡Gracias de nuevo @michebs! ¿Han pensado en crear alguna colección de código abierto de consultas de Data Explorer y compartirla con los usuarios de Discourse?

[codinghorror (Jeff Atwood)]

Sí, esa es la lista integrada de consultas que incluye el Explorador de datos

[Konrad_Sopala]

sí, lo sé, pero ¿es algo que amplíes? por ejemplo, basándote en las preguntas aquí del foro

[rishabh]

Podemos aceptar PRs para agregar consultas, aquí hay un ejemplo:

FEATURE: Add a poll results query (#43)

Además, (Superseded) What cool data explorer queries have you come up with? y la lista de @SidV discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub también tienen un montón de consultas útiles.

[Konrad_Sopala]

¡Genial! ¡Gracias por compartir eso!