SQL 2023 cheatsheet

SQL 2023: Aggregation and window functions

7 common SQL 2023 tasks.

Count rows

Count all rows, or only non-NULL values of a column with COUNT(column).

SELECT COUNT(*) FROM orders;

Compute a value per group.

SELECT status, COUNT(*) AS n, SUM(total) AS revenue FROM orders GROUP BY status;

Keep only groups that meet a condition. WHERE filters rows before grouping, HAVING filters groups after.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5;

Number each user's orders from newest to oldest. Filter on this to get the latest per user.

SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders;

Add up amounts in date order.

SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM sales;

Rank products by price, giving ties the same rank.

SELECT name, price, RANK() OVER (ORDER BY price DESC) AS price_rank FROM products;

Read the value of the previous row in the same ordering.

SELECT day, amount, amount - LAG(amount) OVER (ORDER BY day) AS change FROM sales;