Active User RetentionCompany: Spotify | Difficulty: hard

Spotify wants to measure how many users remained active from one month to the next.

Find the user IDs of users who performed at least one action during January 2024 and at least one action during February 2024 in the user_actions table. Each user should appear only once, even if they performed multiple actions in either month.

Ignore actions with missing user_id or missing action_date values. Sort the retained user IDs in descending order.

Table Schema

Table	Column	Type
`user_actions`	`action_id`	integer
`user_actions`	`user_id`	integer
`user_actions`	`action_date`	timestamp
`user_actions`	`action_type`	varchar(30)

Example Input: `user_actions`

action_id	user_id	action_date	action_type
1	101	2024-01-05 10:00:00	play
2	101	2024-02-08 12:00:00	like
3	102	2024-01-10 09:00:00	play
4	103	2024-02-12 15:00:00	share
5	101	2024-02-20 18:00:00	save
6	102	2024-03-01 08:00:00	play

Example Output Explanation

User 101 is retained because they performed an action in both January and February 2024. User 102 was active only in January during the required two-month period, and user 103 was active only in February, so neither is returned.

SchemaDROP TABLE IF EXISTS user_actions; CREATE TABLE user_actions ( action_id INT, user_id INT, action_date TIMESTAMP, action_type VARCHAR(30) ); INSERT INTO user_actions VALUES (1, 101, '2024-01-05 10:00:00', 'play'), (2, 101, '2024-02-08 12:00:00', 'like'), (3, 102, '2024-01-10 09:00:00', 'play'), (4, 103, '2024-02-12 15:00:00', 'share'), (5, 101, '2024-02-20 18:00:00', 'save'), (6, 102, '2024-03-01 08:00:00', 'play');← All SQL Challenges · Spotify Questions · Learn SQL Free