Statistiche giornaliere, settimanali o totali per utente in un intervallo di tempo specificato

Comunità Dati e reportistica
1 
-- copertura: 'week', 'all' o 'date'
-- [parametri]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :copertura = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :copertura::text = 'week' THEN ps.week::text
WHEN :copertura::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :copertura::text = 'week' THEN ps.week::text
WHEN :copertura::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :copertura::text = 'week' THEN ps.week::text
WHEN :copertura::text = 'all' THEN '-1'
ELSE ps.date::text
END

Puoi utilizzare questa query per estrarre statistiche giornaliere, settimanali o totali per utente in un intervallo di tempo specificato.
Parametri di input:

  • start_date (stringa) es. ‘2019-12-31’
  • end_date (stringa) es. ‘2019-12-31’
  • copertura (enum) una tra 1) ‘date’ per giornaliero, 2) ‘week’ per settimanale o 3) ‘all’ per totale

Campi di output:

  • id (user_id)
  • username
  • created_at (data di creazione dell’utente)
  • period (stringa di data per la copertura ‘date’, numero della settimana (es. 26-31 dic = 52) per la copertura ‘week’, o -1 per la copertura ‘all’)
  • replies (numero di post che non sono il primo post di un argomento)
  • topics
  • likes_received
  • likes_given
  • posts_read
  • time_read
  • visits

Probabilmente dovrai utilizzare l’API per accedere all’intero risultato di questa query (specialmente per dati giornalieri su un lungo periodo). Consulta questo post per ulteriori dettagli su come eseguire query tramite l’API.

Puoi utilizzare questa query per generare un set di dati relativo al coinvolgimento giornaliero degli utenti.

7 Mi Piace