5 SQL queries every data analyst ends up googling

sofrito — Mon, 20 Jul 2026 10:25:41 +0000

You know the pattern. A stakeholder asks a simple-sounding question, you open your editor confident, and thirty seconds later you are back on Google typing "sql running total" for the hundredth time.

Here are five of the queries analysts re-google the most, written so you can actually run them. All examples use a small e-commerce schema (customers, orders, order_items, products) and PostgreSQL syntax, with notes where other dialects differ.

1. Running total (cumulative sum)

The classic "revenue to date" line on every dashboard.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM (
    SELECT order_date::date AS order_date,
           SUM(total_amount)  AS daily_revenue
    FROM orders
    GROUP BY order_date::date
) d
ORDER BY order_date;

The trick is the window function SUM(...) OVER (ORDER BY ...). No self-join, no correlated subquery. It sums every row up to and including the current one.

2. Keep only the latest row per group (deduplicate)

You have multiple rows per customer and want just their most recent order.

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC, id DESC
           ) AS rn
    FROM orders o
) t
WHERE rn = 1;

ROW_NUMBER() numbers each customer's rows newest-first, then you keep number 1. This is the pattern behind "latest status", "most recent login", "current price", and a hundred others.

3. Month-over-month growth

Revenue this month versus last, as a percentage, in one query.

WITH monthly AS (
    SELECT date_trunc('month', order_date) AS month,
           SUM(total_amount)               AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

LAG() pulls the previous row's value onto the current row so you can compare periods. NULLIF(..., 0) quietly saves you from a divide-by-zero on the first month.

4. Top N per group

Top 3 products in every category, not just top 3 overall.

SELECT category, product_name, revenue
FROM (
    SELECT
        p.category,
        p.name AS product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) AS rn
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category, p.name
) t
WHERE rn <= 3
ORDER BY category, revenue DESC;

Same ROW_NUMBER() idea as #2, but PARTITION BY category restarts the count for each group. Change <= 3 to whatever N you need.

5. Find the rows that are missing (anti-join)

Customers who signed up but never ordered. The thing a plain JOIN will never show you.

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

LEFT JOIN keeps every customer; the WHERE ... IS NULL filter then keeps only the ones with no matching order. This is how you find gaps: unpaid invoices, unshipped orders, users with no activity.

The pattern behind the patterns

Four of these five lean on window functions (SUM OVER, ROW_NUMBER, LAG). If there is one thing worth drilling until it is muscle memory, it is those. Most "advanced" analytics SQL is a variation on the three moves above.

These five are pulled from The Real-World SQL Cookbook 75 tested, copy-paste queries like this, all runnable against one small database you load once and follow along with. If this saved you a search or two, the other 70 probably will too: sofrito.gumroad.com/l/sqlcookbook