Retention Rate SQL | Dataford Interview Questions

Wise Energy wants to measure whether customers return to the product after signing up. Write a PostgreSQL query that calculates 30-day retention by signup cohort, using activity recorded in the Wise Energy product.

A user is retained if they perform at least one qualifying activity during days 1 through 30 after signup, inclusive. Use 2024-04-30 as the reporting date, so include only users whose full 30-day observation period has elapsed.

Requirements

Return one row per signup date.
Include eligible users, retained users, and retention percentage.
Count each user once, even if they have multiple qualifying events.
Include cohorts with no qualifying activity, and order results chronologically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Wise Energy user identifier
signup_date	DATE	Date the user signed up

energy_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of Wise Energy activity
occurred_at	TIMESTAMP	Timestamp when the activity occurred

Your question is Retention Rate SQL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem