Benutzerstatistiken plus E-Mail

Daten & Berichterstattung
1

Wie füge ich hier eine Spalte für die gelesene Zeit, gelesene Beiträge und Benutzerbesuche hinzu?

Im Wesentlichen möchte ich die folgende Abfrage mit der von Ihnen hier geposteten kombinieren und die gelesene Zeit hinzufügen (wenn möglich):

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

Hinweis: Ich bevorzuge die Möglichkeit, den Intervallparameter wie in Ihrer Abfrage zu definieren, aber ich hätte gerne den Benutzer aus der von mir geposteten Abfrage als vollständige E-Mail (nicht nur die E-Mail-Domäne).

Ich bin neu im Schreiben von Abfragen und verstehe die Parameter und konnte Ihre so modifizieren, dass sie die vollständige E-Mail-Adresse enthält. Ich kann die Spalten „visits“ und „posts_read“ aus der von mir geteilten Abfrage einfach nicht einbinden und auch nicht die Lesezeit hinzufügen.

2 „Gefällt mir“
2

Ich denke, dass die Ergebnisse pro Benutzer und nicht nach E-Mail-Domäne gruppiert sind, macht diese Abfrage etwas anders. Lassen Sie uns dies in ein eigenes Thema aufteilen. :+1:

1 „Gefällt mir“
3

Ich glaube, so etwas sollte funktionieren:

-- [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 „Gefällt mir“
4

Das ist perfekt!! Vielen Dank.

1 „Gefällt mir“