Your question is Candidate Funnel Conversion by Channel. Start with the requirements and the three 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.
You are given recruiting pipeline data from Micro1 and asked to write a PostgreSQL query that calculates the conversion rate of candidates from the initial AI screening stage to the final technical assessment stage, grouped by sourcing channel. Treat a candidate as having entered the funnel if they have an ai_screening_started event, and as converted if they also have a technical_assessment_completed event. Return the sourcing channel, the number of candidates who started AI screening, the number who reached the final technical assessment, and the conversion rate as a percentage rounded to 2 decimals.
| Column | Type | Description |
|---|---|---|
| candidate_idPK | INT | Unique candidate identifier |
| sourcing_channel | VARCHAR(50) | Channel where the candidate originated |
| created_at | DATE | Date the candidate profile was created |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| candidate_id | INT | Candidate associated with the stage event |
| stage_name | VARCHAR(60) | Name of the funnel stage event |
| event_time | TIMESTAMP | Timestamp when the event occurred |
| Column | Type | Description |
|---|---|---|
| channel_namePK | VARCHAR(50) | Canonical sourcing channel name |
| is_active | BOOLEAN | Whether the sourcing channel is active |