SQL Lab — Window Functions

🖥️

SQL Terminal

📚

Reference

📊

Results

📌 OBJECTIVES

○ROW_NUMBER per department

○RANK employees by salary

○Running total with SUM OVER

○Month-over-month change (LAG)

○Next month preview (LEAD)

○Final report submitted

0 / 6 complete

SQL TERMINAL — finance_db

MySQL 8.0 — finance_db Tables: employees, monthly_revenue Task: Build the CFO's quarterly analysis using window functions ──────────────────────────────────────

mysql>

WINDOW FUNCTION REFERENCE

ROW_NUMBER / RANK

SELECT name, dept, salary, ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary DESC ) AS row_num, RANK() OVER ( ORDER BY salary DESC ) AS overall_rank FROM employees;

RUNNING TOTAL

SELECT month, revenue, SUM(revenue) OVER ( ORDER BY month ROWS UNBOUNDED PRECEDING ) AS running_total FROM monthly_revenue;

LAG / LEAD

SELECT month, revenue, LAG(revenue) OVER ( ORDER BY month ) AS prev_month, LEAD(revenue) OVER ( ORDER BY month ) AS next_month, revenue - LAG(revenue) OVER ( ORDER BY month ) AS mom_change FROM monthly_revenue;

DENSE_RANK / NTILE

SELECT name, salary, DENSE_RANK() OVER ( ORDER BY salary DESC ) AS d_rank, NTILE(4) OVER ( ORDER BY salary ) AS quartile FROM employees;

CFO REPORT BUILDER

QUARTERLY ANALYSIS — sections populate as you complete queries

MISSION BRIEFING

WINDOW FUNCTIONS — MERIDIAN CAPITAL

SCENARIO

The CFO of Meridian Capital needs a quarterly analysis that standard GROUP BY queries cannot produce. She wants employee rankings within departments, running revenue totals, and month-over-month percentage changes — all in a single result set per query. This requires window functions.

TASKS

Use ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) to rank employees within their department
Use RANK() to get overall salary rank across the company
Use SUM() OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) for running revenue total
Use LAG(revenue) to calculate month-over-month change
Use LEAD(revenue) to preview next month's forecast

KEY SYNTAX

FUNCTION() OVER (PARTITION BY col ORDER BY col2 ROWS/RANGE frame)

Window functions never collapse rows — every input row produces an output row. Use the Reference window for working templates.

Display Mode