Coding questions · LinkedIn
[SQL] Job Ad Metrics with Applicant Filter
This question evaluates proficiency in SQL data manipulation and deduplication, focusing on aggregations, distinct counts, and conditional filtering of event records (views vs applies).
Job Ad Metrics Analysis Task
Table Structure
Table: job_activity
job_id INT candidate_id INT activity_type VARCHAR -- can be 'view' or 'apply'
Requirements
Part 1: Basic Metrics
Calculate for each job_id:
Total number of activities
Number of unique candidates who viewed the job
Number of unique candidates who applied to the job
Part 2: Filtered Metrics
Exclude candidates who only applied but never viewed any job
Recalculate the same metrics from Part 1 with this filter applied
Address the edge case: How to handle candidates who apply to multiple jobs but don't view any
Job Activity Metrics With Candidate Filter
You are given a job_activity table that tracks candidate interactions with job postings. For each job_id, compute:
-
Base metrics (using all events):
- total_activities: total number of activity rows
- unique_viewers: number of distinct candidates with activity_type = 'view' on that job
- unique_applicants: number of distinct candidates with activity_type = 'apply' on that job
-
Filtered metrics (after excluding certain candidates):
- Exclude all events from any candidate who has zero 'view' events across the entire table (i.e., candidates who only apply and never view any job).
- This exclusion applies across all jobs: if such a candidate applies to multiple jobs, exclude all of their events for all jobs.
- Recompute the same metrics as in (1) on this filtered event set and return them as:
- filtered_total_activities
- filtered_unique_viewers
- filtered_unique_applicants
Return one row per job_id containing both the base metrics and the filtered metrics.
Table job_activity
| column | type | constraints |
|---|---|---|
| job_id | INTEGER | |
| candidate_id | INTEGER | |
| activity_type | VARCHAR |
Sample data:
| activity_type | candidate_id | job_id |
|---|---|---|
| view | 10 | 1001 |
| apply | 10 | 1001 |
| view | 10 | 1002 |
| view | 11 | 1001 |
| apply | 11 | 1002 |
| apply | 12 | 1001 |
| apply | 12 | 1003 |
| view | 13 | 1003 |
| view | 14 | 1002 |
| apply | 14 | 1002 |
| apply | 15 | 1002 |
| view | 15 | 1003 |
| view | 17 | 1003 |
| apply | 17 | 1003 |
| view | 17 | 1001 |
Expected output
One row per job_id with base metrics (total_activities, unique_viewers, unique_applicants) and filtered metrics (filtered_total_activities, filtered_unique_viewers, filtered_unique_applicants), where filtered metrics exclude all activity from candidates who have zero 'view' events in the entire dataset (including those who apply to multiple jobs).
| filtered_total_activities | filtered_unique_applicants | filtered_unique_viewers | job_id | total_activities | unique_applicants | unique_viewers |
|---|---|---|---|---|---|---|
| 4 | 1 | 3 | 1001 | 5 | 2 | 3 |
| 5 | 3 | 2 | 1002 | 5 | 3 | 2 |
| 4 | 1 | 3 | 1003 | 5 | 2 | 3 |
Hints
- First aggregate base metrics per job_id using conditional COUNT(DISTINCT ...) for viewers and applicants.
- Identify the set of candidates who have at least one 'view' event anywhere in the table.
- Join job_activity to that candidate set to compute filtered metrics, then join filtered metrics back to the base metrics per job.