Buon pomeriggio,
Vorrei raccogliere dati su specifici nomi utente riguardo ai like, ai like ricevuti, ai post e alle soluzioni per un determinato intervallo di date, suddivisi per giorno. Avete suggerimenti su questo tipo di reportistica? Immagino che dovrei sfruttare il plugin Data Explorer; qualcuno ha già una query pronta per questo tipo di lavoro? Apprezzo qualsiasi feedback!
Avevo una query molto simile a quella che state cercando sul mio sito di sviluppo locale. L’ho modificata un po’ per soddisfare le vostre esigenze. Una cosa che ho cercato di fare è permettere alla query di essere eseguita su sia l’attività avvenuta nei topic regolari, sia l’attività avvenuta nei messaggi privati (PM). Parte della mia motivazione per farlo era voler analizzare la mia attività nel corso di un periodo. Potere interrogare per topic regolari o PM ha senso nel mio caso. Il modo in cui la query gestisce le soluzioni quando la casella personal_messages è selezionata non è ideale: la colonna solutions_provided restituirà semplicemente NULL in quel caso. Forse l’interruttore tra topic regolari e PM dovrebbe essere rimosso dalla query.
Questa query non è eccessivamente complessa, ma è piuttosto lunga. Se notate problemi nei risultati, fatemelo sapere.
La query richiede di fornire tre parametri prima di poter essere eseguita: username, start_date e end_date. I parametri di data devono essere nel formato ‘aaaa-mm-gg’. Ad esempio 2020-03-13. Il parametro personal_messages ha come valore predefinito false. Selezionate la casella personal_messages della query se volete vedere i risultati per l’attività avvenuta nei messaggi privati.
--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_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 = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
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 topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_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 = 15
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
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
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC
Perfetto! Grazie @simon!