题解 | #计算用户的平均次日留存率#

WITH daily_activity AS (
  SELECT DISTINCT device_id, date
  FROM question_practice_detail
),

activity_with_lead AS (
  SELECT 
    device_id,
    date,
    LEAD(date) OVER (PARTITION BY device_id ORDER BY date) AS next_date
  FROM 
    daily_activity
)

SELECT 
  ROUND(AVG(CASE 
              WHEN DATEDIFF(next_date, date) = 1 THEN 1.0 
              ELSE 0 
            END), 4) AS avg_ret
FROM 
  activity_with_lead;