Statistici utente più email

Dati e reportistica
1

Come potrei aggiungere una colonna per il tempo di lettura, i post letti e le visite dell’utente a questo?

In sostanza, vorrei combinare la seguente query con quella che hai pubblicato qui, oltre ad aggiungere il tempo di lettura (se possibile):

select user_id,
    count(1) as visits,
    sum(posts_read) as posts_read
from user_visits
where posts_read > 0
and visited_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
group by user_id
order by visits desc, posts_read desc

Nota: preferisco la possibilità di definire il parametro dell’intervallo come hai fatto nella tua query, ma vorrei avere l’utente della query che ho pubblicato come email completa (non solo il dominio dell’email).

Sono nuovo nel provare a scrivere query e capisco i parametri e sono stato in grado di modificare la tua per essere l’indirizzo email completo. Semplicemente non riesco a integrare le colonne visits e posts_read dalla query che ho condiviso, né ad aggiungere il tempo di lettura.

2 Mi Piace
2

Penso che avere i risultati per utente anziché raggruppati per dominio email renda questa una query leggermente diversa. Separiamo questo in un argomento a sé stante. :+1:

1 Mi Piace
3

Penso che qualcosa del genere dovrebbe funzionare:

-- [params]
-- string :interval = 7 days

WITH post_data AS (

    SELECT
        p.user_id,
        COUNT(p.id) AS total_posts,
        COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS topics,
        COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS posts,
        COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS pms
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval 
      AND p.user_id > 0 
      AND p.deleted_at ISNULL
      AND t.deleted_at ISNULL
      AND p.post_type <> 3
GROUP BY p.user_id

    ),
    
user_data AS (
    
    SELECT 
        uv.user_id,
        COUNT(*) AS visits,
        SUM(posts_read) AS posts_read,
        SUM(time_read)/60 AS time_read
    FROM user_visits uv
    WHERE uv.visited_at >= CURRENT_DATE - INTERVAL :interval
    GROUP BY uv.user_id
    )

SELECT 
    ud.user_id,
    ue.email,
    ud.visits,
    ud.posts_read,
    ud.time_read,
    pd.total_posts,
    pd.topics,
    pd.posts,
    pd.pms
FROM user_data ud 
  LEFT JOIN post_data pd ON pd.user_id = ud.user_id
  JOIN user_emails ue ON ue.user_id = ud.user_id AND ue.primary = TRUE
ORDER BY ud.posts_read DESC
1 Mi Piace
4

Questo è perfetto!! Grazie mille.

1 Mi Piace