10 SQL Queries I Reach for Constantly

The small set of query shapes that keep showing up once you're writing real, messy, everyday SQL.

None of these are exotic. They're the handful of query shapes that keep showing up once real data gets messy — duplicate rows, customers who never ordered, "what happened most recently", and so on. I'd rather have these memorised than rederive them every time.

1. Filter, sort, and take a sane-sized look

SELECT id, name, country
FROM customers
WHERE country = 'UK'
ORDER BY name
LIMIT 20;

The most-typed query in existence. WHERE narrows the rows, ORDER BY decides what order they come back in, and LIMIT stops you from accidentally pulling ten million rows into a terminal.

2. Find duplicates

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Real-world Use

I used exactly this to track down a broken signup flow that was silently creating two customer rows per person. GROUP BY collapses the rows, HAVING filters on the aggregate — you can't say COUNT(*) > 1 in a WHERE clause, because WHERE runs before the grouping happens.

3. Rows that don't have a match

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Customers who never placed an order. LEFT JOIN keeps every customer row even when there's no matching order, filling the order columns with NULL — so filtering for o.id IS NULL isolates exactly the rows where the match never happened. This is the general "find what's missing" pattern.

4. Join two tables the ordinary way

SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';

A plain INNER JOIN, which is what JOIN means on its own. Only rows with a match on both sides survive — useful the moment you want customer names next to their orders instead of a customer_id you have to look up separately.

5. Turn rows into a summary

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders
FROM orders
GROUP BY customer_id;

Conditional aggregation: wrap a CASE inside SUM to count only the rows that match a condition, all in the same GROUP BY pass. It reads oddly the first few times, then becomes the default way I write "count of X where Y".

6. The most recent row per group

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

Key Takeaway

PARTITION BY resets the numbering for every customer, ORDER BY decides what "first" means within that group, and filtering on rn = 1 keeps just the top row per group — while every other column comes along for free. It's GROUP BY without having to throw the row away.

7. A running total

SELECT
  created_at,
  amount,
  SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders
WHERE customer_id = 42
ORDER BY created_at;

Same OVER() machinery as above, minus the PARTITION BY, so it accumulates across the whole result set in order. Swap SUM for AVG and you get a moving average with no extra effort.

8. Insert, or update if it's already there

INSERT INTO customers (id, name, email)
VALUES (101, 'Asha Patel', 'asha@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

What Confused Me

I used to SELECT first to check whether a row existed, then decide between INSERT and UPDATE in application code. ON CONFLICT does that in one statement and one round trip — the database checks for me, and EXCLUDED just means "the row I tried to insert".

9. A self-join for hierarchies

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

The same table, joined to itself under two different aliases. Useful for anything tree-shaped that lives in one table — org charts, categories with subcategories, comment replies.

10. When your “table” is an API

Not everything worth querying lives in a database you control. A lot of real data shows up as JSON from an API — and the same instincts still apply: find the rows, pick the fields you want, look for what's missing.

const res = await fetch("https://jsonplaceholder.typicode.com/users");
const users = await res.json();

// The SQL instinct: SELECT name, city FROM users WHERE city LIKE 'S%';
const filtered = users.filter((u) => u.address.city.startsWith("S"));
filtered.forEach((u) => console.log(u.name, "-", u.address.city));

Keeping this list honest

I add to this page whenever I catch myself writing the same shape of query for the third time. If you're newer to SQL, the roadmap is the better starting point — this page is closer to a personal cheat sheet than a lesson.