Your question is SQL Cohort Monthly Conversion Rates. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| application_idPK | INT | Unique loan application identifier |
| customer_id | INT | Customer associated with the application |
| submitted_at | TIMESTAMP | Application submission timestamp |
| channel | VARCHAR(30) | Acquisition channel |
| product_name | VARCHAR(40) | Elevate Credit loan product |
| Column | Type | Description |
|---|---|---|
| status_event_idPK | INT | Unique status event identifier |
| application_id | INT | Related loan application identifier |
| status | VARCHAR(20) | Status recorded at the event |
| changed_at | TIMESTAMP | Status change timestamp |