SQL Cohort Monthly Conversion Rates

Elevate Credit wants to measure how quickly loan applications convert to an approved loan. Write a PostgreSQL query that reports approval conversion by application-month cohort from January through April 2024.

An application belongs to the month of its submitted_at timestamp. An application converts in the month of its first approved status event. Multiple approval events for one application must count only once. Applications without an approval event remain in the cohort denominator but contribute no conversions.

Requirements

1. Create one row for each cohort month and each reporting month from the cohort month through April 2024.
2. Return the cohort size, number of applications first approved in each reporting month, and conversion rate as a percentage.
3. Use the total applications in the cohort as the denominator, including applications with no status history or no approval.
4. Exclude approval events after April 2024 and sort by cohort month, then reporting month.

Output details

• Return columns in this order: cohort_month, reporting_month, cohort_size, converted_applications, conversion_rate_pct.
• Sort cohort_month ascending, then reporting_month ascending.
• Round conversion_rate_pct to two decimal places.
• Treat the earliest approval as the event ordered by changed_at, breaking ties with status_event_id.
• Include submissions from 2024-01-01 through before 2024-05-01, and approval events before 2024-05-01 only.