Retention Rate SQL

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

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

Output details

• Use cohort_date, eligible_users, retained_users, and retention_rate_pct in that order.
• Treat energy_report_viewed, bill_estimate_created, and tariff_checked as qualifying activities.
• Include users whose signup_date is on or before 2024-03-31.
• Measure activity from signup plus one day, inclusive, through signup plus 31 days, exclusive.
• Round retention_rate_pct to two decimal places.