Trial Users Who Became Buyers

SQLHardSenior level~15 min

Problem

An app records each user action in `Activity` with an `action_type` of either `Trial` or `Purchase`. For users whose **very first action** (earliest `acted_at`) was a `Trial`, count how many `Purchase` actions they later performed. Output `user_ref` and `purchase_count`, ordered by `purchase_count` descending and then `user_ref` descending.

Tables

Example rows — the live problem includes the full dataset.

Activity

user_ref	acted_at	action_id	action_type
11	2024-01-01 09:00:00	1	Trial
11	2024-01-05 09:00:00	2	Purchase
11	2024-01-08 09:00:00	3	Purchase

Expected output

Your answer should return 2 rows with the columns user_ref, purchase_count.

Starter code (SQL)

SELECT *
FROM Activity;

Solve this SQL question free

Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.