The Retention Report
SQL · Advanced · about 35 minutes
Mission
Product leadership wants to know whether new users come back, and you own the retention report. For every user who signed up, the report has to say whether they were active exactly one, seven and thirty days after their own signup date, and give each user a compact pattern that makes the result easy to scan. The report is only trustworthy if it keeps the users who never returned and measures each person against their own start date rather than the calendar.
Objective
Return one row per signed-up user with three 1/0 retention flags and the combined pattern, using the exact output columns the challenge specifies.
Skills involved
- Outer joins that keep every user
- Per-user date arithmetic
- Conditional aggregation into 1/0 flags
- Assembling a combined result field
Suggested preparation
Preparation is optional. The battle is written and graded in the regular challenge editor: open the battle challenge.