SQL for data analysis: 12 queries analysts use, on one sales table

SQL for data analysis means using SELECT queries to filter, group, join and summarize tables. A dozen patterns do most of the work: WHERE, GROUP BY with SUM and COUNT, HAVING, joins, CASE, date truncation, and window functions for running totals, shares and period-over-period change. Below are all twelve, numbered, on one eight-row sales table, with the result each one returns.

The queries are written in PostgreSQL. Where SQL Server or MySQL differ, the difference is noted. One applies to several queries: SQL Server does not accept a column position such as GROUP BY 1, so there repeat the expression in the GROUP BY.

The table used throughout

Two tables. invoices holds one row per invoice, Q1 2026, amounts in USD:

customers holds one row per customer:

The control total is 82,500: the sum of amount over all eight rows. Every summary below must add back to it. Before querying a real export, prepare it for analysis: one row per invoice, one date format, no subtotal rows.

Queries 1-3: filter, aggregate, HAVING

Query 1. Filter rows with WHERE. March invoices only:

SELECT invoice_id, customer, invoice_date, amount
FROM invoices
WHERE invoice_date >= DATE '2026-03-01'
  AND invoice_date <  DATE '2026-04-01';

Returns invoices 6, 7 and 8, totaling 29,500. The range is "on or after the first, before the next first", which stays correct if the column is a timestamp.

Query 2. Aggregate with GROUP BY. Revenue and invoice count per customer:

SELECT customer, SUM(amount) AS revenue, COUNT(*) AS invoices
FROM invoices
GROUP BY customer
ORDER BY revenue DESC;

Query 3. Filter groups with HAVING. Customers above 25,000:

SELECT customer, SUM(amount) AS revenue
FROM invoices
GROUP BY customer
HAVING SUM(amount) > 25000;

Returns Acme (35,500) and Birch (28,000). WHERE filters rows before grouping; HAVING filters groups after it.

Queries 4-5: joins

Query 4. JOIN for an attribute. Revenue by segment:

SELECT c.segment, SUM(i.amount) AS revenue, COUNT(*) AS invoices
FROM invoices i
JOIN customers c ON c.customer = i.customer
GROUP BY c.segment
ORDER BY revenue DESC;

Eight invoice rows in, eight rows out of the join, and 54,500 + 28,000 = 82,500.

Query 5. LEFT JOIN with IS NULL to find gaps. Customers with no invoice this quarter:

SELECT c.customer
FROM customers c
LEFT JOIN invoices i
  ON i.customer = c.customer
 AND i.invoice_date >= DATE '2026-01-01'
 AND i.invoice_date <  DATE '2026-04-01'
WHERE i.invoice_id IS NULL;

Returns Dunmore. The date condition sits in the ON clause; moved to WHERE, it would remove the null rows and the query would return nothing.

Queries 6-7: CASE and dates

Query 6. CASE to band values. Invoices by size:

SELECT CASE WHEN amount >= 10000 THEN 'Large'
            WHEN amount >= 5000  THEN 'Medium'
            ELSE 'Small' END AS size_band,
       COUNT(*) AS invoices, SUM(amount) AS revenue
FROM invoices
GROUP BY 1
ORDER BY revenue DESC;

CASE takes the first branch that is true, so the order of the WHEN lines matters.

Query 7. Group by month. PostgreSQL's date_trunc cuts a date back to the start of its month:

SELECT DATE_TRUNC('month', invoice_date) AS month, SUM(amount) AS revenue
FROM invoices
GROUP BY 1
ORDER BY 1;

Returns January 20,500, February 32,500, March 29,500. In SQL Server, DATETRUNC(month, invoice_date) does the same from SQL Server 2022 (16.x); on older versions use DATEFROMPARTS(YEAR(invoice_date), MONTH(invoice_date), 1). In MySQL, DATE_FORMAT(invoice_date, '%Y-%m-01').

Queries 8-10: window functions

A window function, in the PostgreSQL tutorial's words, "performs a calculation across a set of table rows that are somehow related to the current row", but the rows "retain their separate identities" instead of collapsing into one. The OVER clause defines which rows.

Query 8. Running total. With ORDER BY inside OVER, the frame runs from the first row to the current one:

SELECT DATE_TRUNC('month', invoice_date) AS month,
       SUM(amount) AS revenue,
       SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', invoice_date)) AS running_total
FROM invoices
GROUP BY 1
ORDER BY 1;

The inner SUM is the monthly total; the outer SUM OVER adds the months up in order.

Query 9. Share of total. An empty OVER () makes the whole result one window:

SELECT customer,
       SUM(amount) AS revenue,
       SUM(amount) * 1.0 / SUM(SUM(amount)) OVER () AS share
FROM invoices
GROUP BY customer
ORDER BY revenue DESC;

Query 10. Month over month with LAG. LAG reads a value from the previous row "without the use of a self-join", and returns NULL when there is no previous row:

SELECT DATE_TRUNC('month', invoice_date) AS month,
       SUM(amount) AS revenue,
       LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', invoice_date)) AS prior_month
FROM invoices
GROUP BY 1
ORDER BY 1;

Window functions are allowed only in the SELECT list and ORDER BY, never in WHERE or HAVING. To filter on one, wrap the query, as Query 11 does.

Queries 11-12: deduplicate and structure

Query 11. ROW_NUMBER to keep one row per key. The latest invoice for each customer:

SELECT *
FROM (
  SELECT i.*,
         ROW_NUMBER() OVER (PARTITION BY customer ORDER BY invoice_date DESC) AS rn
  FROM invoices i
) t
WHERE rn = 1;

The same pattern removes duplicate rows from a messy export: partition by the columns that should be unique, keep rn = 1.

Query 12. A CTE to chain the steps. A WITH clause names the monthly totals once, and the main query reads them like a table:

WITH m AS (
  SELECT DATE_TRUNC('month', invoice_date) AS month, SUM(amount) AS revenue
  FROM invoices
  GROUP BY 1
)
SELECT month,
       revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month)) * 1.0
         / LAG(revenue) OVER (ORDER BY month) AS mom_change,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM m
ORDER BY month;

Each step is readable on its own, and a third step can be added as another CTE without nesting.

What the queries return on the eight rows

Queries 7, 8, 10 and 12 by month:

February: (32,500 − 20,500) / 20,500 = 58.5%. March: (29,500 − 32,500) / 32,500 = −9.2%. January has no prior month, so LAG returns NULL and the change is NULL, not zero.

Query 9, share of total by customer:

The shares are rounded to one decimal, so they show 99.9% rather than 100%. Unrounded they are 43.03%, 33.94% and 23.03%, which sum to 100%.

The check: every summary sums back to the table

Two checks catch most SQL mistakes before anyone reads the result.

Totals. Every grouped result must add back to the control total: SELECT SUM(amount) FROM invoices returns 82,500. By month, 20,500 + 32,500 + 29,500 = 82,500. By customer, 35,500 + 28,000 + 19,000 = 82,500. By segment, 54,500 + 28,000 = 82,500. By size band, 52,500 + 26,000 + 4,000 = 82,500. The running total ends on 82,500.

Row counts. Count rows before and after every join. invoices has 8 rows, and the join in Query 4 returns 8. If it returned more, the join key is not unique on the customers side and amounts are being counted twice. The same discipline applies when the two tables come from different systems; see reconciling the CRM to the ledger.

Where it goes wrong

• Join fan-out. Joining invoices to a table with several rows per customer, such as one row per contact, multiplies every amount. Compare row counts and totals before and after.
• Integer division. In PostgreSQL and SQL Server an integer divided by an integer truncates: 35500 / 82500 returns 0. Multiply by 1.0 first, as Queries 9 and 12 do.
• NULLs. COUNT(*) counts every row; COUNT(column) counts only non-null values. SUM ignores nulls, and PostgreSQL's aggregate docs note that "sum of no rows returns null, not zero", so wrap it in COALESCE(SUM(amount), 0) where a zero is meant. Counting rows where you meant to sum values is its own trap; see count-weighted and value-weighted.
• WHERE vs HAVING. Filtering an aggregate in WHERE is an error. Filtering plain rows in HAVING works but is slower and harder to read.
• Date boundaries on timestamps. BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp column stops at midnight at the start of January 31 and misses most of that day. Use >= the start and < the next start.

If the results end up in a spreadsheet anyway, the same summaries are a pivot table in Excel away, and Excel or an analytics tool covers when the spreadsheet stops being enough.

Analysis without writing SQL

Most finance teams have the exports but not the time to write and check twelve queries every month. Covirage's tools run the equivalent of these queries on the files a finance team uploads, check that every summary adds back to the control total, and cite the rows behind each figure; the external AI model turns a plain question into the right tool call and explains the result, without doing the arithmetic. See Covirage for finance teams. To turn rows into columns in the query itself, see SQL PIVOT, and to have the query written from a plain question, see text-to-SQL.