📈 Analytics
ROW_NUMBER, LAG/LEAD, running totals and PIVOT: reporting without leaving SQL.
| Snippet | |
|---|---|
| ☆ROW_NUMBER by Dept — Number rows within each partition |
SELECT empno, name, salary, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY salary DESC) AS rn FROM employees;
|
| ☆LAG / LEAD — Access previous/next row values without a self join |
SELECT name, salary, LAG(salary) OVER (ORDER BY salary) AS prev_sal, LEAD(salary) OVER (ORDER BY salary) AS next_sal FROM employees;
|
| ☆FIRST_VALUE — Highest-paid employee name repeated per dept |
SELECT deptno, name, FIRST_VALUE(name) OVER (PARTITION BY deptno ORDER BY salary DESC) AS top_earner FROM employees;
|
| ☆Running Total — Cumulative sum down the rows |
SELECT name, salary, SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) AS running_total FROM employees;
|
| ☆PIVOT — Rows to columns |
SELECT * FROM (SELECT deptno, job, salary FROM employees) PIVOT (SUM(salary) AS total FOR job IN ('CLERK', 'MANAGER', 'ANALYST'));
|
| ☆UNPIVOT — Columns to rows |
SELECT deptno, metric, val FROM dept_stats UNPIVOT (val FOR metric IN (min_sal, max_sal, avg_sal));
|
| ☆NTILE Quartiles — Bucket rows into quartiles |
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;
|