SQL for Analytics I — Select, Filter, Group

P12.data-quality-sql.01 · Audience: guest, it-ml, language-pro · Prerequisites: From Notebook to Pipeline

Orchestration Without the Logo Soup SQL for Analytics II — Joins & Windows

Before a number can be trusted it has to be computed, and for most of the world's analytics that computation is written in SQL — a language old enough to vote several times over and still the first tool an analyst reaches for. Its whole design is one idea: describe the rows you want, and let the engine find them. This module builds that idea from three verbs — SELECT to choose columns, WHERE to keep rows, GROUP BY to fold many rows into one summary — on a tiny retail dataset small enough to check every answer by hand. Everything runs in the scratch console below, which ships a real SQLite engine; nothing is fetched, nothing is hidden.

Step 1 / 5 — Three tables, one question

Everything in this track runs on one small retail dataset: three tables that between them record who our customers are, what we sell, and what got ordered. It is deliberately tiny — every total below can be checked on your fingers — but it has the exact shape of a real warehouse: facts (the orders) pointing at dimensions (the customers and products they refer to).

`customers`	customer_id	city
	c1	Paris
	c2	Lyon

`products`	product_id	price
	p1	10.0
	p2	20.0

order_id	customer_id	product_id	quantity
o1	c1	p1	2
o2	c1	p2	1
o3	c2	p1	3

The question we will chase all module is a business one: how much revenue does each city bring in? Nothing in any single table answers it — the money lives in orders (as quantities), the prices in products, the cities in customers. SQL's job is to describe the answer we want and let the engine assemble it.

ⓘ Concept: SQL is declarative: say what, not how

query = a description of the desired rows → engine plans and executes the retrieval

Why it matters — A SQL query does not spell out loops or index lookups — it names the rows and columns you want, and the database's query planner decides how to fetch them. That is the whole reason one language has survived fifty years of hardware: you describe the result, and the engine, not you, worries about the mechanics. Learn to state the 'what' precisely and SQL rewards you; try to micromanage the 'how' and you are fighting the tool.

✅ Key takeaways

SQL is declarative: you describe the rows and columns you want and the engine plans how to fetch them. The bundled retail dataset — customers (Paris, Lyon), products (10.0, 20.0), three orders — is small enough to check every answer by hand.
SELECT projects columns; WHERE filters rows. They are independent cuts: SELECT narrows the table sideways, WHERE narrows it downward. NULL means 'no value' and is tested with IS NULL, never with =.
GROUP BY folds many rows sharing a key into one summary row, with an aggregate (SUM/COUNT/AVG/…) per group — changing the grain of the table. SUM(quantity) per product gives p1 = 5, p2 = 1 from the three orders.
SQL executes FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, not in written order. That is why WHERE cannot filter on an aggregate (use HAVING) and why a good WHERE filter is cheap — it runs before the grouping.
Revenue per city (SUM(quantity × price) GROUP BY city) gives Paris = 40, Lyon = 30 — matching the hand arithmetic. The only black box left is the JOIN that stitches the three tables, which the next module opens on.

Next: P12.data-quality-sql.02 — SQL for Analytics II — Joins & Windows

Ask the mentor about this module

Ask a question about this content. The mentor explains and grounds its answer in what you are studying; asking is recorded as a learning signal, not a grade.

Ctrl/Cmd + Enter to send

Try it yourself

A scratch console for this page's ideas — ungraded, nothing you run here is recorded.

Scratch console

A scratch console with the scientific stack (pandas, numpy, scikit-learn). Runs on the server — no network, resource-limited and measured.

Output appears here.

Where next?

Continue

SQL for Analytics II — Joins & Windows →

Data Quality & SQL · P12

Later in Data Quality & SQL

This module unlocks

Go up a level