Compute weighted response rates by job category

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).

Compute weighted response rates and Wilson confidence intervals by job category

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.

Table job_postings

column	type	constraints
job_id	INT	PRIMARY KEY
job_category	VARCHAR(50)	NOT NULL
invitations_sent	INT	NOT NULL
provider_responses	INT	NOT NULL
region	VARCHAR(50)	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	invitations_sent	job_category	job_id	provider_responses	region
2025-02-10	10	Plumbing	1	7	US-East
2025-03-15	5	Plumbing	2	4	US-West
2025-02-20	8	Electrical	3	4	US-East
2025-04-05	2	Electrical	4	1	US-West
2025-01-25	20	Landscaping	5	15	US-East
2025-03-30	3	Landscaping	6	1	US-West
2025-02-01	0	Cleaning	7	0	US-East
2025-03-02	4	Cleaning	8	3	US-West
2025-03-01	6	Plumbing	9	8	US-East
2025-02-15	-1	Electrical	10	0	US-East
2024-12-31	5	Landscaping	11	2	US-East
2025-05-10	7	Roofing	12	3	US-West
2025-05-12	1	Roofing	13	1	US-West
2025-05-20	5	Cleaning	14	0	US-East
2025-04-10	4	Gardening	15	-1	US-East

Expected output

Top 5 job categories (after filtering invalid rows) with invitation-weighted response rate, 95% Wilson confidence interval bounds, and a global verification flag showing that the job-level weighted average equals the overall response rate within 1e-9. Based on the sample data, invalid rows are dropped and the valid job_ids are 1,2,3,4,5,6,8,12,13,14. The totals by category are:

• Plumbing: 15 invitations, 11 responses
• Landscaping: 23 invitations, 16 responses
• Electrical: 10 invitations, 5 responses
• Roofing: 8 invitations, 4 responses
• Cleaning: 9 invitations, 3 responses All five categories are returned, ordered by weighted_response_rate descending, breaking ties by ci_lower descending. The verification_passed flag is TRUE because both methods of computing the overall response rate yield 0.6 exactly.

category_invitations	category_responses	ci_lower	ci_upper	job_category	verification_passed	weighted_response_rate
15	11	0.4805	0.891	Plumbing	true	0.7333
23	16	0.4913	0.844	Landscaping	true	0.6957
10	5	0.2366	0.7634	Electrical	true	0.5
8	4	0.2152	0.7848	Roofing	true	0.5
9	3	0.1206	0.6458	Cleaning	true	0.3333

Hints

1. Start with a CTE that filters out invalid rows (bad counts or dates) so that all downstream logic uses only valid_jobs.
2. 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.
3. In PostgreSQL, cast to NUMERIC before calling ROUND(value, decimal_places).