[SQL] Job Ad Metrics with Applicant Filter
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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
Overview: This question evaluates proficiency in SQL data manipulation and deduplication, focusing on aggregations, distinct counts, and conditional filtering of event records (views vs applies).
You are given a job_activity table that tracks candidate interactions with job postings. For each job_id, compute:
1) 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
2) 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.
Tables
job_activity(job_id INTEGER, candidate_id INTEGER, activity_type VARCHAR)
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.