SQL Week-Over-Week Late Increase | Dataford Interview Questions

Write a SQL query to identify facilities with the largest week-over-week increase in late packages.

Use the provided facility and package event data. Compare consecutive calendar weeks, include weeks with zero late packages, and return every facility tied for the largest increase.

Output

One row per facility and week producing the maximum increase.
Columns: facility_id, facility_name, week_start, late_packages, prior_week_late_packages, and increase.
Order by increase descending, then facility_id ascending.

Schema

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(100)	Facility name
region	VARCHAR(50)	Operational region

package_events

Column	Type	Description
package_idPK	BIGINT	Unique package event identifier
facility_id	INT	Facility processing the package
delivered_date	DATE	Package delivery date
is_late	BOOLEAN	Whether the package was late

Your question is SQL Week-Over-Week Late Increase. 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