Compute weighted response rates by job category
Company: Thumbtack
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given a CSV with one row per job posting and the following columns: job_id, job_category, invitations_sent (integer >= 0), provider_responses (integer >= 0), region, created_at (ISO date). Write pandas code to: (1) compute a per-job response_rate = provider_responses / invitations_sent, treating invitations_sent = 0 as missing and excluding those rows; (2) produce an invitation-weighted response rate by job_category with 95% Wilson score confidence intervals; (3) return the top 5 job_category values ranked by the weighted response rate, breaking ties by the lower bound of the CI; (4) robustly handle outliers where provider_responses > invitations_sent, negative values, or impossible dates by logging and dropping them; and (5) verify that the job-level weighted average equals the overall response rate computed from aggregated numerators/denominators (within 1e-9).
Overview: This question evaluates a candidate's data manipulation and statistical estimation skills, including pandas-based aggregation, invitation-weighted rate calculation, confidence-interval computation, and robust handling of data-quality issues, in the Data Manipulation (SQL/Python) domain.
Read the full Thumbtack Data Scientist interview experience this question came from
You are given a table job_postings with one row per job posting and the following columns:
- job_id (unique per job)
- job_category (logical category for the job)
- invitations_sent (integer, may contain bad data)
- provider_responses (integer, may contain bad data)
- region
- created_at (DATE)
Write a single SQL query that:
1) Computes a per-job response_rate = provider_responses / invitations_sent, but only for rows where invitations_sent > 0.
2) Treats the following rows as invalid data and excludes them from all calculations:
- invitations_sent <= 0
- provider_responses < 0
- provider_responses > invitations_sent
- created_at before '2025-01-01' or after '2025-06-01' (consider these dates impossible for this dataset).
3) For the remaining valid rows, computes an invitation-weighted response rate by job_category: sum(provider_responses) / sum(invitations_sent) for each category.
4) For each job_category, computes a 95% Wilson score confidence interval for the response rate, using the aggregated counts provider_responses as successes and invitations_sent as trials. Use z = 1.96 and the standard Wilson interval formula.
5) Ranks job_category values by the weighted response rate in descending order, breaking ties by the lower bound of the Wilson confidence interval in descending order, and returns only the top 5 categories.
6) Verifies that the invitation-weighted average of the per-job response_rate values (weighted by invitations_sent) is equal to the overall response rate computed directly from total provider_responses / total invitations_sent across all valid jobs, within a tolerance of 1e-9. Return a boolean indicator column verification_passed that is TRUE if this check passes and FALSE otherwise (the same value may appear on every returned row).
Return one row per job_category with: job_category, total invitations_sent, total provider_responses, weighted_response_rate, ci_lower, ci_upper, and verification_passed, limited to the top 5 categories by the ranking described above.
Use PostgreSQL numeric casts for rate calculations that are rounded to a fixed number of decimal places.
Tables
job_postings(job_id INT, job_category VARCHAR(50), invitations_sent INT, provider_responses INT, region VARCHAR(50), created_at DATE)
Hints
- Start with a CTE that filters out invalid rows (bad counts or dates) so that all downstream logic uses only valid_jobs.
- Compute the Wilson confidence interval using aggregated successes (sum of provider_responses) and trials (sum of invitations_sent) per category, and use a separate CTE to compute and compare the two overall response rates for the verification flag.