Company: Google | Difficulty: hard
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.
Day 1 of the activation window is the signup date itself, so day 7 falls exactly six days after signup.
Return user_id, signup_date, active_days_first_7, and returned_on_day_7. Count each calendar day once and exclude events before signup. Sort the results by user_id in descending order.
user_id
signup_date
active_days_first_7
returned_on_day_7
app_events
event_id
event_name
occurred_at
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');
← All SQL Challenges · Google Questions · Learn SQL Free