10 Actually Useful SQL Queries (2026)

You know SELECT, JOIN, and GROUP BY. So does everyone. This list covers the SQL patterns that separate production developers from beginners — the queries you write when you need answers that simple aggregations can’t provide. All examples use PostgreSQL syntax.

The Queries

Window Functions: ROW_NUMBER for Deduplication — Assign a unique rank to each row within a partition. Use it to delete duplicates while keeping one copy.

DELETE FROM orders WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (
      PARTITION BY customer_id, order_date ORDER BY created_at DESC
    ) AS rn FROM orders
  ) sub WHERE rn > 1
);

Output: Deletes all but the newest duplicate order per customer per day.

LAG and LEAD for Row Comparison — Access the previous or next row’s value without a self-join.

SELECT date, amount,
  LAG(amount, 1) OVER (ORDER BY date) AS prev_day,
  amount - LAG(amount, 1) OVER (ORDER BY date) AS change
FROM daily_sales ORDER BY date;

Output: Shows each day’s sales, the previous day’s sales, and the difference.

Recursive CTE for Tree Structures — Traverse hierarchical data (org charts, categories, comment threads) without application-level recursion.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Output: Flattened org chart with depth column showing hierarchy level.

Running Total with SUM Window — Calculate cumulative sums without subqueries.

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions ORDER BY date;

Output: Each row shows the transaction and the running total up to that point.

Rolling Average (Moving Window) — Smooth noisy time-series data.

SELECT date, amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_sales ORDER BY date;

Output: A 7-day rolling average that smooths daily fluctuations.

Date Bucketing with date_trunc — Group timestamps into consistent intervals.

SELECT date_trunc('week', created_at) AS week, COUNT(*) AS signups
FROM users GROUP BY week ORDER BY week;

Output: Weekly signup counts, with each row representing Monday of that week.

Pagination with the Seek Method — Cursor-based pagination that scales, unlike OFFSET.

SELECT id, name, created_at FROM users
WHERE created_at > '2026-06-01T00:00:00Z'
ORDER BY created_at ASC, id ASC
LIMIT 20;

Output: Next 20 users after the cursor. Works efficiently on millions of rows because it uses an index scan instead of counting skipped rows.

Find Duplicates Across Multiple Columns — Identify rows that share values in specific columns.

SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

Output: Lists every duplicate email and how many times it appears.

Upsert with ON CONFLICT — Insert or update in one atomic statement.

INSERT INTO inventory (sku, quantity, updated_at)
VALUES ('ABC-123', 50, NOW())
ON CONFLICT (sku) DO UPDATE SET
  quantity = inventory.quantity + EXCLUDED.quantity,
  updated_at = NOW();

Output: Inserts a new row or adds 50 to the existing quantity for that SKU.

Fuzzy Match with ILIKE and Similarity — Find approximate text matches.

SELECT name FROM products
WHERE name ILIKE '%wireless%mouse%' OR
  SIMILARITY(name, 'wireless mouse') > 0.3
ORDER BY SIMILARITY(name, 'wireless mouse') DESC
LIMIT 10;

Output: Products matching “wireless mouse” even with typos, abbreviations, or different word order. Requires pg_trgm extension.