SQL for analysts

SQL (Structured Query Language) is how analysts ask questions of data stored in databases. Most company data (orders, users, payments) lives in tables, and a spreadsheet can't hold millions of rows. SQL can. It is also the skill most often tested in data analyst interviews.

In this tutorial you will build a tiny Brew & Bean database and answer real business questions with it: which items earn the most, how much did we sell each day, who are our repeat customers. You need nothing installed: the free website sqliteonline.com (or any SQLite viewer such as DB Browser for SQLite) runs everything below. Basic spreadsheet comfort helps; see *Spreadsheets for analysis* first.

A database table is like a sheet: columns are fields, rows are records. We use three tables. menu_items lists what we sell. orders has one row per order. order_items has one row per item in an order (an order with a cappuccino and a croissant has two rows). Splitting data like this avoids repeating the same text again and again, and it is why you need joins.

CREATE TABLE menu_items (id TEXT PRIMARY KEY, name TEXT, category TEXT, price INTEGER);
INSERT INTO menu_items VALUES
 ('espresso','Espresso','coffee',120),
 ('cappuccino','Cappuccino','coffee',180),
 ('cold-brew','Cold Brew','coffee',220),
 ('filter-coffee','Filter Coffee','coffee',80),
 ('masala-chai','Masala Chai','tea',90),
 ('croissant','Butter Croissant','bakery',150),
 ('banana-bread','Banana Bread','bakery',130),
 ('veg-sandwich','Veg Grilled Sandwich','food',160);

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_name TEXT, status TEXT, created_at TEXT);
INSERT INTO orders VALUES
 (1,'Asha','picked_up','2026-10-01 08:15:00'),
 (2,'Ravi','picked_up','2026-10-01 09:40:00'),
 (3,'Asha','picked_up','2026-10-02 08:20:00'),
 (4,'Meena','cancelled','2026-10-02 18:05:00'),
 (5,'Kiran','picked_up','2026-10-03 19:30:00'),
 (6,'Ravi','picked_up','2026-10-03 20:10:00'),
 (7,'Asha','picked_up','2026-10-04 08:05:00'),
 (8,'Divya','picked_up','2026-10-04 21:00:00');

CREATE TABLE order_items (order_id INTEGER, item_id TEXT, qty INTEGER);
INSERT INTO order_items VALUES
 (1,'cappuccino',1),(1,'croissant',1),(2,'espresso',2),(3,'cappuccino',1),
 (4,'cold-brew',2),(5,'veg-sandwich',1),(5,'cold-brew',1),(6,'masala-chai',2),
 (6,'banana-bread',1),(7,'filter-coffee',1),(8,'cappuccino',2),(8,'croissant',2);

Creates three tables and fills them with 8 orders from 1 to 4 October 2026. Run it once in SQLite. All later queries use these tables.

Every query reads in the order you would say it aloud: select these columns, from this table, where this is true, sorted by this. SELECT picks columns, FROM names the table, WHERE keeps only rows that match, ORDER BY sorts (add DESC for largest first).

Text values go in single quotes. Use = to compare, <> for not equal, and AND / OR to combine conditions.

SELECT name, price
FROM menu_items
WHERE category = 'coffee'
ORDER BY price DESC;

Prints 4 rows: Cold Brew 220, Cappuccino 180, Espresso 120, Filter Coffee 80.

Other useful filters: price BETWEEN 100 AND 160, category IN ('tea','bakery'), name LIKE '%Coffee%' (the % means any characters) and column IS NULL for missing values. Never write = NULL; it never matches. LIMIT 5 returns only the first five rows.

Analysts rarely want raw rows. They want totals and averages per something: per category, per day, per customer. Aggregate functions summarise many rows into one: COUNT, SUM, AVG, MIN, MAX. GROUP BY says what to summarise per.

SELECT category,
       COUNT(*) AS items,
       ROUND(AVG(price), 1) AS avg_price
FROM menu_items
GROUP BY category
ORDER BY items DESC;

coffee 4 items avg 150.0; bakery 2 avg 140.0; tea 1 avg 90.0; food 1 avg 160.0. AS gives a column a readable name.

The rule: every column in SELECT must either be inside an aggregate function or appear in GROUP BY. Mixing them up is the most common beginner error.

WHERE filters rows before grouping. HAVING filters groups after grouping, so it can use aggregates: HAVING COUNT(*) >= 2.

order_items stores an item id like cappuccino, not its price. To find money you must join it to menu_items. A join matches rows from two tables using a shared column. JOIN (also called INNER JOIN) keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table, even with no match, filling the gaps with NULL.

SELECT o.id AS order_id,
       o.customer_name,
       m.name AS item,
       oi.qty,
       oi.qty * m.price AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN menu_items m ON m.id = oi.item_id
ORDER BY o.id;

12 rows, for example order 1 Asha Cappuccino 1 180 and Butter Croissant 1 150; order 4 Meena Cold Brew 2 440. o, oi and m are short aliases for the table names.

Now combine everything. Cancelled orders were never paid for, so a revenue question must exclude them.

SELECT m.name,
       SUM(oi.qty) AS units,
       SUM(oi.qty * m.price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN menu_items m ON m.id = oi.item_id
WHERE o.status <> 'cancelled'
GROUP BY m.name
ORDER BY revenue DESC;

Cappuccino 4 units 720; Butter Croissant 3 units 450; Espresso 2 units 240; Cold Brew 1 unit 220; Masala Chai 2 units 180; Veg Grilled Sandwich 1 unit 160; Banana Bread 1 unit 130; Filter Coffee 1 unit 80.

SELECT date(o.created_at) AS day,
       COUNT(DISTINCT o.id) AS orders,
       SUM(oi.qty * m.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN menu_items m ON m.id = oi.item_id
WHERE o.status = 'picked_up'
GROUP BY date(o.created_at)
ORDER BY day;

2026-10-01: 2 orders, 570; 2026-10-02: 1 order, 180; 2026-10-03: 2 orders, 690; 2026-10-04: 2 orders, 740. COUNT(DISTINCT o.id) is needed because an order with two items appears in two joined rows. date() is SQLite; PostgreSQL and MySQL use DATE(created_at).

A CTE (common table expression, written with WITH) gives a name to an intermediate result so a long query reads top to bottom. Here the first step totals each order; the second step summarises per customer.

WITH order_totals AS (
  SELECT o.id, o.customer_name,
         SUM(oi.qty * m.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN menu_items m ON m.id = oi.item_id
  WHERE o.status = 'picked_up'
  GROUP BY o.id, o.customer_name
)
SELECT customer_name,
       COUNT(*) AS orders,
       SUM(total) AS spent
FROM order_totals
GROUP BY customer_name
HAVING COUNT(*) >= 2
ORDER BY spent DESC;

Asha: 3 orders, 590; Ravi: 2 orders, 550. Kiran and Divya ordered once, so HAVING removes them.

GROUP BY collapses rows. A window function calculates across related rows but keeps every row. RANK() OVER (ORDER BY total DESC) numbers rows from the largest. Add PARTITION BY to restart the ranking inside each group, for example the top order per customer. Window functions appear in many interviews. SQLite supports them from version 3.25, which current tools include.

WITH order_totals AS (
  SELECT o.id, o.customer_name,
         SUM(oi.qty * m.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN menu_items m ON m.id = oi.item_id
  WHERE o.status = 'picked_up'
  GROUP BY o.id, o.customer_name
)
SELECT id, customer_name, total,
       RANK() OVER (ORDER BY total DESC) AS value_rank
FROM order_totals
ORDER BY value_rank;

Order 8 Divya 660 rank 1; order 5 Kiran 380 rank 2; order 1 Asha 330 rank 3; order 6 Ravi 310 rank 4; order 2 Ravi 240 rank 5; order 3 Asha 180 rank 6; order 7 Asha 80 rank 7.

Act as a patient SQL tutor. Here is my [database dialect, e.g. PostgreSQL] query and the table structure.

Tables:
[paste CREATE TABLE statements]

Query:
[paste your query]

Explain it line by line in plain English. Then tell me (1) any join that could duplicate rows, (2) whether the WHERE and HAVING are correct for the question "[your business question]", and (3) one way to make it easier to read. Do not rewrite it unless there is a bug.

What to expect: A good answer walks through FROM, JOIN, WHERE, GROUP BY in the order they run and points out specific risks such as duplicated rows. Always run the query yourself and check one number by hand.

How this comes up in interviews. Expect questions such as: the difference between WHERE and HAVING; INNER vs LEFT JOIN; find the second highest salary; top 3 products per category (a window function with PARTITION BY); customers who ordered in two consecutive months; remove duplicates. Practise writing them by hand and explaining your thinking aloud. The practice sites in this track's resource list are good for this.