Using SQL Window Functions | Dataford Interview Questions

Explain and demonstrate the use of window functions like LEAD, LAG, and RANK in SQL.

Use the supplied employee and monthly usage data. Return each usage record with the previous and next usage value for that employee, plus the employee's usage rank for that month.

Output

One row per matched monthly usage record.
Columns: employee_id, employee_name, usage_month, usage_gb, previous_usage_gb, next_usage_gb, and monthly_usage_rank.
Sort by employee_id, then usage_month.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee display name
region	VARCHAR(50)	Employee region

monthly_usage

Column	Type	Description
usage_idPK	INT	Unique usage record identifier
employee_id	INT	Referenced employee identifier
usage_month	DATE	Month represented by the record
usage_gb	INT	Usage amount in gigabytes

Your question is Using SQL 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