SQL snippet library
Copy-ready SQL queries, each with an explanation, the tables it assumes and the mistakes to avoid.
Share of total
Show each group as a percentage of the grand total using a window function.
Total and average per group
Add up and average a number for every value of a column, with GROUP BY.
Find duplicate rows
List the values that appear more than once, with HAVING COUNT(*) > 1.
Orders per month (SQLite)
Count and add up orders by month with strftime.
Customers with no orders (LEFT JOIN)
Find the rows of one table that have no match in another table.
Top 2 orders for each customer
Rank rows inside each group with ROW_NUMBER and keep the first few.
Running total by month
A cumulative total that grows month by month, with a window SUM.
Latest order for each customer
Keep only the most recent row per customer using a CTE and ROW_NUMBER.
Month-over-month change with LAG
Compare each month with the month before it using LAG.