Rank Monthly Usage and Track Changes

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

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

Output details

• Sort employee_id and usage_month in ascending order.
• Compute previous and next values per employee in ascending usage_month order.
• Rank records separately within each usage_month by usage_gb descending, placing NULL values last.
• Use competition ranking, assigning tied values the same rank and leaving gaps afterward.
• Preserve NULL usage_gb values as NULL rather than treating them as zero.