SQL cheat sheet for data analysts

First rows of a table

SELECT *
FROM products
LIMIT 10;

Only the columns you need

SELECT name, category, unit_price
FROM products;

A calculated column with a name

SELECT name,
       unit_price - unit_cost AS margin,
       ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct
FROM products;

Any arithmetic with NULL gives NULL: products with no unit_cost get a NULL margin.

Unique values (or combinations)

SELECT DISTINCT category, subcategory
FROM products
ORDER BY category, subcategory;

Equals one value

SELECT order_id, order_date, channel
FROM orders
WHERE status = 'refunded';

Several conditions, with brackets

SELECT name, category, unit_price
FROM products
WHERE category IN ('Decor', 'Kitchen')
  AND (unit_price < 50 OR NOT is_active);

AND binds tighter than OR. Bracket every OR you mix with AND.

A date range (half-open)

SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE '2024-03-01'
  AND order_date <  DATE '2024-04-01';

BETWEEN '2024-03-01' AND '2024-03-31' silently drops orders placed after midnight on the 31st.

Text pattern

SELECT customer_id, email
FROM customers
WHERE email ILIKE '%@gmail.com';

ILIKE ignores case (PostgreSQL, DuckDB). Elsewhere: LOWER(email) LIKE ...

Rows where a value is missing

SELECT customer_id, email
FROM customers
WHERE referral_source IS NULL;

= NULL is never true. Use IS NULL / IS NOT NULL.

Replace NULL with a label

SELECT COALESCE(referral_source, 'unknown') AS source,
       COUNT(*) AS customers
FROM customers
GROUP BY 1;

COUNT(*) counts rows, COUNT(col) skips NULLs

SELECT COUNT(*)               AS customers,
       COUNT(referral_source) AS with_source
FROM customers;

Divide without a divide-by-zero error

SELECT channel,
       SUM(amount) / NULLIF(COUNT(*), 0) AS avg_daily_spend
FROM marketing_spend
GROUP BY channel;

Put NULLs first when sorting

SELECT name, unit_cost
FROM products
ORDER BY unit_cost NULLS FIRST;

Most expensive first, with a tie-breaker

SELECT product_id, name, unit_price
FROM products
ORDER BY unit_price DESC, product_id;

Top 10

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Page 2 of 10 rows

SELECT product_id, name
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

Totals for the whole table

SELECT COUNT(*)          AS lines,
       SUM(quantity)     AS units,
       AVG(unit_price)   AS avg_price,
       MIN(unit_price), MAX(unit_price)
FROM order_items;

One row per group

SELECT channel, status, COUNT(*) AS orders
FROM orders
GROUP BY channel, status
ORDER BY channel, orders DESC;

Distinct count per group

SELECT channel, COUNT(DISTINCT customer_id) AS buyers
FROM orders
GROUP BY channel;

Filter groups with HAVING

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 20;

Run order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE filters rows, HAVING filters groups.

Bucket values into bands

SELECT CASE WHEN unit_price < 50  THEN 'under 50'
            WHEN unit_price < 200 THEN '50-199'
            ELSE '200+' END AS band,
       COUNT(*) AS products
FROM products
GROUP BY 1;

CASE stops at the first true WHEN, so order the tests from narrow to wide.

A rate per group (conditional aggregation)

SELECT channel,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       ROUND(AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END) * 100, 1) AS cancel_pct
FROM orders
GROUP BY channel;

FILTER works in PostgreSQL and DuckDB; SUM(CASE WHEN ... THEN 1 ELSE 0 END) works everywhere.

Inner join: revenue by category

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS revenue
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Rows with no match (anti-join)

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

Keep zero counts: filter in ON, not WHERE

SELECT c.customer_id, COUNT(o.order_id) AS app_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.channel = 'app'
WHERE c.segment = 'business'
GROUP BY c.customer_id;

Put o.channel = 'app' in WHERE and the customers with no app orders disappear: the LEFT JOIN becomes an inner join.

Count after a one-to-many join

SELECT o.channel,
       COUNT(*)                   AS lines,
       COUNT(DISTINCT o.order_id) AS orders
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.channel;

After joining order lines, each order appears once per line (fan-out). Never SUM an order-level column like shipping_fee here.

A table joined to itself

SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id;

Monthly totals

SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY 1
ORDER BY 1;

Year, month, weekday

SELECT EXTRACT(year FROM order_date)  AS yr,
       EXTRACT(month FROM order_date) AS mo,
       EXTRACT(dow FROM order_date)   AS weekday,  -- 0 = Sunday
       COUNT(*) AS orders
FROM orders
GROUP BY 1, 2, 3;

Days between two dates

SELECT r.return_id,
       r.returned_on - CAST(o.order_date AS DATE) AS days_to_return
FROM returns AS r
JOIN order_items AS oi ON oi.order_item_id = r.order_item_id
JOIN orders AS o ON o.order_id = oi.order_id;

DATE minus DATE gives whole days in PostgreSQL and DuckDB. SQL Server and BigQuery use DATEDIFF / DATE_DIFF.

The last 30 days of data

SELECT COUNT(*) AS orders
FROM orders
WHERE order_date >= TIMESTAMP '2025-12-31' - INTERVAL '30 days';

Trim spaces and fix case

SELECT UPPER(TRIM(state)) AS state, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

Split text on a character

SELECT SPLIT_PART(email, '@', 2) AS domain, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

Duplicate records by a cleaned key

SELECT LOWER(TRIM(email)) AS email_key,
       COUNT(*) AS records,
       MIN(customer_id) AS keep_id
FROM customers
GROUP BY 1
HAVING COUNT(*) > 1;

Join text together

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

CONCAT(a, b) also works and treats NULL as empty; || returns NULL if either side is NULL.

Compare to an overall number

SELECT name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

Rows that have a match (EXISTS)

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN returns AS r ON r.order_item_id = oi.order_item_id
  WHERE o.customer_id = c.customer_id
);

NOT IN (subquery) returns no rows at all if the subquery holds a NULL. NOT EXISTS is safe.

Name each step with WITH

WITH order_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price * (1 - discount_pct / 100)) AS merch
  FROM order_items
  GROUP BY order_id
)
SELECT o.customer_id,
       COUNT(*)              AS orders,
       ROUND(SUM(t.merch), 2) AS lifetime_value,
       ROUND(AVG(t.merch), 2) AS avg_order_value
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id;

Pre-aggregate before joining (no fan-out)

WITH merch AS (
  SELECT order_id, SUM(quantity * unit_price) AS merch
  FROM order_items
  GROUP BY order_id
)
SELECT o.channel,
       SUM(o.shipping_fee) AS shipping,
       ROUND(SUM(m.merch), 2) AS merchandise
FROM orders AS o
LEFT JOIN merch AS m ON m.order_id = o.order_id
GROUP BY o.channel;

Top 3 per group

WITH ranked AS (
  SELECT category, name, unit_price,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY unit_price DESC, product_id) AS rn
  FROM products
)
SELECT category, name, unit_price
FROM ranked
WHERE rn <= 3;

You cannot filter on a window function in WHERE: wrap it in a CTE or subquery first.

Ties: ROW_NUMBER, RANK, DENSE_RANK

SELECT name, unit_price,
       ROW_NUMBER() OVER (ORDER BY unit_price DESC) AS row_num,
       RANK()       OVER (ORDER BY unit_price DESC) AS rnk,       -- 1, 2, 2, 4
       DENSE_RANK() OVER (ORDER BY unit_price DESC) AS dense_rnk  -- 1, 2, 2, 3
FROM products;

Running total

WITH daily AS (
  SELECT CAST(order_date AS DATE) AS day, SUM(shipping_fee) AS fees
  FROM orders
  WHERE order_date >= DATE '2025-01-01'
  GROUP BY 1
)
SELECT day, fees,
       SUM(fees) OVER (ORDER BY day) AS fees_ytd
FROM daily;

Month over month change (LAG)

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT month, orders,
       LAG(orders) OVER (ORDER BY month) AS prev,
       ROUND((orders - LAG(orders) OVER (ORDER BY month)) * 100.0
             / LAG(orders) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly;

7-day moving average

WITH daily AS (
  SELECT CAST(order_date AS DATE) AS day, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT day, orders,
       AVG(orders) OVER (ORDER BY day
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily;

ROWS counts rows, not days: a day with no orders has no row, so fill the calendar first if gaps matter.

Average order value (AOV)

SELECT ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100))
             / COUNT(DISTINCT o.order_id), 2) AS aov
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

Conversion rate by traffic source

SELECT source,
       COUNT(*) AS sessions,
       ROUND(AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) * 100, 2) AS conversion_pct
FROM web_sessions
GROUP BY source
ORDER BY conversion_pct DESC;

Return rate by category

SELECT p.category,
       ROUND(COUNT(r.return_id) * 100.0 / COUNT(*), 1) AS return_pct
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
LEFT JOIN returns AS r ON r.order_item_id = oi.order_item_id
GROUP BY p.category;