Lab 1 — Answer a business question with SQL

Objective

By the end of this lab you will have a working, self-contained analysis on your own machine: a small e-commerce database (shop.db) built from Python's standard-library sqlite3, a set of SQL queries that answer a stakeholder's question, and a written answer.txt a head of growth could read in ten seconds. The question is the one this course opened with: "Which region drove the most completed revenue in Q2 2026, and who are our top customers?"

Skills exercised:

Reading one table with SELECT, WHERE, and ORDER BY
Summarizing with GROUP BY and aggregate functions
Joining orders to customers and filtering a date range correctly
Translating a query result back into a plain-English answer

Prerequisites and setup

Prerequisites: the three lessons in this module — Select, filter, and sort, Aggregation and GROUP BY, and Joins for analysts.

Setup: you need Python 3.8 or newer. There is nothing to install — sqlite3 ships with Python's standard library. Verify your version and create a working folder.

macOS / Linux
Windows (PowerShell)

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

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

Expected output from the version command is a line like Python 3.11.6. If the command is not found, install Python from python.org before continuing. The command blocks below use python3; on Windows type python instead.

note

Every step writes files into this one folder, and the database shop.db is a real file on disk. If you close your terminal and come back, re-run cd da101-sql-lab to return — your database and queries are all still there, so you never redo earlier steps.

Step 1 — Build and load the database

Create the e-commerce dataset the stakeholder's question lives in: a customers table and an orders table, connected by customer_id.

Save this exactly as setup_shop.py in your lab folder:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

cur.executescript(
    """
    DROP TABLE IF EXISTS orders;
    DROP TABLE IF EXISTS customers;

    CREATE TABLE customers (
        customer_id INTEGER PRIMARY KEY,
        full_name   TEXT NOT NULL,
        region      TEXT NOT NULL,
        signup_date TEXT NOT NULL
    );

    CREATE TABLE orders (
        order_id    INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
        order_date  TEXT NOT NULL,
        amount      REAL NOT NULL,
        status      TEXT NOT NULL
    );
    """
)

customers = [
    (1, "Amara Okafor", "West", "2025-11-02"),
    (2, "Diego Fernandez", "West", "2026-01-15"),
    (3, "Priya Nair", "East", "2025-09-21"),
    (4, "Liam Walsh", "East", "2026-02-08"),
    (5, "Sofia Rossi", "North", "2026-03-30"),
    (6, "Kenji Tanaka", "North", "2025-12-11"),
]

orders = [
    (1001, 1, "2026-04-05", 120.00, "completed"),
    (1002, 1, "2026-05-18", 80.00, "completed"),
    (1003, 1, "2026-06-02", 60.00, "refunded"),
    (1004, 2, "2026-04-22", 200.00, "completed"),
    (1005, 2, "2026-06-25", 150.00, "completed"),
    (1006, 3, "2026-04-10", 300.00, "completed"),
    (1007, 3, "2026-05-05", 90.00, "completed"),
    (1008, 3, "2026-05-06", 45.00, "cancelled"),
    (1009, 4, "2026-06-15", 75.00, "completed"),
    (1010, 5, "2026-04-28", 110.00, "completed"),
    (1011, 5, "2026-06-30", 95.00, "completed"),
    (1012, 6, "2026-03-31", 500.00, "completed"),
    (1013, 6, "2026-05-20", 130.00, "completed"),
]

cur.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", customers)
cur.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", orders)
conn.commit()

n_customers = cur.execute("SELECT COUNT(*) FROM customers").fetchone()[0]
n_orders = cur.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
print(f"Loaded {n_customers} customers and {n_orders} orders into shop.db")

conn.close()

Notice the data has realistic wrinkles you will have to handle: order 1003 was refunded and 1008 was cancelled (neither is revenue), and order 1012 is dated March 31 — Q1, not Q2. Run the setup:

python3 setup_shop.py

Checkpoint: the command prints exactly Loaded 6 customers and 13 orders into shop.db, and a shop.db file now exists in your folder. If you get sqlite3.OperationalError, re-check that you copied the CREATE TABLE block verbatim; if the counts differ, re-check the two data lists.

Step 2 — Read the table with a query runner

Goal: build a tiny reusable runner so you can execute any SQL file against shop.db, then use it to eyeball the Q2 completed orders with SELECT / WHERE / ORDER BY.

Save this as query.py:

import sqlite3
import sys

with open(sys.argv[1], encoding="utf-8") as f:
    sql = f.read()

conn = sqlite3.connect("shop.db")
cur = conn.execute(sql)
headers = [d[0] for d in cur.description]
line = " | ".join(headers)
print(line)
print("-" * len(line))
for row in cur.fetchall():
    print(" | ".join(str(v) for v in row))
conn.close()

Now save this query as explore.sql. It reads one table with intent: only completed orders inside Q2 (April 1 through June 30), largest first. The < '2026-07-01' bound keeps the range clean — every date before July 1 is in Q2.

SELECT order_id, customer_id, amount, order_date
FROM orders
WHERE status = 'completed'
  AND order_date >= '2026-04-01'
  AND order_date <  '2026-07-01'
ORDER BY amount DESC;

Run it:

python3 query.py explore.sql

Checkpoint: you should see 10 rows of completed Q2 orders, topped by order 1006 at 300.0. The refunded (1003), cancelled (1008), and Q1 (1012) orders are correctly absent. If you see 13 rows, your WHERE clause is not filtering; if you see order 1012, your date upper bound is wrong.

Step 3 — Answer half the question: revenue by region

Goal: the region half of the stakeholder's question. Region lives on customers and amount lives on orders, so this needs a join, the Q2 filter, and a GROUP BY.

Save this as region_revenue.sql:

SELECT c.region, ROUND(SUM(o.amount), 2) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= '2026-04-01'
  AND o.order_date <  '2026-07-01'
GROUP BY c.region
ORDER BY revenue DESC;

Run it:

python3 query.py region_revenue.sql

Checkpoint: you should see three regions, with West on top at 550.0, East at 465.0, and North at 335.0. If North shows 835.0, the Q1 order 1012 ($500) is leaking in — check the date bounds. If you see more than three rows or a NULL region, check the ON condition.

Step 4 — Answer the other half: top customers

Goal: the top-customers half. Same join and filter as Step 3, but grouped by customer and capped to the top three by revenue.

Save this as top_customers.sql:

SELECT c.full_name, c.region, ROUND(SUM(o.amount), 2) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= '2026-04-01'
  AND o.order_date <  '2026-07-01'
GROUP BY c.customer_id
ORDER BY revenue DESC
LIMIT 3;

Run it:

python3 query.py top_customers.sql

Checkpoint: the top row is Priya Nair | East | 390.0, followed by Diego Fernandez (350.0) and Sofia Rossi (205.0). Here is the analytical catch worth noticing: the top region is West, but the single biggest customer, Priya, is in East. If your top customer is someone else, re-check that you grouped by c.customer_id and kept the LIMIT 3.

Step 5 — Assemble the stakeholder's answer

Goal: turn two query results into one written answer a busy stakeholder reads in seconds. This is the analyst's actual deliverable — the SQL is a means to it.

Save this as answer.py:

import sqlite3

conn = sqlite3.connect("shop.db")
Q2 = (
    "o.status = 'completed' "
    "AND o.order_date >= '2026-04-01' "
    "AND o.order_date < '2026-07-01'"
)

region = conn.execute(
    f"""
    SELECT c.region, ROUND(SUM(o.amount), 2) AS revenue
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.customer_id
    WHERE {Q2}
    GROUP BY c.region
    ORDER BY revenue DESC
    """
).fetchall()

top3 = conn.execute(
    f"""
    SELECT c.full_name, c.region, ROUND(SUM(o.amount), 2) AS revenue
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.customer_id
    WHERE {Q2}
    GROUP BY c.customer_id
    ORDER BY revenue DESC
    LIMIT 3
    """
).fetchall()
conn.close()

top_region, top_region_rev = region[0]
lead_name, lead_region, _ = top3[0]

lines = ["Q2 2026 revenue answer for the head of growth", ""]
lines.append(
    f"Top region: {top_region} led Q2 with ${top_region_rev:,.2f} "
    "in completed revenue."
)
lines.append("Revenue by region:")
for r, rev in region:
    lines.append(f"  - {r}: ${rev:,.2f}")
lines.append("Top 3 customers:")
for name, reg, rev in top3:
    lines.append(f"  - {name} ({reg}): ${rev:,.2f}")
lines.append("")
lines.append(
    f"Headline: {top_region} is the strongest region, but the biggest "
    f"single customer, {lead_name}, is in {lead_region} - worth a look."
)

answer = "\n".join(lines)
print(answer)
with open("answer.txt", "w", encoding="utf-8") as f:
    f.write(answer + "\n")

Run it:

python3 answer.py

Checkpoint: the script prints the full answer and writes answer.txt. The first content line reads Top region: West led Q2 with $550.00 in completed revenue. and the headline names Priya Nair in East. If the file is empty, check that the with open(...) block is indented at the top level, not inside a loop.

Validation

Verify the whole build against the objective: the numbers in your answer must be correct, not just present. Save this independent checker as validate.py — it recomputes both headline figures straight from shop.db and asserts them.

import sqlite3

conn = sqlite3.connect("shop.db")
Q2 = (
    "o.status = 'completed' "
    "AND o.order_date >= '2026-04-01' "
    "AND o.order_date < '2026-07-01'"
)

region_rows = conn.execute(
    f"""
    SELECT c.region, ROUND(SUM(o.amount), 2) AS revenue
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.customer_id
    WHERE {Q2}
    GROUP BY c.region
    ORDER BY revenue DESC
    """
).fetchall()

top_customer = conn.execute(
    f"""
    SELECT c.full_name, c.region, ROUND(SUM(o.amount), 2) AS revenue
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.customer_id
    WHERE {Q2}
    GROUP BY c.customer_id
    ORDER BY revenue DESC
    LIMIT 1
    """
).fetchone()
conn.close()

top_region, top_region_rev = region_rows[0]
name, region, rev = top_customer

checks = {
    "top region is West at $550.00": (top_region, top_region_rev)
    == ("West", 550.0),
    "three regions returned": len(region_rows) == 3,
    "top customer is Priya Nair (East) at $390.00": (name, region, rev)
    == ("Priya Nair", "East", 390.0),
}

for label, ok in checks.items():
    print(f"[{'ok' if ok else 'XX'}] {label}")

if all(checks.values()):
    print(
        "PASSED - West led Q2 with $550.00; Priya Nair (East) is the "
        "top customer at $390.00"
    )
else:
    print("FAILED - re-check your Q2 filter, join, and grouping")

Run it:

python3 validate.py

Checkpoint: every line starts with [ok] and the last line reads PASSED - West led Q2 with $550.00; Priya Nair (East) is the top customer at $390.00. If any line starts with [XX], that check names what is wrong: a region total that is off points at the date filter or the join; a wrong top customer points at the GROUP BY.

Teardown

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

Troubleshooting

Symptom	Likely cause	Fix
`sqlite3.OperationalError: no such table: orders`	`setup_shop.py` was not run, or was run in a different folder	Run `python3 setup_shop.py` from inside `da101-sql-lab` before any query
Step 2 shows 13 rows instead of 10	The `WHERE` status/date filter is missing or altered	Copy `explore.sql` exactly, including all three `AND` conditions
Order `1012` appears in Q2 results	Upper date bound wrong or missing	The bound must be `order_date < '2026-07-01'`, excluding Q1 dates
Region revenue totals look inflated	Cancelled or refunded orders counted as revenue	Keep only `status = 'completed'` in the `WHERE` clause
`IndexError: list index out of range` in a script	A query returned no rows, so `region[0]` fails	Confirm `shop.db` loaded (Step 1) and the date strings are quoted
`validate.py` prints `[XX]` on the top customer	Grouped by the wrong column	Group by `c.customer_id`, not `c.region`, in `top_customers`

Objective​

Prerequisites and setup​

Step 1 — Build and load the database​

Step 2 — Read the table with a query runner​

Step 3 — Answer half the question: revenue by region​

Step 4 — Answer the other half: top customers​

Step 5 — Assemble the stakeholder's answer​

Validation​

Teardown​

Troubleshooting​

Objective

Prerequisites and setup

Step 1 — Build and load the database

Step 2 — Read the table with a query runner

Step 3 — Answer half the question: revenue by region

Step 4 — Answer the other half: top customers

Step 5 — Assemble the stakeholder's answer

Validation

Teardown

Troubleshooting