Go beyond the basics with subqueries, CTEs, window functions and the analytics patterns analysts use every day, practised in your browser.

SQL for Analysis

## Why it matters

SQL is read far more often than it is written. Your query will be reviewed by a teammate, reused in a dashboard, copied into a scheduled job and debugged by someone at 9 a.m. when the numbers look off. A query that is correct but unreadable is a liability: nobody can check it, so nobody can trust it. Consistent style costs nothing to run and makes mistakes much easier to spot.

## A before and after

This query works:

```sql
select a.region r,count(distinct a.id) x,sum(b.amount) y from customers a,orders b where a.id=b.customer_id and b.status='completed' group by a.region;
```

But what are `r`, `x` and `y`? Which tables are `a` and `b`? Is the join condition mixed in with the business filter on purpose? Here is the same logic, restyled:

```sql
-- Revenue and active customers per region.
-- Business rule: only completed orders count.
WITH completed_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
)
SELECT c.region,
       COUNT(DISTINCT c.id) AS active_customers,
       SUM(co.amount)       AS revenue
FROM customers AS c
JOIN completed_orders AS co
  ON co.customer_id = c.id
GROUP BY c.region;
```

Same rows, but each line now says what it does.

## The conventions that matter most

There is no single official style, but most team guides (for example the widely shared GitLab and Mozilla data guides, or Simon Holywell's sqlstyle.guide) agree on these points:

**1. One clause per line.** `SELECT`, `FROM`, `JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY` each start a new line. Long column lists get one column per line.

**2. Consistent keyword case.** Upper-case keywords (`SELECT`, `FROM`) and lower-case names (`orders`, `customer_id`) are the most common choice. Lower-case keywords are fine too; what matters is that a file does not mix both.

**3. Meaningful aliases with `AS`.** `customers AS c` and `completed_orders AS co` are short but recognisable. `a`, `b`, `t1` force the reader to scroll back. Always alias output columns with names a stakeholder would understand: `revenue`, not `y`; `active_customers`, not `x`.

**4. Explicit joins.** Write `JOIN ... ON` instead of listing tables with commas and putting the join condition in `WHERE`. The old comma style mixes join logic with filters, and forgetting one condition silently produces a cross join with millions of rows.

**5. Qualify columns when more than one table is involved.** `c.region`, not `region`. When a second table later gains a `region` column, an unqualified name becomes ambiguous or, worse, refers to the wrong one.

**6. CTEs for steps.** Each CTE does one job and has a name that describes its rows: `completed_orders`, `customer_totals`, `ranked_products`.

**7. No `SELECT *` in finished queries.** List the columns you need. `*` breaks when someone adds a column and hides what the query actually depends on. (It is fine while exploring.)

**8. Trailing or leading commas, consistently.** Some teams put commas at the start of lines (`, amount`) so a column can be commented out easily. Pick one.

## Comments: explain why, not what

```sql
-- Bad: restates the code
WHERE status = 'completed'  -- status is completed

-- Good: explains the business rule
WHERE status = 'completed'  -- refunds are reported separately by finance
```

Put a short header at the top of any saved query: what question it answers, the key definitions (what counts as "active"), and anything surprising. Use `--` for single lines and `/* ... */` for longer blocks.

## Readable is also safer

Style is not only cosmetic. Formatting each condition on its own line makes `AND`/`OR` precedence visible:

```sql
WHERE status = 'completed'
  AND (region = 'North' OR region = 'South')
```

Without the brackets, `a AND b OR c` means `(a AND b) OR c`, which would include every South order whatever its status. Clear layout makes that bug jump out in a review.

## Formatters and linters

You do not have to format by hand. SQLFluff is a free, open source linter and formatter that supports many dialects and can enforce a team style in CI. Most editors (VS Code, DataGrip, DBeaver) have a "format SQL" command. Agree on the settings once, then let the tool do it.

## Common mistakes

- **Single-letter or numbered aliases** (`a`, `t1`) in a query longer than a few lines.
- **Cryptic output names** that leak into dashboards and spreadsheets.
- **Comma joins** with the join condition hidden in `WHERE`.
- **Mixing `AND` and `OR` without brackets.**
- **Comments that repeat the code** instead of explaining the reason.

## Quick recap

- One clause per line, consistent keyword case, explicit `JOIN ... ON`.
- Meaningful table aliases and business-friendly column names.
- CTEs for steps; comments for business rules.
- Use a formatter such as SQLFluff to keep a team consistent.

## Try it

Open the **Do** tab and refactor a one-line query into a readable, commented one with clear column names.


Refactor queries so others can read, review and trust them, with clear layout, aliases, CTEs and comments.

SQL style and comments

SQL Style Guide (for Readability, Portability, Consistency)

SQL Style Essentials: Best Practices & Semicolon Usage Explained

## Why it matters

On a table of 1,000 rows every query is instant. On 100 million rows, the same query can take minutes, time out a dashboard or slow down the application that shares the database. Analysts and DBAs do not need to know every detail of a database engine, but they do need to know what an **index** is, when it helps, and how to read a **query plan** to see whether the database is using one.

## What an index is

Think of the index at the back of a textbook. To find every page about "photosynthesis" you do not read the whole book; you look the word up in a sorted list that points to page numbers. A database index is the same idea: a separate, **sorted** structure (usually a B-tree) holding the values of one or more columns plus a pointer to each matching row.

```sql
CREATE INDEX idx_orders_customer ON orders (customer_id);
```

Without that index, `WHERE customer_id = 42` makes the database read **every** row (a full table scan). With it, the database jumps straight to the 42s, reading a tiny fraction of the table. Most databases automatically index primary keys and columns with a `UNIQUE` constraint.

## Reading a query plan

You ask the database how it **plans** to run a query, without running it, using `EXPLAIN`. In SQLite the readable form is `EXPLAIN QUERY PLAN`:

```sql
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;
```

Before the index:

```text
SCAN orders
```

After `CREATE INDEX idx_orders_customer ON orders (customer_id)`:

```text
SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
```

In SQLite plans:

- **`SCAN`** means it reads the whole table (or the whole index) from start to finish.
- **`SEARCH`** means it uses an index or the primary key to jump to matching rows.
- **`USING COVERING INDEX`** means the index alone contains every column the query needs, so the table itself is not read at all: the fastest case.
- **`USE TEMP B-TREE FOR ORDER BY`** (or `GROUP BY`) means it has to sort rows itself because no index provides that order.

Other databases show more detail. PostgreSQL's `EXPLAIN` prints nodes such as `Seq Scan` (full scan), `Index Scan` and `Bitmap Heap Scan`, with estimated costs and row counts; `EXPLAIN ANALYZE` actually runs the query and shows real times. MySQL has `EXPLAIN` with a `type` column (`ALL` = full scan, `ref`/`range` = index use). SQL Server shows graphical execution plans. The vocabulary differs, but the main question is always the same: **is it scanning everything, or using an index to read only what it needs?**

## When an index can be used

An index on `(customer_id)` helps conditions that compare the **bare column**:

```sql
WHERE customer_id = 42                -- can use the index
WHERE customer_id IN (1, 2, 3)        -- can use the index
WHERE customer_id + 0 = 42            -- cannot: the column is inside an expression
```

The same applies to functions: `WHERE substr(order_date, 1, 4) = '2026'` cannot use a plain index on `order_date`, but the equivalent range `WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'` can. Rewriting filters as ranges on the raw column is one of the most useful habits for fast analytical SQL. Similarly, `LIKE 'abc%'` (prefix) can sometimes use an index, while `LIKE '%abc'` cannot.

## Composite indexes and column order

An index can cover several columns: `CREATE INDEX idx_cust_date ON orders (customer_id, order_date)`. It is sorted by `customer_id` first, then `order_date` within each customer, like a phone book sorted by last name then first name. So it helps:

- `WHERE customer_id = 42`
- `WHERE customer_id = 42 AND order_date >= '2026-01-01'`

but generally **not** `WHERE order_date >= '2026-01-01'` alone, just as a phone book does not help you find everyone named "Sam". Put the column you filter with `=` most often first.

## Indexes are not free

Every index:

- takes disk space, sometimes as much as the table;
- must be updated on every `INSERT`, `UPDATE` and `DELETE`, which slows writes;
- gives the planner more choices, and a bad index can occasionally be picked.

So you do not index every column. You index columns that appear often in `WHERE`, `JOIN ... ON` and `ORDER BY` of important queries, on tables big enough to matter. For a very small table, or a condition that matches most of the rows (say `status = 'shipped'` when 95% are shipped), a full scan is often faster and the planner will rightly ignore the index.

## Analysts and indexes in practice

In many companies analysts query a **data warehouse** (BigQuery, Snowflake, Redshift, Databricks). These use columnar storage, partitioning and clustering instead of classic B-tree indexes, so the advice becomes "filter on the partition column (usually a date) and select only the columns you need". In an operational database (PostgreSQL, MySQL, SQL Server), adding indexes is usually the DBA's or developer's job: an analyst's role is to notice a slow query, read its plan, and ask for the right index with evidence.

## Quick recap

- An index is a sorted lookup structure that lets the database find rows without scanning the table.
- `EXPLAIN QUERY PLAN` (SQLite) or `EXPLAIN` (PostgreSQL, MySQL) shows whether a query scans or searches.
- Compare bare columns or ranges; wrapping a column in a function or expression usually prevents index use.
- Composite indexes work left to right. Indexes speed reads and slow writes.

## Check yourself

This lesson has no coding task. Answer the questions in the **Do** tab, then try `EXPLAIN QUERY PLAN` on any query from earlier lessons in a SQLite tool such as DB Browser for SQLite or the `sqlite3` command line.


Understand what an index is, when the database can use one, and how to read a query plan to tell a scan from a search.

Indexes and EXPLAIN

SQL Indexes - Definition, Examples, and Tips

How to Read an Execution Plan: Databases for Developers: Performance #1

## Why it matters

In a job, nobody asks you to "write a CTE with a CASE expression". They ask a business question in plain language: "Which regions are growing?", "Did the spring promotion work?", "Which customers should account managers call this week?". Turning that question into a correct, readable query, and then into a clear answer, is the real skill. This capstone walks through the whole process once, then the Do tab asks you to finish it.

## The question

> The sales director asks: "Which regions grew revenue from Q1 to Q2 this year, and by how much?"

The data lives in three tables:

| table | columns |
|---|---|
| `customers` | `id`, `name`, `region` |
| `orders` | `id`, `customer_id`, `order_date`, `status` |
| `order_items` | `order_id`, `product`, `quantity`, `unit_price` |

## Step 1: pin down the definitions

Before writing SQL, turn every vague word into a rule, and confirm the rules with the person asking:

- **Revenue:** `quantity * unit_price` summed over order lines. (Not including tax or shipping, which are not in these tables.)
- **Which orders count:** everything except `status = 'cancelled'`.
- **Q1 and Q2:** January 1 to March 31 and April 1 to June 30, 2026, by `order_date`.
- **Growth:** `(Q2 - Q1) / Q1`, as a percentage.
- **Region:** the customer's region (a customer's region at the time of the order might differ, but we only have the current value; note this as a limitation).

Write these as comments at the top of the query. They are part of the answer.

## Step 2: explore the data

Quick checks first, so surprises do not end up in the final number:

```sql
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT MIN(order_date), MAX(order_date) FROM orders;
SELECT COUNT(*) FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;   -- orphan orders?
```

Here you would discover that there are cancelled orders to exclude and an order in July that must not leak into Q2. This is the data quality lesson in action.

## Step 3: build the query in steps

**Order lines with region.** Join the three tables and compute line revenue, excluding cancelled orders:

```sql
WITH order_lines AS (
  SELECT c.region,
         o.order_date,
         i.quantity * i.unit_price AS line_revenue
  FROM orders AS o
  JOIN customers AS c ON c.id = o.customer_id
  JOIN order_items AS i ON i.order_id = o.id
  WHERE o.status <> 'cancelled'
)
SELECT * FROM order_lines LIMIT 10;   -- check, then continue
```

**One row per region with both quarters.** This uses **conditional aggregation**: a `CASE` inside `SUM` puts each line in the right quarter column, and lines outside both quarters add 0.

```sql
by_region AS (
  SELECT region,
         SUM(CASE WHEN order_date BETWEEN '2026-01-01' AND '2026-03-31'
                  THEN line_revenue ELSE 0 END) AS q1_revenue,
         SUM(CASE WHEN order_date BETWEEN '2026-04-01' AND '2026-06-30'
                  THEN line_revenue ELSE 0 END) AS q2_revenue
  FROM order_lines
  GROUP BY region
)
```

`BETWEEN` includes both ends. That is correct for plain dates like `'2026-03-31'`, but if `order_date` held timestamps such as `'2026-03-31 14:20'`, that value is greater than `'2026-03-31'` and would be missed. With timestamps, use `order_date >= '2026-01-01' AND order_date < '2026-04-01'` instead.

**Growth and sort.** The final `SELECT` computes the percentage and orders the regions from fastest to slowest growth:

```sql
SELECT region, q1_revenue, q2_revenue,
       ROUND((q2_revenue - q1_revenue) * 100.0 / NULLIF(q1_revenue, 0), 1) AS growth_pct
FROM by_region
ORDER BY growth_pct DESC;
```

`NULLIF(q1_revenue, 0)` protects against a region that had no Q1 sales: its growth becomes `NULL` (undefined) instead of an error or a nonsense number. SQLite and MySQL treat `NULL` as smaller than any value, so with `ORDER BY growth_pct DESC` such regions appear last; PostgreSQL treats `NULL` as larger, so they would appear first unless you add `NULLS LAST`.

## Step 4: sanity-check the result

Before sending anything:

- Does the sum of `q1_revenue` across regions match a simple total of Q1 revenue computed another way?
- Does any region have very few orders, so a big percentage is really a small change? (East going from 75 to 165 is +120%, but only 90 in money.)
- Are cancelled orders really excluded? Count them separately.

## Step 5: communicate the answer

A query result is not an answer. Write two or three sentences a director can act on, with the caveats:

> South grew fastest (+144%, 180 to 440), followed by East (+120%, from a small base) and North (+68%). West fell 92%, because one large Q1 order (a standing desk) was not repeated. Cancelled orders are excluded; figures use each customer's current region.

Attach the query, with its definition comments, so anyone can reproduce or audit the numbers.

## A reusable checklist

1. Restate the question and agree on definitions.
2. Explore and quality-check the tables involved.
3. Build with CTEs, checking each step.
4. Sanity-check totals and small bases.
5. Write the answer in plain language with caveats, and share the query.

## Try it

Open the **Do** tab. The order-lines step is written; add the cancelled-order filter, split revenue into Q1 and Q2, and compute and sort the growth.


Answer a business question end to end: define the terms, explore the data, build the query in steps and communicate the result.

Analysis project in SQL

How I use SQL as a Data Analyst

Data Analyst Portfolio Project: Restaurant Order Analysis in SQL (with Solutions)

Write SQL others can read and trust, understand how indexes and query plans affect speed, and finish with an end-to-end analysis project.