SQL for Funding Conversion | Dataford Interview Questions

Write a SQL query to measure weekly application-to-funding conversion by channel, including counts at each stage and conversion rates. Use the applications and application_events tables. Attribute each application to the week in which it was submitted, and count an application once per stage even if duplicate events exist.

Output

One row per application week and channel, including channels with no matching events.
Columns: week_start, channel, application_count, approved_count, funded_count, approval_rate_pct, funding_rate_pct, and approval_to_funding_rate_pct.
Rates must be percentages rounded to two decimals. Sort by week_start, then channel, with null channels last.

Schema

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
channel	VARCHAR(30)	Acquisition channel associated with the application
applied_at	TIMESTAMP	Timestamp when the application was submitted

application_events

Column	Type	Description
event_idPK	INT	Unique application event identifier
application_id	INT	Application associated with the event
event_type	VARCHAR(20)	Lifecycle event type, such as approved or funded
occurred_at	TIMESTAMP	Timestamp when the event occurred

Your question is SQL for Funding Conversion. 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