Redshift查询编写求助：2月用户数据统计需求

Hi there! Let's work through your Redshift query needs step by step. I'll break down your requirements, data context, current code limitations, and provide a revised solution that aligns with your desired output.

Your Core Requirements #

• Count users who joined the service in February (Seoul time)
• Identify users who returned after signing up, plus calculate their return timing (e.g., Day 1, Day 2 post-signup)
• Aggregate daily read/impression counts per user for each visit day

Data Context #

Tables & Key Columns #

1. users Table

Note: created_at stores account creation time in US time zone, but your local working time zone is Seoul (Asia/Seoul).

2. user_content_action_by_traffic_source Table

Note: created_at here records the time of the read/impression event, also in US time zone.

Example Query & Output #

Sample Query:

SELECT u.created_at, u.id, 
       SUM(CASE WHEN s.action_type = 'read' THEN 1 ELSE 0 END) AS read, 
       SUM(CASE WHEN s.action_type = 'impression' THEN 1 ELSE 0 END) AS impression 
FROM users u 
INNER JOIN user_content_action_by_traffic_source s ON u.id = s.user_id 
WHERE u.created_at >= CURRENT_DATE - INTERVAL '2 days' 
GROUP BY 1, 2 
ORDER BY 1 LIMIT 10

Sample Output:

Your Current Work #

Initial Query (Known Gaps) #

WITH t1 (SELECT convert_timezone('Asia/Seoul', u.created_at) AS created_at, 
                u.id AS new_user, 
                COUNT(DISTINCT date_trunc('day', convert_timezone('Asia/Seoul', action.created_at))) AS num_of_days_visited 
         FROM users u 
         JOIN user_content_action_by_traffic_source action ON u.id = action.user_id 
         WHERE DATE(u.created_at) >= '2018-02-01' AND DATE(u.created_at) <= '2018-02-28' 
           AND action.created_at >= u.created_at 
           AND action.created_at <= u.created_at + INTERVAL '4 weeks' 
         GROUP BY 1, 2 
         ORDER BY 1, 2) 
SELECT DATE(t1.created_at), t1.new_user, 
       DATE(convert_timezone('Asia/Seoul', action.created_at)) AS date_visited, 
       SUM(CASE WHEN action.action_type = 'read' THEN 1 ELSE 0 END) AS Read, 
       SUM(CASE WHEN action.action_type = 'impression' THEN 1 ELSE 0 END) AS Imp 
FROM t1 
JOIN user_content_action_by_traffic_source action ON t1.new_user = action.user_id 
WHERE convert_timezone('Asia/Seoul', action.created_at) >= t1.created_at 
  AND convert_timezone('Asia/Seoul', action.created_at) <= t1.created_at + INTERVAL '4 weeks' 
  AND action.content_type = 'post' 
  AND t1.num_of_days_visited = 2 
GROUP BY 1, 2, 3 
ORDER BY 1, 2, 3

Current Output #

Desired Output Format #

Revised Solution Query #

The key issues with your current code are inconsistent timezone handling and lack of pivoting to combine multiple visits per user into a single row. Here's a revised query that fixes these and meets all your requirements:

-- Step 1: Standardize all timestamps to Seoul time, aggregate daily actions per user
WITH user_daily_actions AS (
    SELECT
        u.id AS new_user,
        DATE(convert_timezone('Asia/Seoul', u.created_at)) AS signup_date_seoul,
        DATE(convert_timezone('Asia/Seoul', action.created_at)) AS visit_date_seoul,
        SUM(CASE WHEN action.action_type = 'read' THEN 1 ELSE 0 END) AS daily_read,
        SUM(CASE WHEN action.action_type = 'impression' THEN 1 ELSE 0 END) AS daily_imp,
        -- Calculate days since signup (for return timing requirement)
        DATEDIFF(day, convert_timezone('Asia/Seoul', u.created_at), convert_timezone('Asia/Seoul', action.created_at)) AS days_since_signup,
        -- Assign a sequential number to each user's visit days (for pivoting)
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY visit_date_seoul) AS visit_sequence
    FROM users u
    JOIN user_content_action_by_traffic_source action
        ON u.id = action.user_id
        AND action.created_at >= u.created_at -- Only actions after signup
        AND action.created_at <= u.created_at + INTERVAL '4 weeks' -- Limit to 4 weeks post-signup
        AND action.content_type = 'post' -- Filter for post-related actions
    WHERE DATE(convert_timezone('Asia/Seoul', u.created_at)) BETWEEN '2018-02-01' AND '2018-02-28' -- Feb users (Seoul time)
    GROUP BY u.id, signup_date_seoul, visit_date_seoul, days_since_signup
),
-- Step 2: Pivot first 2 visit days into columns (matches your desired output)
pivoted_visits AS (
    SELECT
        signup_date_seoul AS date,
        new_user,
        MAX(CASE WHEN visit_sequence = 1 THEN visit_date_seoul END) AS date_visited,
        MAX(CASE WHEN visit_sequence = 1 THEN daily_read END) AS read,
        MAX(CASE WHEN visit_sequence = 1 THEN daily_imp END) AS imp,
        MAX(CASE WHEN visit_sequence = 2 THEN visit_date_seoul END) AS date_visited_2,
        MAX(CASE WHEN visit_sequence = 2 THEN daily_read END) AS read2,
        MAX(CASE WHEN visit_sequence = 2 THEN daily_imp END) AS imp2
    FROM user_daily_actions
    GROUP BY signup_date_seoul, new_user
),
-- Step 3: Calculate total number of Feb-joined users (fulfills first requirement)
total_feb_users AS (
    SELECT COUNT(DISTINCT id) AS total_feb_new_users
    FROM users
    WHERE DATE(convert_timezone('Asia/Seoul', created_at)) BETWEEN '2018-02-01' AND '2018-02-28'
)
-- Final output: combined pivoted visits + total user count
SELECT
    pv.*,
    tfu.total_feb_new_users
FROM pivoted_visits pv
CROSS JOIN total_feb_users tfu
ORDER BY pv.date, pv.new_user;

Key Improvements: #

1. Timezone Consistency: All date filters and calculations use Seoul-time converted timestamps to avoid off-by-one errors (e.g., a US-time Feb 28 might be Seoul-time March 1).
2. Pivoting: Uses ROW_NUMBER() to label each user's visit days, then pivots the first two visits into separate columns to match your desired output structure.
3. Return Timing: Adds days_since_signup to directly show how many days after joining the user returned.
4. Full Requirement Coverage: Includes the total count of February-joined users and aggregates daily read/impression counts per visit.

If you need to handle more than 2 visit days, simply extend the pivot logic by adding more MAX(CASE WHEN visit_sequence = N ...) columns. Let me know if you need adjustments to date ranges, filters, or output fields!

内容的提问来源于stack exchange，提问作者Sung Kim