Google Play wants to measure activation quality. For every user whose first event was in January 2025, return the signup date, the number of distinct active days in the first 7 days including signup, and whether the user returned on day 7. Return `user_id`, `signup_date`, `active_days_first_7`, and `returned_on_day_7`. Count each calendar day once and exclude events before signup. ### Table Schema | Table | Column | Type | |---|---|---| | `app_events` | `event_id` | integer | | `app_events` | `user_id` | integer | | `app_events` | `event_name` | varchar(30) | | `app_events` | `occurred_at` | timestamp | ### Example Input: `app_events` | event_id | user_id | event_name | occurred_at | |---:|---:|---|---| | 1 | 1 | open | 2025-01-02 08:00 | | 2 | 1 | open | 2025-01-02 09:00 | | 3 | 1 | buy | 2025-01-03 10:00 | | 4 | 1 | open | 2025-01-09 10:00 | | 5 | 2 | open | 2025-01-10 11:00 | | 6 | 2 | open | 2025-01-16 11:00 | | 7 | 3 | open | 2025-01-31 12:00 | | 8 | 3 | open | 2025-02-01 12:00 | ### Example Output Explanation User 1 signed up on January 2 and was active on two distinct days, January 2 and January 3. User 2 returned on January 16, which is day 7 of their activation window. User 3 signed up on January 31 and did not return on day 7.
DROP TABLE IF EXISTS app_events; CREATE TABLE app_events (event_id INT, user_id INT, event_name VARCHAR(30), occurred_at TIMESTAMP); INSERT INTO app_events VALUES (1,1,'open','2025-01-02 08:00'), (2,1,'open','2025-01-02 09:00'), (3,1,'buy','2025-01-03 10:00'), (4,1,'open','2025-01-09 10:00'), (5,2,'open','2025-01-10 11:00'), (6,2,'open','2025-01-16 11:00'), (7,3,'open','2025-01-31 12:00'), (8,3,'open','2025-02-01 12:00');