Tune SQL for Top Jobs per Recruiter

You need to optimize a slow-running query. What steps would you take?

Using the provided schema, write an efficient query that returns qualifying open jobs and explain how you would investigate and validate the optimization. Include only submitted applications created on or after 2026-01-01, and retain jobs with at least two such applications.

Output

1. One row per qualifying job with job_id, title, recruiter_name, submitted_application_count, unique_candidate_count, latest_submission, and recruiter_rank
2. Include only the top two jobs per recruiter, preserving ties
3. Order by recruiter name, rank, and job ID

Output details

• Rank jobs within each recruiter by submitted_application_count descending, then latest_submission descending, preserving rank ties.
• Order recruiter_name, recruiter_rank, and job_id ascending, placing NULL recruiter_name values last.
• Count only applications associated with an existing candidate record.
• Count every qualifying application, while counting each candidate once for unique_candidate_count.