Lab 1 — Build an analytical query

Objective

By the end of this lab you will have a local SQLite database, shop.db, holding a small online-bookstore dataset, plus a set of Python scripts that answer a real analytical question: which customer generated the most completed-order revenue, and how does the engine plan that lookup? You will build the query in layers — join, aggregate, rank — then read its execution plan and add an index that changes it. Everything uses Python's standard-library sqlite3 module: no server, no install, no cost.

Skills exercised:

Joining three related tables without dropping or double-counting rows
Aggregating revenue with GROUP BY and a completed-orders filter
Ranking groups with a RANK() window function
Reading an execution plan and adding an index that turns a scan into a search

Prerequisites and setup

Prerequisites: the three lessons in this course — Joins and Set Operations, Aggregation and Window Functions, and Query Performance and EXPLAIN.

Setup: you need Python 3.10 or newer. The sqlite3 module ships with Python, so there is nothing to install. Verify your version and create a working folder.

macOS / Linux
Windows (PowerShell)

python3 --version
mkdir de102-sql-lab && cd de102-sql-lab

python --version
mkdir de102-sql-lab; cd de102-sql-lab

Expected output from the version command is a line like Python 3.12.10. If the command is not found, install Python from python.org before continuing. In the steps below, use python3 on macOS/Linux and python on Windows.

info

This lab uses window functions, which require the SQLite library bundled with Python to be version 3.25 or newer. Any Python 3.10+ from python.org already meets this. To check, run python3 -c "import sqlite3; print(sqlite3.sqlite_version)" — you need 3.25.0 or higher.

note

Every step writes a small Python file and reads or writes shop.db in this one folder. If you close your terminal and come back, re-run the cd de102-sql-lab command to return to it; shop.db and your scripts are still there, so you can resume at the step you left off.

Step 1 — Create the database and load the data

Goal: build shop.db with three tables — customers, orders, and order items — and confirm the row counts.

Save this exactly as build_db.py:

"""Create shop.db: a small online-bookstore dataset for the lab."""
import sqlite3

con = sqlite3.connect("shop.db")
con.executescript("""
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    country     TEXT NOT NULL,
    signup_date TEXT NOT NULL
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  TEXT NOT NULL,
    status      TEXT NOT NULL
);

CREATE TABLE order_items (
    item_id    INTEGER PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    product    TEXT NOT NULL,
    category   TEXT NOT NULL,
    quantity   INTEGER NOT NULL,
    unit_price REAL NOT NULL
);

INSERT INTO customers VALUES
    (1, 'Ana Duarte',  'PT', '2025-11-02'),
    (2, 'Bilal Khan',  'IN', '2025-11-15'),
    (3, 'Chen Wei',    'SG', '2026-01-08'),
    (4, 'Dara Okoro',  'NG', '2026-02-20'),
    (5, 'Elena Rossi', 'IT', '2026-03-30');

INSERT INTO orders VALUES
    (101, 1, '2026-06-01', 'completed'),
    (102, 1, '2026-06-15', 'completed'),
    (103, 2, '2026-06-03', 'completed'),
    (104, 2, '2026-06-20', 'refunded'),
    (105, 3, '2026-06-10', 'completed'),
    (106, 3, '2026-06-25', 'pending'),
    (107, 4, '2026-07-01', 'completed');

INSERT INTO order_items VALUES
    (1,  101, 'The Pragmatic Programmer', 'tech',       1, 39.99),
    (2,  101, 'Clean Code',               'tech',       1, 32.50),
    (3,  102, 'Dune',                     'fiction',    2, 14.00),
    (4,  103, 'Sapiens',                  'nonfiction', 1, 24.00),
    (5,  103, 'Educated',                 'nonfiction', 1, 18.00),
    (6,  104, 'Foundation',               'fiction',    3, 12.00),
    (7,  105, 'Data-Intensive Apps',      'tech',       1, 45.00),
    (8,  105, 'The Phoenix Project',      'tech',       1, 22.00),
    (9,  106, 'Project Hail Mary',        'fiction',    1, 16.00),
    (10, 107, 'Atomic Habits',            'nonfiction', 2, 20.00);
""")
con.commit()

counts = {
    t: con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
    for t in ("customers", "orders", "order_items")
}
print(counts)
con.close()

Note the two customers and orders that will matter later: customer 5 (Elena) placed no orders, order 104 was refunded, and order 106 is still pending. Your revenue query must handle all three.

Checkpoint: run the builder.

python3 build_db.py

You should see exactly:

{'customers': 5, 'orders': 7, 'order_items': 10}

If you see an error about a locked database, close any other program holding shop.db and re-run. If counts differ, re-copy the INSERT statements — every value row must be present.

Step 2 — Join the three tables

Goal: combine customers, orders, and order items into one wide result and see the raw joined rows — including the refunded and pending orders you will filter out later.

Save this as join_query.py:

"""Step 2: join customers, orders, and items into one wide row set."""
import sqlite3

con = sqlite3.connect("shop.db")
rows = con.execute("""
    SELECT c.name, o.order_id, o.status, i.product, i.quantity, i.unit_price
    FROM customers   c
    JOIN orders      o ON o.customer_id = c.customer_id
    JOIN order_items i ON i.order_id = o.order_id
    ORDER BY o.order_id, i.item_id
""").fetchall()
for row in rows:
    print(row)
print("rows:", len(rows))
con.close()

Checkpoint: run it.

python3 join_query.py

You should see 10 rows and this final line:

rows: 10

The result has one row per order item, so orders with two items (like 101 and 103) appear twice — that is the fan-out from the joins lesson, and it is expected here. Notice Elena never appears: she has no orders, so the inner JOIN drops her. The refunded order 104 and pending order 106 do appear; you will filter them in the next step. If you see fewer than 10 rows, your shop.db is missing item rows — re-run Step 1.

Step 3 — Aggregate revenue per category

Goal: collapse the joined rows into revenue per category, counting only completed orders.

Save this as revenue_query.py:

"""Step 3: revenue per category from completed orders only."""
import sqlite3

con = sqlite3.connect("shop.db")
rows = con.execute("""
    SELECT i.category,
           ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
    FROM orders      o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY i.category
    ORDER BY revenue DESC
""").fetchall()
for row in rows:
    print(row)
con.close()

Checkpoint: run it.

python3 revenue_query.py

You should see exactly:

('tech', 139.49)
('nonfiction', 82.0)
('fiction', 28.0)

Revenue is quantity * unit_price summed per category. The WHERE o.status = 'completed' filter is what excludes the refunded order 104 (a fiction sale of 36.00) and the pending order 106. If fiction shows 64.0, your filter is missing and the refunded and pending fiction orders leaked in.

Step 4 — Rank customers by revenue

Goal: find each customer's completed-order revenue and rank them, using a RANK() window function over an aggregated result.

Save this as ranking_query.py:

"""Step 4: rank customers by completed revenue with a window function."""
import sqlite3

con = sqlite3.connect("shop.db")
rows = con.execute("""
    WITH customer_revenue AS (
        SELECT c.customer_id, c.name,
               SUM(i.quantity * i.unit_price) AS revenue
        FROM customers   c
        JOIN orders      o ON o.customer_id = c.customer_id
        JOIN order_items i ON i.order_id = o.order_id
        WHERE o.status = 'completed'
        GROUP BY c.customer_id, c.name
    )
    SELECT name,
           ROUND(revenue, 2) AS revenue,
           RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
    FROM customer_revenue
    ORDER BY revenue_rank
""").fetchall()
for row in rows:
    print(row)
con.close()

Checkpoint: run it.

python3 ranking_query.py

You should see exactly:

('Ana Duarte', 100.49, 1)
('Chen Wei', 67.0, 2)
('Bilal Khan', 42.0, 3)
('Dara Okoro', 40.0, 4)

The CTE first aggregates revenue per customer; the outer query then ranks those rows without collapsing them. Ana is rank 1 with 100.49. Elena is absent — with no completed orders she has no revenue row to rank. If Ana shows a value other than 100.49, revisit Step 3's completed-orders filter, which this query reuses.

Step 5 — Read the plan and add an index

Goal: read how the engine plans a per-customer order lookup, then add an index and confirm the plan changes from a scan to a search.

Save this as explain_query.py:

"""Step 5: read the plan for a per-customer lookup, then add an index."""
import sqlite3

con = sqlite3.connect("shop.db")
query = "SELECT order_id, order_date FROM orders WHERE customer_id = 1"

print("BEFORE index:")
for row in con.execute("EXPLAIN QUERY PLAN " + query):
    print(" ", row[-1])

con.execute(
    "CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)"
)

print("AFTER index:")
for row in con.execute("EXPLAIN QUERY PLAN " + query):
    print(" ", row[-1])
con.commit()
con.close()

Checkpoint: run it.

python3 explain_query.py

You should see exactly:

BEFORE index:
  SCAN orders
AFTER index:
  SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

The plan changed from reading every order (SCAN orders) to jumping straight to customer 1's orders through the new index (SEARCH … USING INDEX). The index now lives inside shop.db, so the validation in the next section can confirm it exists. If both lines still say SCAN, the CREATE INDEX did not run — check for a typo in the index statement.

Validation

Verify the whole build against the objective: the correct top customer, the correct total revenue, and the index in place. Save this as validate.py:

"""Validation: verify the analytical build end to end."""
import sqlite3

con = sqlite3.connect("shop.db")

# 1) Top customer by completed revenue.
top = con.execute("""
    SELECT c.name, ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
    FROM customers   c
    JOIN orders      o ON o.customer_id = c.customer_id
    JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY c.customer_id, c.name
    ORDER BY revenue DESC
    LIMIT 1
""").fetchone()

# 2) Total completed revenue across all categories.
total = con.execute("""
    SELECT ROUND(SUM(i.quantity * i.unit_price), 2)
    FROM orders      o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status = 'completed'
""").fetchone()[0]

# 3) The per-customer index exists.
has_index = con.execute("""
    SELECT COUNT(*) FROM sqlite_master
    WHERE type = 'index' AND name = 'idx_orders_customer'
""").fetchone()[0]

con.close()

checks = [
    ("top customer is Ana Duarte", top[0] == "Ana Duarte"),
    ("top customer revenue is 100.49", top[1] == 100.49),
    ("total completed revenue is 249.49", total == 249.49),
    ("idx_orders_customer exists", has_index == 1),
]
for label, ok in checks:
    print(f"  [{'x' if ok else ' '}] {label}")

if all(ok for _, ok in checks):
    print("PASSED - analytical query complete")
else:
    print("FAILED - review the checks above")

Checkpoint: run it.

python3 validate.py

Every check should be marked, and the last line should read:

  [x] top customer is Ana Duarte
  [x] top customer revenue is 100.49
  [x] total completed revenue is 249.49
  [x] idx_orders_customer exists
PASSED - analytical query complete

If any line prints FAILED, the unmarked check names the problem: a wrong top customer or total points back to Step 3's completed-orders filter, and a missing index means Step 5's explain_query.py did not run against this shop.db.

Teardown

Nothing is billable — this lab runs entirely on your machine. Keep the de102-sql-lab folder if you want to reuse shop.db and the query scripts as a reference; otherwise delete it with rm -rf de102-sql-lab (macOS/Linux) or Remove-Item -Recurse de102-sql-lab (Windows PowerShell).

Troubleshooting

Symptom	Likely cause	Fix
`sqlite3.OperationalError: near "OVER": syntax error`	SQLite library older than 3.25	Use Python 3.10+ from python.org; check `sqlite3.sqlite_version`
`sqlite3.OperationalError: no such table: customers`	Running a query before Step 1 built the DB	Run `python3 build_db.py` first, from the `de102-sql-lab` folder
Step 2 prints fewer than 10 rows	Missing `order_items` rows	Re-run Step 1; every `INSERT INTO order_items` value row must be present
`fiction` totals `64.0` in Step 3	Missing completed-orders filter	Keep only rows where `o.status = 'completed'`
Step 5 shows `SCAN` on both lines	`CREATE INDEX` did not execute	Confirm the `CREATE INDEX … ON orders(customer_id)` line ran without error
Validation reports `idx_orders_customer` missing	Step 5 ran against a different DB copy	Run `explain_query.py` from the same folder as `shop.db`, then re-validate

Objective​

Prerequisites and setup​

Step 1 — Create the database and load the data​

Step 2 — Join the three tables​

Step 3 — Aggregate revenue per category​

Step 4 — Rank customers by revenue​

Step 5 — Read the plan and add an index​

Validation​

Teardown​

Troubleshooting​

Objective

Prerequisites and setup

Step 1 — Create the database and load the data

Step 2 — Join the three tables

Step 3 — Aggregate revenue per category

Step 4 — Rank customers by revenue

Step 5 — Read the plan and add an index

Validation

Teardown

Troubleshooting