Spotify's growth analytics team wants to measure how many new listeners return exactly seven days after signing up. For each market and subscription plan, calculate the Day-7 return rate as follows: - The eligible population includes listeners whose signup date was at least seven days before April 1, 2025. - A listener is a Day-7 returner only if they have at least one listening session exactly seven calendar days after signup. - Sessions before signup or on any other date do not count as Day-7 returns. - Multiple sessions by the same listener on Day 7 count only once. Return the columns in this exact order: `market`, `plan_type`, `eligible_listeners`, `day_7_returners`, and `day_7_return_rate`. Treat NULL plan values as `UNKNOWN`. Include groups with zero Day-7 returners. Round `day_7_return_rate` to four decimal places. Sort by `day_7_return_rate` descending, then `market` descending, then `plan_type` descending. ### Table Schema | Table | Column | Type | |---|---|---| | `listener_signups` | `listener_id` | integer | | `listener_signups` | `signup_date` | date | | `listener_signups` | `market` | varchar(30) | | `listener_signups` | `plan_type` | varchar(20) | | `listener_sessions` | `session_id` | integer | | `listener_sessions` | `listener_id` | integer | | `listener_sessions` | `session_date` | date | | `listener_sessions` | `minutes_streamed` | integer | ### Example Input: `listener_signups` | listener_id | signup_date | market | plan_type | |---:|---|---|---| | 1 | 2025-03-01 | US | free | | 2 | 2025-03-02 | US | premium | | 3 | 2025-03-10 | DE | free | | 4 | 2025-03-26 | DE | premium | ### Example Input: `listener_sessions` | session_id | listener_id | session_date | minutes_streamed | |---:|---:|---|---:| | 101 | 1 | 2025-03-01 | 30 | | 102 | 1 | 2025-03-08 | 45 | | 103 | 1 | 2025-03-08 | 10 | | 104 | 2 | 2025-03-09 | 20 | | 105 | 3 | 2025-03-17 | 0 | | 106 | 4 | 2025-04-02 | 50 | ### Example Output Explanation The US free group has one eligible listener, and that listener returned exactly seven days after signup, producing a rate of 1.0000. The US premium listener has no Day-7 session, producing 0.0000. The DE free listener also returns on Day 7, while the DE premium listener is excluded because the signup is less than seven days before the April 1 cutoff.
DROP TABLE IF EXISTS listener_sessions; DROP TABLE IF EXISTS listener_signups; CREATE TABLE listener_signups ( listener_id INT, signup_date DATE, market VARCHAR(30), plan_type VARCHAR(20) ); CREATE TABLE listener_sessions ( session_id INT, listener_id INT, session_date DATE, minutes_streamed INT ); INSERT INTO listener_signups VALUES (1,'2025-03-01','US','free'), (2,'2025-03-02','US','premium'), (3,'2025-03-10','DE','free'), (4,'2025-03-26','DE','premium'); INSERT INTO listener_sessions VALUES (101,1,'2025-03-01',30), (102,1,'2025-03-08',45), (103,1,'2025-03-08',10), (104,2,'2025-03-09',20), (105,3,'2025-03-17',0), (106,4,'2025-04-02',50);