SQL for Highest Error Rates | Dataford Interview Questions

Write a SQL query to identify payroll runs with the highest error rates by customer, state, and pay period at Miter. Consider completed runs with more than zero employees processed. Calculate error rate as errors divided by employees processed, treat a missing error count as zero, and include ties.

Output

One row per highest-error payroll run, with customer_name, state, pay_period_start, run_id, error_rate, and rate_rank.
Include only tied highest-rate runs within each customer, state, and pay period, ordered by customer, state, pay period, and run ID.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
customer_status	VARCHAR(20)	Current customer status

payroll_runs

Column	Type	Description
run_idPK	INT	Unique payroll run identifier
customer_id	INT	Customer associated with the payroll run
state	VARCHAR(2)	Payroll state code
pay_period_start	DATE	Start date of the pay period
total_employees	INT	Employees processed by the run
error_count	INT	Number of employee payroll errors
status	VARCHAR(20)	Payroll run lifecycle status

Your question is SQL for Highest Error Rates. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem