SQL Declining Output With Window Functions | Dataford Interview Questions

Write a SQL query to identify facilities with declining output over the last 4 weeks using window functions.

Use the provided facility and weekly output data. Include only facilities with output recorded for all four weeks, where each week's output is strictly lower than the preceding week.

Output

One row per qualifying facility with facility_id, facility_name, week_1_output, week_2_output, week_3_output, week_4_output, and decline_weeks.
week_1_output is the latest week, and week_4_output is the oldest.
Order by facility_id ascending.

Schema

facilities

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

facility_output

Column	Type	Description
output_idPK	INT	Unique weekly output record identifier
facility_id	INT	Facility associated with the output record
week_start	DATE	Start date of the reporting week
output_units	INT	Units produced during the week

Your question is SQL Declining Output With Window Functions. 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