Статистика пользователя плюс электронная почта

Создание сообщества Данные и отчетность
1

Как мне добавить столбцы для времени чтения, количества прочитанных постов и посещений пользователя в этот запрос?

По сути, я хотел бы объединить следующий запрос с тем, который вы опубликовали здесь, а также добавить время чтения (если это возможно):

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

Примечание: Я предпочитаю возможность задавать параметр интервала так, как это сделано в вашем запросе, но хотел бы, чтобы пользователь в запросе, который я отправил, указывался по полному адресу электронной почты (а не только по домену).

Я новичок в написании запросов, понимаю параметры и смог модифицировать ваш запрос так, чтобы он использовал полный адрес электронной почты. Однако мне не удается объединить столбцы посещений и прочитанных постов из моего запроса, а также добавить время чтения.

2

Я думаю, что получение результатов по пользователям, а не сгруппированных по домену электронной почты, делает этот запрос немного другим. Давайте вынесем это в отдельную тему. :+1:

3

Думаю, что-то вроде этого должно сработать:

-- [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
4

Это просто отлично!! Огромное вам спасибо.