Projects

Chapter 24 32 code blocks 12236 words

Everything in this guide so far has been taught one idea at a time: a GROUP BY here, a boxplot there. Real analytical work never arrives in that shape. It arrives as a question from somebody who does not know or care which tool you use, and the answer has to travel the whole distance — from rows sitting in a database, through cleaning and reshaping, to a chart and a sentence a non-analyst can act on.

This closing chapter contains four end-to-end projects. Each one runs the full stack taught in this guide:

Projects 1 and 2 use school.db, the small teaching database built in Chapter 1. It has ten students and twenty enrolments, which is exactly the right size for a learner to check every intermediate result by hand — and exactly the wrong size for an interesting chart. So projects 3 and 4 build a larger, realistic SQLite database from scratch with numpy. The generator is reproducible (a fixed seed), needs no internet connection, and takes about a second to run.

24.1 How to use these projects

These are not exercises to be handed out and collected. They are designed to be run with the class, then handed over.

Suggested pacing

Each project is roughly one three-hour session if you demonstrate it live and let students type along. A workable rhythm:

• 0:00–0:15 — read the business question aloud. Before showing any code, ask the class which tables they would need and which columns. Write their answer on the board; compare it with the query in Step 1.
• 0:15–0:45 — Step 1 together. Run the query. Look at the raw result table. Ask: how many rows did we expect, and how many did we get? This single question catches most join mistakes.
• 0:45–1:15 — Step 2. This is where students are weakest. Insist that they call df.info() and df.isna().sum() every single time, out loud, before they touch a chart.
• 1:15–2:15 — Step 3. Build the charts one at a time. After each one, before you explain it, ask the class what they see. Their wrong readings are the most useful teaching material in the room.
• 2:15–2:45 — Step 4 and the gotchas. Have a student state the finding in one sentence with no jargon.
• 2:45–3:00 — set the extension tasks as homework.

What to assess

Resist grading on chart prettiness. The skills that matter, in order:

1. Does the SQL answer the question asked? Not a similar question. The exact one.
2. Did they notice the missing data? Every project here contains at least one deliberate NULL trap.
3. Is the aggregation done at the right level? Aggregating after a fan-out join is the single most common professional error, and it is silent.
4. Does the chart type match the question? Distribution → boxplot or histogram. Comparison of categories → barplot. Change over time → lineplot. Two-way counts → heatmap.
5. Can they state the finding without naming a single function?

24.2 Shared setup

Every project starts from the same six lines. Put them in the first cell of every notebook.

# --- standard imports for every project in this chapter ---
import sqlite3
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_theme(style="whitegrid", palette="deep")
pd.set_option("display.max_columns", 40)
pd.set_option("display.width", 140)

The connection and the helper

Opening a connection for every query and forgetting to close it is a habit worth breaking early. One tiny helper function removes the repetition and gives us a single place to fix problems later.

DB_SCHOOL = "school_big.db"      # built in Chapter 1
DB_SHOP   = "shop.db"        # generated below, in 24.5

def q(sql, db=DB_SCHOOL, params=None):
    """Run a SELECT and return the result as a DataFrame."""
    with sqlite3.connect(db) as conn:          # closes/commits automatically
        return pd.read_sql_query(sql, conn, params=params)

# --- build school_big.db once: Chapter 1's schema at realistic volume ------
# Chapter 1's school.db holds 20 enrolments: perfect for reading a JOIN by eye,
# far too small to compute a pass rate from. This rebuilds the SAME schema at
# scale and preserves every teaching quirk that the queries below rely on:
# one course with no enrolments, NULL cities, NULL teacher_ids, NULL grades.
from pathlib import Path

SEED_SCHOOL = 11
if not Path(DB_SCHOOL).exists():
    rng = np.random.default_rng(SEED_SCHOOL)
    con = sqlite3.connect(DB_SCHOOL)
    con.executescript("""
        CREATE TABLE students (
            student_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL,
            last_name TEXT NOT NULL, gender TEXT CHECK (gender IN ('M','F')),
            city TEXT, birth_date DATE, enrolled_date DATE NOT NULL);
        CREATE TABLE teachers (
            teacher_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL,
            subject TEXT, hired_date DATE, salary DECIMAL(10,2));
        CREATE TABLE courses (
            course_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
            category TEXT, credits INTEGER DEFAULT 3, price DECIMAL(10,2));
        CREATE TABLE enrollments (
            enrollment_id INTEGER PRIMARY KEY, student_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL, teacher_id INTEGER,
            enrolled_at DATE NOT NULL, grade INTEGER,
            status TEXT DEFAULT 'active',
            FOREIGN KEY (student_id) REFERENCES students(student_id),
            FOREIGN KEY (course_id)  REFERENCES courses(course_id),
            FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id));
    """)

    teachers = [("Nodira Alimova","Data Science","2021-08-15",9500000),
                ("Rustam Halilov","Databases","2019-03-01",11000000),
                ("Kamola Ergasheva","Statistics","2023-01-10",8200000),
                ("Sardor Yoqubov","Programming","2024-09-01",7800000),
                ("Dilshod Umarov","Programming","2020-02-17",8900000),
                ("Feruza Saidova","Data Science","2022-06-05",9100000),
                ("Jahongir Aliev","Mathematics","2018-11-20",10200000),
                ("Zulfiya Karimova","Statistics","2023-09-12",7950000)]
    con.executemany("INSERT INTO teachers VALUES (?,?,?,?,?)",
                    [(i+1,)+t for i,t in enumerate(teachers)])

    # course 12 deliberately receives NO enrolments - the LEFT JOIN trap
    courses = [("Python Basics","Programming",4,1200000),
               ("SQL for Analytics","Data",3,1500000),
               ("Statistics 101","Math",4,1100000),
               ("Data Visualization","Data",3,1300000),
               ("Machine Learning","Data",5,2100000),
               ("Linear Algebra","Math",4,1000000),
               ("Web Scraping","Programming",2,900000),
               ("Time Series Analysis","Data",4,1750000),
               ("Probability Theory","Math",4,1150000),
               ("Deep Learning","Data",5,2400000),
               ("Git and Workflow","Programming",2,700000),
               ("Research Seminar","Math",1,0)]
    con.executemany("INSERT INTO courses VALUES (?,?,?,?,?)",
                    [(i+1,)+c for i,c in enumerate(courses)])

    fem = ["Aziza","Dilnoza","Malika","Sevara","Zarina","Nilufar","Gulnora",
           "Kamila","Shahzoda","Feruza","Nodira","Mohira"]
    mal = ["Bekzod","Jasur","Otabek","Timur","Ulugbek","Sanjar","Aziz",
           "Doniyor","Shohruh","Farrux","Islom","Bobur"]
    last = ["Karimova","Toshev","Yusupova","Rahimov","Sobirova","Nazarov",
            "Ismoilova","Abdullaev","Qodirova","Mirzaev","Ergashev","Saidova"]
    cities = ["Tashkent","Samarkand","Bukhara","Namangan","Andijan","Fergana","Nukus"]
    starts = ["2025-09-01","2025-09-15","2026-02-01","2026-02-10","2026-09-01"]

    rows = []
    for sid in range(1, 241):
        male = rng.random() < 0.5
        city = None if rng.random() < 0.07 else str(rng.choice(cities))  # ~7% NULL
        bd = f"{int(rng.integers(2002,2007))}-{int(rng.integers(1,13)):02d}-{int(rng.integers(1,29)):02d}"
        rows.append((sid, str(rng.choice(mal if male else fem)), str(rng.choice(last)),
                     "M" if male else "F", city, bd, str(rng.choice(starts))))
    con.executemany("INSERT INTO students VALUES (?,?,?,?,?,?,?)", rows)

    pop = np.array([1.6,1.5,1.2,1.1,1.3,0.7,0.8,0.9,0.7,1.0,1.1]); pop = pop/pop.sum()
    mean_grade = {1:78, 2:84, 3:71, 4:80, 5:74, 6:69, 7:82, 8:73, 9:70, 10:68, 11:88}

    enr, eid = [], 1
    for sid in range(1, 241):
        picks = rng.choice(np.arange(1,12), size=int(rng.integers(3,9)),
                           replace=False, p=pop)
        for cid in picks:
            cid = int(cid)
            tid = None if rng.random() < 0.05 else int(rng.integers(1,9))  # ~5% NULL
            r = rng.random()
            status = "completed" if r < 0.62 else ("active" if r < 0.88 else "dropped")
            if status == "completed":
                g = int(np.clip(rng.normal(mean_grade[cid], 11), 35, 100))
            elif status == "dropped" and rng.random() < 0.4:
                g = int(np.clip(rng.normal(mean_grade[cid]-18, 12), 20, 75))
            else:
                g = None                       # active = not graded yet
            enr.append((eid, sid, cid, tid, str(rng.choice(starts)), g, status))
            eid += 1
    con.executemany("INSERT INTO enrollments VALUES (?,?,?,?,?,?,?)", enr)
    con.commit(); con.close()
    print("school_big.db built")

Check your SQLite version

Projects 2, 3 and 4 use window functions (RANK, LAG, NTILE), which SQLite gained in version 3.25, and Project 1 uses the aggregate FILTER clause, added in 3.30. Every supported Python release ships a newer one, but check once at the start of the course so a student on an old machine finds out now rather than in week nine.

print(sqlite3.sqlite_version) # must be 3.30.0 or higher

Offline datasets

Nothing in this chapter downloads anything. Projects 1 and 2 read school.db. Projects 3 and 4 build shop.db with the generator in section 24.5, which depends only on numpy. If your classroom has no internet at all, this chapter still runs end to end.

24.3 Project 1: Course revenue & enrolment analysis

The business question

What you will practise

• SQL: LEFT JOIN (so the zero-enrolment course survives), COUNT(expr) versus COUNT(*), SUM with CASE WHEN, GROUP BY, ORDER BY, ROUND, and forcing float division with * 1.0.
• pandas: astype, computing a share of total, groupby().agg(), pivot_table.
• seaborn: barplot with hue= and palette=, countplot, heatmap with annotations.

Step 1 — get the data (SQL)

One row per course, whether or not anybody enrolled in it.

SQL_REVENUE = """ SELECT c.course_id, c.title, c.category, c.credits, c.price, COUNT(e.enrollment_id) AS n_enrolments, -- COUNT of a column ignores NULLs, so the unmatched -- LEFT JOIN row for course 6 correctly counts as 0. -- COUNT(*) would have returned 1 here. This is the trap. COUNT(e.enrollment_id) * c.price AS gross_revenue, -- every enrolment is billed at the course list price SUM(CASE WHEN e.status = 'dropped' THEN 0 ELSE c.price END) FILTER (WHERE e.enrollment_id IS NOT NULL) AS net_revenue, -- dropped students are refunded, so they contribute 0. -- FILTER excludes the padded LEFT JOIN row for course 6 -- -- without it, e.status is NULL there, the CASE falls through -- to ELSE, and a course with no students earns a full price. -- With FILTER, SUM aggregates zero rows and returns NULL, -- which we repair deliberately in pandas. ROUND(c.price * 1.0 / c.credits, 2) AS price_per_credit -- * 1.0 forces real division; without it SQLite does -- integer division and 1200/4 style results get truncated FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.course_id -- LEFT keeps all 6 courses. A plain JOIN would silently return 5. GROUP BY c.course_id, c.title, c.category, c.credits, c.price ORDER BY gross_revenue DESC; """

rev = q(SQL_REVENUE)          # the query above, already assigned
print(rev.shape)              # expect (12, 9) — twelve courses, not eleven
rev

The second query gives the two-way breakdown of enrolment status by category.

SQL_STATUS = """ SELECT c.category, e.status, COUNT(*) AS n FROM enrollments AS e JOIN courses AS c ON c.course_id = e.course_id -- an INNER join is correct here: we are counting enrolments, -- and a course with none contributes nothing to a status count GROUP BY c.category, e.status ORDER BY c.category, e.status; """

status = q(SQL_STATUS)
print(status["n"].sum())      # must equal 1303 — the total enrolment count

Step 2 — shape it (pandas / numpy)

`rev.info() # always. every time.

SUM over zero matched rows returns NULL -> NaN in pandas.

For course 6 the honest value is 0, so fill it explicitly.

rev["net_revenue"] = rev["net_revenue"].fillna(0).astype(float) rev["gross_revenue"] = rev["gross_revenue"].astype(float)

share of total revenue, as a percentage

total = rev["gross_revenue"].sum() rev["revenue_share_pct"] = np.round(100 * rev["gross_revenue"] / total, 1)

average revenue actually collected per enrolment

rev["rev_per_enrolment"] = np.where( rev["n_enrolments"] > 0, rev["net_revenue"] / rev["n_enrolments"], np.nan, # not 0 — it is undefined, not zero )

category roll-up

by_cat = ( rev.groupby("category", as_index=False) .agg(courses=("course_id", "count"), enrolments=("n_enrolments", "sum"), gross=("gross_revenue", "sum"), net=("net_revenue", "sum")) .sort_values("gross", ascending=False) )

wide table for the heatmap: rows = category, columns = status

status_wide = status.pivot_table(index="category", columns="status", values="n", aggfunc="sum", fill_value=0)`

Step 3 — explore & visualise (seaborn)

Chart 1 — revenue by course. A horizontal barplot, sorted, coloured by category.

fig, ax = plt.subplots(figsize=(9, 5))
sns.barplot(
    data=rev.sort_values("gross_revenue", ascending=False),
    x="gross_revenue", y="title",
    hue="category", palette="deep", dodge=False,   # hue is required with palette
    ax=ax,
)
ax.set_xlabel("Gross revenue")
ax.set_ylabel("")
ax.set_title("Revenue by course")
ax.legend(title="Category", loc="lower right")
plt.tight_layout()

What to look for: the length of the bars is revenue, but the interesting thing is the bar at the bottom. Deep Learning must appear with a bar of length zero. If it is missing from the chart entirely, the student used an inner join somewhere and has just made an unsellable course invisible to management.

Chart 2 — enrolment status by category.

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.heatmap(status_wide, annot=True, fmt="d", cmap="Blues",
            linewidths=.5, cbar_kws={"label": "enrolments"}, ax=ax)
ax.set_title("Enrolment status by category")
ax.set_xlabel("Status")
ax.set_ylabel("Category")
plt.tight_layout()

What to look for: whether failures and drops are spread evenly or concentrated in one category. With twenty enrolments the cells are small numbers, so read them as a pattern, not as a statistic.

The same information as a countplot, which is often easier for a beginner to read:

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.countplot(
    data=q("""SELECT c.category, e.status
              FROM enrollments e JOIN courses c ON c.course_id = e.course_id"""),
    x="category", hue="status", palette="deep", ax=ax,
)
ax.set_title("Enrolment status by category")
plt.tight_layout()

Chart 3 — price per credit. Is our pricing internally consistent?

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.barplot(data=rev, x="price_per_credit", y="title",
            hue="category", palette="deep", dodge=False,
            errorbar=None, ax=ax)          # errorbar=None, not ci=None (removed in 0.13)
ax.set_xlabel("Price per credit")
ax.set_ylabel("")
ax.set_title("Is pricing consistent across the catalogue?")
plt.tight_layout()

What to look for: a ragged profile means credits and price were set independently of each other. That is a pricing-policy finding, not a data problem, and it is exactly the sort of thing a stakeholder did not know they wanted to hear.

Step 4 — the finding

Written the way it should be handed to a head of department:

• All twenty enrolments in the current catalogue fall into five of our six courses. Deep Learning has attracted no enrolments at all, so it contributes zero revenue while still occupying a slot in the catalogue and, presumably, a teacher's preparation time.
• Gross revenue is concentrated: because revenue is enrolments multiplied by price, the courses that combine a high price with above-average take-up dominate the total. Read the exact ranking off the barplot and quote the top two courses' combined revenue_share_pct.
• Gross and net revenue differ only by the dropped enrolments. Compare the two columns: the gap is the direct financial cost of drop-out, and it is a number the department can act on.
• Price per credit is not uniform across the catalogue, which means "expensive" courses are not always expensive per unit of teaching delivered.

Gotchas in this project

Extension tasks

1. Add a column completion_rate per course: the proportion of that course's enrolments with status = 'completed'. Take care that a course with zero enrolments yields NULL, not a division-by-zero error, and decide how to display it.
2. Revenue is currently list price times enrolments. Rewrite the query so that failed enrolments are billed at half price and dropped at zero, using a single CASE expression, then re-draw Chart 1 and describe how the ranking changes.
3. Produce a category-level summary table with total revenue, total enrolments, and average price per credit, and draw it as a barplot with the categories ordered by revenue. Which category has the highest revenue per course offered rather than in total?
4. Enrolment 19 has a NULL teacher_id. Write a query that reports revenue per teacher and makes the unassigned enrolment visible as its own row labelled "Unassigned" rather than dropping it. Which join type do you need, and where does COALESCE go?

24.4 Project 2: Student performance report

The business question

What you will practise

• SQL: multi-table joins, AVG and its NULL behaviour, COUNT(grade) versus COUNT(*), AVG(CASE WHEN ...) as a rate, string concatenation with ||, COALESCE, and window functions — RANK() OVER (PARTITION BY ... ORDER BY ...) from Chapter 7.
• pandas: pivot_table, isna() auditing, ordered categoricals, merge.
• seaborn: boxplot, histplot with kde, annotated heatmap, barplot with errorbar=, and stripplot overlaid on a boxplot.

Step 1 — get the data (SQL)

Start at the finest grain available: one row per enrolment, with names attached.

SQL_MARKS = """ SELECT e.enrollment_id, s.student_id, s.first_name || ' ' || s.last_name AS student, -- || is SQLite's concatenation operator, not + COALESCE(s.city, 'Unknown') AS city, -- student 7 (Sevara) has city = NULL; label it instead of losing it c.course_id, c.title AS course, c.category, COALESCE(t.full_name, 'Unassigned') AS teacher, -- enrolment 19 has teacher_id = NULL e.status, e.grade -- 6 of the 20 grades are NULL: active students, and drop-outs FROM enrollments AS e JOIN students AS s ON s.student_id = e.student_id -- INNER is safe: every enrolment has a valid student_id JOIN courses AS c ON c.course_id = e.course_id -- INNER is safe here too, but note it hides course 6 (no enrolments) LEFT JOIN teachers AS t ON t.teacher_id = e.teacher_id -- LEFT is REQUIRED: an INNER join here would delete enrolment 19 ORDER BY c.course_id, e.grade DESC; """

marks = q(SQL_MARKS)
print(marks.shape)                 # expect (1303, 10) — all 1303 enrolments survive
print(marks["grade"].isna().sum()) # expect 423 NULL grades

Now the per-course summary, computed in SQL so the class sees how NULLs behave in aggregates:

SQL_COURSE_SUMMARY = """ SELECT c.course_id, c.title AS course, COUNT(*) AS n_enrolments, COUNT(e.grade) AS n_graded, -- differs from COUNT(*) exactly by the number of NULL grades ROUND(AVG(e.grade), 1) AS avg_grade, -- AVG silently SKIPS NULLs: it divides by n_graded, not n_enrolments ROUND(MIN(e.grade), 1) AS min_grade, ROUND(MAX(e.grade), 1) AS max_grade, ROUND(100.0 * AVG(CASE WHEN e.grade >= 60 THEN 1.0 ELSE 0.0 END), 1) AS pass_rate_pct -- AVG of a 1/0 flag is a proportion. The CASE returns NULL for -- NULL grades only if we let it — here NULL >= 60 is NULL, so -- CASE falls through to ELSE 0.0 and ungraded rows count as fails. -- Decide deliberately whether that is what you want (see gotchas). FROM enrollments AS e JOIN courses AS c ON c.course_id = e.course_id GROUP BY c.course_id, c.title HAVING COUNT(e.grade) > 0 -- HAVING filters AFTER aggregation; WHERE could not do this ORDER BY avg_grade DESC; """

And the window-function query: the top-graded student in each course.

WITH ranked AS (
    SELECT
        c.title                              AS course,
        s.first_name || ' ' || s.last_name    AS student,
        e.grade,
        RANK() OVER (
            PARTITION BY e.course_id          -- restart the ranking per course
            ORDER BY e.grade DESC             -- highest grade gets rank 1
        ) AS rnk
    FROM enrollments AS e
    JOIN students AS s ON s.student_id = e.student_id
    JOIN courses  AS c ON c.course_id  = e.course_id
    WHERE e.grade IS NOT NULL
        -- filter BEFORE ranking, or NULLs get a rank of their own
)
SELECT course, student, grade, rnk
FROM ranked
WHERE rnk = 1                -- ties both keep rank 1, so a course can have two winners
ORDER BY course;

… first 15 of 24 rows

24 rows

3 ms

Step 2 — shape it (pandas / numpy)

marks.info()
audit = marks.isna().sum()
print(audit[audit > 0]) # exactly which columns carry missing values

# grade arrives as float64 precisely BECAUSE of the NULLs.
# An int64 column cannot hold NaN. Keep it float, or use the
# nullable integer dtype if integer semantics matter:
marks["grade"] = marks["grade"].astype("Float64")

# derived columns
marks["passed"] = marks["grade"] >= 60 # pd.NA where grade is NA
marks["is_graded"] = marks["grade"].notna()

# letter bands, with an ORDERED categorical so charts sort correctly
bands = ["F", "D", "C", "B", "A"]
marks["band"] = pd.cut(
 marks["grade"].astype(float),
 bins=[0, 60, 70, 80, 90, 100],
 labels=bands,
 right=False, # [0,60) is F, 60 is D
)
marks["band"] = marks["band"].cat.set_categories(bands, ordered=True)

# graded subset — every average and every boxplot uses this, not 

marks

graded = marks[marks["is_graded"]].copy()
print(len(graded)) # 20 - 6 = 14 graded enrolments

# student x course grade matrix for the heatmap
grid = graded.pivot_table(index="student", columns="course",
 values="grade", aggfunc="mean")

# per-student summary
per_student = (
 graded.groupby("student", as_index=False)
 .agg(n_courses=("course_id", "nunique"),
 avg_grade=("grade", "mean"),
 n_passed=("passed", "sum"))
 .sort_values("avg_grade", ascending=False)
)

Step 3 — explore & visualise (seaborn)

Chart 1 — grade distribution by course. A boxplot with the individual points drawn on top, because with fourteen graded rows a box alone is misleading.

fig, ax = plt.subplots(figsize=(9, 5))
sns.boxplot(data=graded, x="course", y="grade",
            hue="course", palette="deep", legend=False,
            width=.55, showfliers=False, ax=ax)
sns.stripplot(data=graded, x="course", y="grade",
              color="black", size=6, jitter=.15, alpha=.8, ax=ax)
ax.axhline(60, ls="--", lw=1, color="grey")
ax.text(ax.get_xlim()[1], 60.5, " pass mark", va="bottom", ha="right",
        fontsize=9, color="grey")
ax.set_title("Grade distribution by course (graded enrolments only)")
ax.tick_params(axis="x", rotation=20)
plt.tight_layout()

What to look for: which boxes sit mostly below the dashed pass line, and how many black dots each box is actually built from. A "box" drawn from two points is a decoration, not a distribution — say so out loud.

Chart 2 — the overall grade distribution.

fig, ax = plt.subplots(figsize=(8, 4.5))
sns.histplot(data=graded, x="grade", bins=np.arange(40, 105, 5),
             kde=True, color="#4C72B0", ax=ax)
ax.axvline(graded["grade"].astype(float).mean(), color="crimson", ls="--",
           label=f"mean = {graded['grade'].astype(float).mean():.1f}")
ax.legend()
ax.set_title("Distribution of all graded results")
plt.tight_layout()

What to look for: is the shape single-peaked, or is there a cluster near the bottom separate from the main body? A bimodal grade distribution usually means two different populations of student sitting the same assessment.

Chart 3 — the student × course matrix.

fig, ax = plt.subplots(figsize=(9, 6))
sns.heatmap(grid.astype("float64"), annot=True, fmt=".0f", cmap="RdYlGn",
            vmin=40, vmax=100, linewidths=.6,
            cbar_kws={"label": "grade"}, ax=ax)
ax.set_title("Grade by student and course (blank = not taken / not yet graded)")
ax.set_xlabel("")
ax.set_ylabel("")
plt.tight_layout()

What to look for: read it twice — once along the rows (a student weak everywhere versus weak in one subject) and once down the columns (a course where everybody struggles). The two readings lead to completely different interventions.

Chart 4 — pass rate by course.

rates = q(SQL_COURSE_SUMMARY)          # the second query from Step 1

fig, ax = plt.subplots(figsize=(9, 4.5))
sns.barplot(data=rates.sort_values("pass_rate_pct", ascending=False),
            x="pass_rate_pct", y="course",
            hue="course", palette="deep", legend=False,
            errorbar=None, ax=ax)      # errorbar=None: these are already aggregates
for i, row in enumerate(rates.sort_values("pass_rate_pct", ascending=False)
                             .itertuples()):
    ax.text(row.pass_rate_pct + 1, i, f"n={row.n_graded}", va="center", fontsize=9)
ax.set_xlim(0, 110)
ax.set_xlabel("Pass rate (%)")
ax.set_ylabel("")
ax.set_title("Pass rate by course")
plt.tight_layout()

What to look for: the n= annotation is the most important element on the chart. A 100% pass rate from one graded student and a 100% pass rate from forty are not the same claim, and a bar chart makes them look identical unless you print the denominator.

Step 4 — the finding

• Only 14 of the 20 enrolments carry a grade. The other six are active or dropped students. Every average in this report therefore describes 70% of enrolments, and the report must say so.
• Because AVG divides by the number of non-NULL grades, the per-course averages are computed on very small denominators. Report n_graded beside every average; a course average from two grades is an anecdote.
• The RANK() query names the top student in each course. Where two students share the top grade, both receive rank 1 and both appear — which is the correct behaviour for a prize list and the reason to prefer RANK over ROW_NUMBER here.
• The heatmap shows coverage as well as performance: no student has taken every course, so cross-course comparisons of students are not like-for-like.

Gotchas in this project

Extension tasks

1. Rewrite the pass-rate query using the "graded rows only" definition, put both versions side by side in one DataFrame, and draw a grouped barplot comparing them. Explain in two sentences which one you would put in a report to parents and why.
2. Use ROW_NUMBER() instead of RANK() in the top-student query. Find a course where the two give different answers, and explain what happened.
3. Add a per-student rank across the whole school using DENSE_RANK() OVER (ORDER BY avg_grade DESC) computed from a CTE of student averages. Which students change position if you rank on median instead of mean?
4. Grades are missing for active and dropped enrolments. Draw a chart comparing the number of missing grades by status and by category, and argue whether the missingness looks random or systematic.

24.5 Project 3: Sales time series & seasonality

school.db has twenty enrolment rows. You cannot see seasonality in twenty rows. So this project — and project 4 — build their own database first.

Building shop.db (run once)

The generator below creates three years of daily sales across five product categories. Each series is a trend, multiplied by a yearly seasonal wave, a weekly effect, a December uplift, and multiplicative noise. Because the structure is put in deliberately, students can check whether their analysis recovers what they know is there — which is the single best way to learn time-series analysis.

SEED = 7
rng = np.random.default_rng(SEED)      # modern numpy 2 generator API

dates = pd.date_range("2022-01-01", "2024-12-31", freq="D")
n     = len(dates)                      # 1096 days (2024 is a leap year)
t     = np.arange(n)                    # 0,1,2,... — the linear trend index
doy   = dates.dayofyear.to_numpy()
dow   = dates.dayofweek.to_numpy()      # Monday = 0 ... Sunday = 6
month = dates.month.to_numpy()

# base level, daily growth, yearly amplitude, weekend uplift
spec = {
    "Electronics": dict(base=1800, growth=1.10, yearly=0.30, weekend=0.15),
    "Grocery":     dict(base=2600, growth=0.35, yearly=0.08, weekend=0.30),
    "Clothing":    dict(base=1200, growth=0.60, yearly=0.45, weekend=0.25),
    "Home":        dict(base= 900, growth=0.25, yearly=0.20, weekend=0.10),
    "Toys":        dict(base= 600, growth=0.15, yearly=0.55, weekend=0.35),
}

frames = []
for name, p in spec.items():
    trend  = p["base"] + p["growth"] * t
    yearly = 1 + p["yearly"] * np.sin(2 * np.pi * (doy - 80) / 365.25)
        # peak around day 171 (late June); shift -80 moves the peak
    weekly = 1 + p["weekend"] * (dow >= 5)      # bool array -> 0/1 arithmetic
    xmas   = 1 + 0.45 * (month == 12)          # December uplift
    noise  = rng.normal(1.0, 0.08, n)          # multiplicative, 8% sd

    revenue = np.maximum(trend * yearly * weekly * xmas * noise, 0).round(2)
    units   = rng.poisson(revenue / 40).astype(int)

    frames.append(pd.DataFrame({
        "sale_date": dates.strftime("%Y-%m-%d"),   # ISO text: SQLite's date format
        "category":  name,
        "units":     units,
        "revenue":   revenue,
    }))

sales = pd.concat(frames, ignore_index=True)
print(sales.shape)      # (5480, 4) = 1096 days x 5 categories

conn = sqlite3.connect(DB_SHOP)
sales.to_sql("sales", conn, if_exists="replace", index=False)
conn.execute("CREATE INDEX IF NOT EXISTS ix_sales_date ON sales(sale_date)")
conn.commit()
conn.close()

(5480, 4)

The business question

What you will practise

• SQL: strftime for date parts, GROUP BY on a derived column, SUM, ORDER BY on dates, and the window function LAG (Chapter 7) for month-over-month change.
• pandas: to_datetime, set_index, rolling, pct_change, pivot_table, groupby on a PeriodIndex.
• numpy: sinusoidal seasonal components, boolean-to-numeric arithmetic, np.maximum.
• seaborn: lineplot with hue, a rolling-mean overlay, month × year heatmap, barplot of change with a diverging palette.

Step 1 — get the data (SQL)

SQL_MONTHLY = """ SELECT strftime('%Y-%m', s.sale_date) AS ym, -- '2024-03'. Text, but it sorts chronologically, which is why -- ISO dates matter. '%Y' alone gives the year, '%m' the month. s.category, SUM(s.revenue) AS revenue, SUM(s.units) AS units, COUNT(*) AS n_days -- sanity column: 28-31 for a complete month, less if data is missing FROM sales AS s WHERE s.sale_date >= '2022-01-01' AND s.sale_date <= '2024-12-31' -- string comparison works because the format is ISO-8601 GROUP BY ym, s.category ORDER BY ym, s.category; """

m = q(SQL_MONTHLY, db=DB_SHOP)
print(m.shape)                    # (180, 5) = 36 months x 5 categories
print(m["n_days"].unique())       # should be only 28, 29, 30, 31

The month-over-month change can be done entirely in SQL with LAG, which is worth showing so students see the SQL-native route before reaching for pandas:

WITH monthly AS (
    SELECT strftime('%Y-%m', sale_date) AS ym,
           category,
           SUM(revenue)                 AS revenue
    FROM sales
    GROUP BY ym, category
)
SELECT
    ym,
    category,
    revenue,
    LAG(revenue) OVER (PARTITION BY category ORDER BY ym) AS prev_revenue,
        -- LAG looks one row BACK within each category's own timeline.
        -- The first month of each category gets NULL — there is no previous.
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY ym))
              / LAG(revenue) OVER (PARTITION BY category ORDER BY ym), 1
    ) AS mom_pct
        -- 100.0 (not 100) to force real division
FROM monthly
ORDER BY category, ym;

Step 2 — shape it (pandas / numpy)

m["ym"] = pd.to_datetime(m["ym"] + "-01")     # text -> real timestamps
m["year"]  = m["ym"].dt.year
m["month"] = m["ym"].dt.month
m["month_name"] = m["ym"].dt.strftime("%b")
m["month_name"] = pd.Categorical(
    m["month_name"],
    categories=["Jan","Feb","Mar","Apr","May","Jun",
                "Jul","Aug","Sep","Oct","Nov","Dec"],
    ordered=True,                              # or charts sort Apr, Aug, Dec...
)

# month-over-month change, the pandas way (compare with the LAG query)
m = m.sort_values(["category", "ym"])
m["mom_pct"] = m.groupby("category")["revenue"].pct_change() * 100
    # groupby before pct_change is the pandas equivalent of PARTITION BY

# total across all categories, for the headline series
total = (m.groupby("ym", as_index=False)["revenue"].sum()
           .sort_values("ym"))
total["roll3"]  = total["revenue"].rolling(3,  min_periods=3).mean()
total["roll12"] = total["revenue"].rolling(12, min_periods=12).mean()
    # a 12-month rolling mean removes seasonality entirely,
    # leaving the trend. This is the whole point of the chart.

# year x month matrix of total revenue
grid = m.pivot_table(index="month_name", columns="year",
                     values="revenue", aggfunc="sum", observed=True)

Step 3 — explore & visualise (seaborn)

Chart 1 — the headline series with a rolling mean.

fig, ax = plt.subplots(figsize=(11, 5))
sns.lineplot(data=total, x="ym", y="revenue",
             color="#9aa5b1", lw=1.2, label="monthly revenue", ax=ax)
sns.lineplot(data=total, x="ym", y="roll12",
             color="#C44E52", lw=2.5, label="12-month rolling mean", ax=ax)
ax.set_xlabel("")
ax.set_ylabel("Revenue")
ax.set_title("Total revenue: raw monthly series and de-seasonalised trend")
plt.tight_layout()

What to look for: the grey line wobbles, the red line does not. If the red line rises steadily, growth is real; if it is flat while the grey line has big peaks, then what looks like growth is only seasonality repeating. This one chart answers the stakeholder's question.

Chart 2 — per-category series.

fig, ax = plt.subplots(figsize=(11, 5))
sns.lineplot(data=m, x="ym", y="revenue", hue="category",
             palette="deep", lw=1.6, ax=ax)
ax.set_title("Monthly revenue by category")
ax.set_xlabel("")
ax.legend(title="Category", ncol=5, loc="upper left", fontsize=9)
plt.tight_layout()

What to look for: the amplitude of each line relative to its own level. Toys should swing much more than Grocery — that is the yearly parameter in the generator, recovered from the data.

Chart 3 — month × year heatmap.

fig, ax = plt.subplots(figsize=(7, 6))
sns.heatmap(grid.astype("float64"), annot=True, fmt=".0f", cmap="YlOrRd",
            linewidths=.5, cbar_kws={"label": "revenue"}, ax=ax)
ax.set_title("Revenue by month and year")
ax.set_xlabel("Year")
ax.set_ylabel("")
plt.tight_layout()

What to look for: read across a row to see growth for that month year on year; read down a column to see the seasonal shape within a year. A consistently dark row is a reliably strong month — which is what a stock planner actually needs.

Chart 4 — month-over-month change.

recent = m[(m["category"] == "Toys") & (m["year"] == 2024)].copy()

fig, ax = plt.subplots(figsize=(10, 4.5))
sns.barplot(data=recent, x="month_name", y="mom_pct",
            hue="mom_pct", palette="vlag", legend=False,
            errorbar=None, ax=ax)
ax.axhline(0, color="black", lw=1)
ax.set_xlabel("")
ax.set_ylabel("Change vs previous month (%)")
ax.set_title("Toys: month-over-month revenue change, 2024")
plt.tight_layout()

What to look for: alternating positive and negative bars are normal seasonality, not instability. The December bar and the January bar are the pair to watch — a large December rise followed by a large January fall is one event, not two.

Step 4 — the finding

• Growth is real, and it is separate from seasonality. The 12-month rolling mean rises across the whole window, which cannot be produced by a repeating seasonal pattern. Quote the rolling-mean value at the start and end of the series.
• Seasonality differs sharply by category. Grocery is close to flat through the year; Toys and Clothing swing hardest. Stock planning should therefore be category-specific, not shop-wide.
• December is an event, not a trend. Every category shows a December uplift followed by a January fall. Month-over-month percentages in January will look alarming and mean nothing; compare January with the previous January instead.
• There is a weekly pattern underneath all of this that monthly aggregation hides completely — weekends run higher than weekdays. Aggregating to months is a decision that destroys information, and it should be a conscious one.

Gotchas in this project

Extension tasks

1. Add a year-over-year column using LAG(revenue, 12) OVER (PARTITION BY category ORDER BY ym), and draw a lineplot of YoY growth per category. Which category is growing fastest in percentage terms, and is that the same as the one growing fastest in absolute terms?
2. Go back to daily data and draw a boxplot of revenue by day of week for each category. Recover the weekend parameter from the generator: which category has the strongest weekend effect?
3. Decompose one category into trend, seasonal and residual components by hand: trend = 12-month centred rolling mean; seasonal = the average ratio of actual to trend for each calendar month; residual = whatever is left. Plot all three.
4. Re-run the generator with a different SEED and confirm the trend and seasonality survive while the month-to-month wiggles change. Explain in two sentences which parts of your findings were signal and which were noise.

24.6 Project 4: Customer segmentation with RFM

The business question

RFM is the oldest and most durable answer to that question. Score each customer on three axes:

Extending shop.db (run once, after 24.5)

rng = np.random.default_rng(SEED + 1)      # different stream, same reproducibility

N_CUST   = 1200
END_DATE = np.datetime64("2024-12-31")

cust_id = np.arange(1, N_CUST + 1)
cities  = rng.choice(["Tashkent", "Samarkand", "Bukhara", "Namangan", "Andijan"],
                     size=N_CUST, p=[.40, .20, .15, .15, .10])

# signup spread over the first ~2.5 years of the window
signup = np.datetime64("2022-01-01") + rng.integers(0, 900, N_CUST).astype("timedelta64[D]")

# latent loyalty -> number of transactions. Gamma gives a realistic
# right-skewed shape: many light buyers, a few very heavy ones.
loyalty = rng.gamma(shape=2.0, scale=1.6, size=N_CUST)
n_txn   = rng.poisson(loyalty * 2.0) + 1        # everybody buys at least once

# how long each customer stays active after signing up
active_days = 60 + rng.integers(0, 900, N_CUST)

# expand to one row per transaction (vectorised — no Python loop)
cust_rep   = np.repeat(cust_id, n_txn)
signup_rep = np.repeat(signup, n_txn)
span_rep   = np.repeat(active_days, n_txn)

# **1.3 skews offsets toward the START of the active window, so a
# realistic share of customers lapse rather than all being current
offset   = (rng.random(cust_rep.size) ** 1.3 * span_rep).astype(int)
txn_date = signup_rep + offset.astype("timedelta64[D]")

# basket value: lognormal is the standard shape for spend
amount = np.round(rng.lognormal(mean=3.4, sigma=0.6, size=cust_rep.size), 2)

keep = txn_date <= END_DATE                     # drop anything past the window

txns = pd.DataFrame({
    "customer_id": cust_rep[keep],
    "txn_date":    pd.to_datetime(txn_date[keep]).strftime("%Y-%m-%d"),
    "amount":      amount[keep],
})
custs = pd.DataFrame({
    "customer_id": cust_id,
    "city":        cities,
    "signup_date": pd.to_datetime(signup).strftime("%Y-%m-%d"),
})

print(len(custs), len(txns))                    # ~1200 customers, ~8-9k transactions

conn = sqlite3.connect(DB_SHOP)
custs.to_sql("customers",    conn, if_exists="replace", index=False)
txns .to_sql("transactions", conn, if_exists="replace", index=False)
conn.execute("CREATE INDEX IF NOT EXISTS ix_txn_cust ON transactions(customer_id)")
conn.commit()
conn.close()

1200 8059

What you will practise

• SQL: CTEs (WITH), julianday date arithmetic, MAX/COUNT/SUM per group, NTILE(5) window scoring (Chapter 7), a multi-branch CASE for labelling, and LEFT JOIN so customers with no transactions are not silently deleted.
• pandas: ordered categoricals, crosstab, merge, quantile inspection.
• seaborn: scatterplot with hue and log scales, heatmap of a crosstab, barplot of revenue per segment.

Step 1 — get the data (SQL)

SQL_RFM = """ WITH base AS ( SELECT c.customer_id, c.city, MAX(t.txn_date) AS last_txn, COUNT(t.txn_date) AS frequency, -- COUNT of a column, not COUNT(*): a customer with no -- transactions must score 0, not 1 (same trap as Project 1) COALESCE(SUM(t.amount), 0) AS monetary, CAST(julianday('2024-12-31') - julianday(MAX(t.txn_date)) AS INTEGER) AS recency_days -- julianday() converts an ISO date to a day number, so the -- difference is a plain number of days. Anchor on the last day -- of the data, NEVER on today: 'now' makes results change daily. FROM customers AS c LEFT JOIN transactions AS t ON t.customer_id = c.customer_id -- LEFT keeps customers who never bought; their frequency is 0 -- and their recency is NULL, which we must handle downstream GROUP BY c.customer_id, c.city ), active AS ( SELECT * FROM base WHERE frequency > 0 -- NTILE on a NULL recency would bucket non-buyers arbitrarily, -- so score only customers who have actually transacted ), scored AS ( SELECT a.*, NTILE(5) OVER (ORDER BY a.recency_days DESC) AS r_score, -- DESC: the LARGEST recency (worst customer) lands in tile 1, -- so tile 5 = most recently seen = best. Get this backwards -- and every conclusion in the project inverts. NTILE(5) OVER (ORDER BY a.frequency ASC) AS f_score, NTILE(5) OVER (ORDER BY a.monetary ASC) AS m_score -- ASC: highest frequency / spend lands in tile 5 = best FROM active AS a ) SELECT s.*, s.r_score + s.f_score + s.m_score AS rfm_total, CASE WHEN s.r_score >= 4 AND s.f_score >= 4 AND s.m_score >= 4 THEN 'Champions' WHEN s.r_score >= 4 AND s.f_score >= 3 THEN 'Loyal' WHEN s.r_score >= 4 AND s.f_score <= 2 THEN 'New / Promising' WHEN s.r_score = 3 THEN 'Needs attention' WHEN s.r_score <= 2 AND s.m_score >= 4 THEN 'At risk (high value)' ELSE 'Lapsed' END AS segment -- CASE stops at the FIRST matching branch, so order matters: -- the most specific rule must come first FROM scored AS s ORDER BY rfm_total DESC, s.monetary DESC; -- ORDER BY may reference an output alias, but NEVER a qualified one: --s.rfm_totalfails with "no such column" because the alias -- belongs to the result, not to the table s """

rfm = q(SQL_RFM, db=DB_SHOP)
print(rfm.shape)
print(rfm["segment"].value_counts())
print(rfm[["recency_days", "frequency", "monetary"]].describe())

Step 2 — shape it (pandas / numpy)

rfm.info()

seg_order = ["Champions", "Loyal", "New / Promising", "Needs attention",
 "At risk (high value)", "Lapsed"]
rfm["segment"] = pd.Categorical(rfm["segment"], categories=seg_order, ordered=True)
 # fixes the order in every chart and every value_counts, permanently

rfm["last_txn"] = pd.to_datetime(rfm["last_txn"])
rfm["avg_order_value"] = rfm["monetary"] / rfm["frequency"]
 # safe: the 

active

 CTE guaranteed frequency >= 1

# segment-level summary — the table the stakeholder actually reads
seg = (
 rfm.groupby("segment", as_index=False, observed=True)
 .agg(customers=("customer_id", "count"),
 revenue=("monetary", "sum"),
 avg_freq=("frequency", "mean"),
 avg_recency=("recency_days", "mean"),
 avg_order=("avg_order_value", "mean"))
)
seg["revenue_share_pct"] = np.round(100 * seg["revenue"] / seg["revenue"].sum(), 1)
seg["cust_share_pct"] = np.round(100 * seg["customers"] / seg["customers"].sum(), 1)

# R x F count matrix
rf = pd.crosstab(rfm["r_score"], rfm["f_score"])

# do the three axes actually measure different things?
print(rfm[["recency_days", "frequency", "monetary",
 "avg_order_value"]].corr(numeric_only=True).round(2))

Step 3 — explore & visualise (seaborn)

Chart 1 — frequency versus monetary, coloured by segment.

fig, ax = plt.subplots(figsize=(9, 6))
sns.scatterplot(data=rfm, x="frequency", y="monetary",
                hue="segment", palette="deep",
                size="r_score", sizes=(20, 120),
                alpha=.75, edgecolor="none", ax=ax)
ax.set_yscale("log")            # spend is lognormal — a linear axis wastes 90% of the plot
ax.set_xlabel("Number of purchases")
ax.set_ylabel("Total spend (log scale)")
ax.set_title("Customer base: frequency vs value")
ax.legend(bbox_to_anchor=(1.02, 1), loc="upper left", fontsize=8)
plt.tight_layout()

What to look for: the diagonal band is the mechanical relationship (buying more costs more). The interesting customers are off the diagonal: few purchases but very high spend, or many purchases at trivial value. Point size encodes recency, so large points high and right are the champions.

Chart 2 — the R × F grid.

fig, ax = plt.subplots(figsize=(7, 5.5))
sns.heatmap(rf, annot=True, fmt="d", cmap="Purples",
            linewidths=.5, cbar_kws={"label": "customers"}, ax=ax)
ax.set_xlabel("Frequency score (5 = most frequent)")
ax.set_ylabel("Recency score (5 = most recent)")
ax.set_title("How many customers sit in each R x F cell?")
ax.invert_yaxis()               # put the best recency at the top, where readers expect it
plt.tight_layout()

What to look for: the two corners. Top-right (high R, high F) is the loyal core; bottom-right (low R, high F) is the alarm bell — customers who used to buy often and have stopped. That cell is where a retention budget belongs.

Chart 3 — revenue by segment.

fig, ax = plt.subplots(figsize=(9, 5))
sns.barplot(data=seg, x="revenue", y="segment",
            hue="segment", palette="deep", legend=False,
            errorbar=None, ax=ax)
for i, row in enumerate(seg.itertuples()):
    ax.text(row.revenue, i,
            f"  {row.revenue_share_pct}% of revenue / {row.cust_share_pct}% of customers",
            va="center", fontsize=9)
ax.set_xlim(0, seg["revenue"].max() * 1.55)
ax.set_xlabel("Total revenue")
ax.set_ylabel("")
ax.set_title("Revenue and customer share by segment")
plt.tight_layout()

What to look for: the gap between the two percentages on each bar. A segment holding 12% of customers and 35% of revenue is the one to protect; the reverse pattern is the one to leave alone.

Step 4 — the finding

• Revenue is concentrated. Compare revenue_share_pct with cust_share_pct across the segments: the top segments hold a share of revenue well above their share of customers. Quote both numbers together — one without the other is not a finding.
• The "At risk (high value)" segment is the campaign target. These customers have spent in the top tiers but have not been seen recently. They have proven willingness to pay and a known reason to come back; a new-customer campaign has neither.
• Frequency and monetary are strongly correlated — check the correlation matrix printed in Step 2 — so they carry overlapping information. Recency is the axis that adds something genuinely new, which is why it leads the segment rules.
• Segment sizes are a consequence of the rules, not a discovery. Because NTILE forces equal fifths on each axis, changing a single >= 4 to >= 3 in the CASE moves hundreds of customers. Report the rule alongside the result, always.

Gotchas in this project

Extension tasks

1. Add the never-purchased customers back as an explicit seventh segment. How many are there, and does their existence change any percentage you reported?
2. Replace NTILE(5) with fixed business thresholds (for example: recency under 90 days, four or more purchases, spend over the median). Compare the two segmentations with a crosstab and describe which customers changed group and why.
3. Bring in city from the customers table and draw a stacked or grouped barplot of segment composition by city. Is the champion rate uniform across cities, or is one city carrying the business?
4. Join the sales table from Project 3 to the RFM output by month and check whether the At-risk segment's decline lines up with any particular period. State clearly why this is a correlation and not a cause.

24.7 Marking rubric

Twenty-five marks per project. The weighting deliberately favours correctness and interpretation over presentation — a beautiful chart of a wrong number is worth less than a plain chart of a right one.

Grade boundaries

• 22–25 — professional. Could be sent to a stakeholder with light editing.
• 18–21 — solid. Correct analysis, communication needs work.
• 13–17 — the mechanics work but at least one trap was walked into unnoticed.
• below 13 — the pipeline runs but the numbers do not describe the question asked.

24.8 Summary

Four projects, one pipeline, repeated until it is automatic.

• Project 1 — revenue. The lesson is LEFT JOIN and COUNT(column). A course nobody bought is the most important row in the table, and an inner join deletes it.
• Project 2 — performance. The lesson is NULL semantics. AVG skips missing grades, NULL >= 60 is neither true nor false, and a rate is only interpretable next to its denominator.
• Project 3 — time series. The lesson is separating trend from season. A rolling annual mean answers "are we growing?"; a month × year heatmap answers "when should we stock?". Month-over-month answers neither.
• Project 4 — segmentation. The lesson is that a segment is a rule you wrote, not a fact you discovered. NTILE makes the rule relative, anchoring the date makes it reproducible, and reporting revenue share against customer share makes it useful.

Across all four, the same three habits appear again and again, and they are the habits worth carrying out of this guide:

The techniques in Part I and Part II were the vocabulary. These four projects were the first paragraphs written in it. The next ones should come from your own students' questions — a school's own timetable data, a local shop's till receipts, a sports league's results table. The pipeline does not change; only the question does.