Advanced MySQL Query Patterns
Window Functions (MySQL 8.0+)
-- Row number per partition
SELECT
username,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
-- Running total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Common Table Expressions (CTE)
WITH active_customers AS (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'complete'
GROUP BY user_id
HAVING total > 1000
)
SELECT u.username, ac.total
FROM users u
JOIN active_customers ac ON ac.user_id = u.id;