TODO (1069B)
Ration about how NULLs affect queries
BUILD QUERY FRAGMENTS FOR FAST RECALL:
top-N per group (ROW_NUMBER() + PARTITION BY)
SELECT *
FROM (
SELECT
dept,
employee,
salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn <= 3;
"Give me the top three salaries per department."
running totals (SUM() OVER)
SELECT
order_date,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
"Give me an order-flow report"
conditional aggregation (SUM(CASE WHEN ...))
SELECT
dept,
SUM(CASE WHEN symbol= 'W' THEN 1 ELSE 0 END) AS windows,
SUM(CASE WHEN symbol = 'L' THEN 1 ELSE 0 END) AS linux
FROM machines
GROUP BY dept;
"Give me a count of employee windows and linux machines"
top-N per group alternative
SELECT e.*
FROM employees e
JOIN (
SELECT dept, MAX(salary) AS max_salary
FROM employees
GROUP BY dept
) m ON e.dept = m.dept AND e.salary = m.max_salary;
"Give me highest salary per employee department"
pivot/unpivot tricks - covered by own blog post, I think?