[SQL] Job Ad Metrics with Applicant Filter

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