求助：如何用SQL计算Desktop设备Game页面的14天3日平均留存率？

Let’s work through this step by step—you’re aiming to get a single numeric value for the average 3-day retention rate of Desktop users returning to the Game page over a 14-day window. First, let’s break down what’s off with your current query, then build the correct one.

What’s Wrong With Your Original SQL #

Your query has both syntax and logical issues:

• Syntax errors: Missing comma after sessions.visitorId, invalid COUNT(sessions.visitor > 1 BETWEEN...) structure, and incorrect date condition formatting.
• Logical gaps: You didn’t distinguish between "initial cohort users" (users active on a given day) and "returning users" (those who came back to the Game page 3 days later)—the core of retention calculation.

Correct Approach to 3-Day Retention #

3-day retention for a cohort date means:

(Number of users who visited on Day X AND returned to the Game page on Day X+3) / (Total users who visited on Day X)

We’ll calculate this for each of the 14 cohort dates, then average those rates to get your final single value.

Working SQL Query #

WITH cohort_users AS (
    -- Step 1: Identify all Desktop users in our 14-day cohort window (2018-04-26 to 2018-05-09)
    SELECT
        visitorId,
        sessionDate AS cohort_date
    FROM sessions
    WHERE
        deviceType = 'Desktop'
        AND sessionDate BETWEEN '2018-04-26' AND DATE_ADD('2018-04-26', INTERVAL 13 DAY)
    GROUP BY visitorId, sessionDate -- Group to avoid duplicate user entries per day
),
cohort_sizes AS (
    -- Step 2: Get total users per cohort date
    SELECT
        cohort_date,
        COUNT(DISTINCT visitorId) AS total_cohort_users
    FROM cohort_users
    GROUP BY cohort_date
),
returning_users AS (
    -- Step 3: Find users who returned to the Game page exactly 3 days after their cohort date
    SELECT
        cu.cohort_date,
        COUNT(DISTINCT cu.visitorId) AS returning_user_count
    FROM cohort_users cu
    JOIN pageviews pv
        ON cu.visitorId = pv.visitorId
        AND pv.pageviewDate = DATE_ADD(cu.cohort_date, INTERVAL 3 DAY)
        AND pv.pageType = 'Game'
    GROUP BY cu.cohort_date
)
-- Step 4: Calculate daily retention rates and average them
SELECT
    AVG(COALESCE(returning_user_count / total_cohort_users, 0)) AS average_3day_retention_rate
FROM cohort_sizes cs
LEFT JOIN returning_users ru ON cs.cohort_date = ru.cohort_date;

Key Notes About This Query #

• COALESCE ensures dates with zero returning users don’t break the average (treats those as 0 retention).
• If you need "returned within 3 days" (instead of exactly Day X+3), replace pv.pageviewDate = DATE_ADD(...) with pv.pageviewDate BETWEEN DATE_ADD(cu.cohort_date, INTERVAL 1 DAY) AND DATE_ADD(cu.cohort_date, INTERVAL 3 DAY).
• The result will be a single decimal value (e.g., 0.15 for 15% average retention).

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