Harbor Snow: build a reporting layer

TOPIC 1 OF 8

Save a definition, keep the report live

A view names a SELECT stored in the database. A CTE names a result for one statement. A table made with CREATE TABLE AS stores a snapshot. Choose by how long the definition should live and whether the results must reflect changes.

Read views.sql in the kit: guest_spend and monthly_sales depend on sales; coach_summary depends on lessons and coaches. Build base tables before views, and views before reports. A view does not freeze its input or guarantee display order.

Run this rehearsal in one desktop connection. The view reflects the inserted lesson inside the transaction; ROLLBACK restores its previous result. DROP VIEW removes a definition, not its base rows. These whole-dollar practice values simplify arithmetic; they are not a general money-storage design.

SELECT * FROM coach_summary WHERE id=3;
BEGIN;
INSERT INTO lessons VALUES (5,1,3,'2026-03-10',60);
SELECT * FROM coach_summary WHERE id=3;
ROLLBACK;
SELECT * FROM coach_summary WHERE id=3;

Pause and investigate: Predict all three view outputs before running. What would a previously exported CSV show instead?

TOPIC 2 OF 8

Keep quiet guests and anonymous sales

Write the grain first: guest_spend is one row per registered guest, monthly_sales is one row per observed sale month, and sales is one row per admission purchase. A guest with no purchases still belongs in a guest report.

Start from guests and LEFT JOIN sales. COUNT(s.id) counts matched purchases; COUNT(*) counts the placeholder row too. COALESCE changes the missing aggregate to zero. Filtering the right side in WHERE can remove the guest you intended to preserve.

Anonymous admissions have no guest owner but still earn revenue. Guest totals therefore need not equal all sales. Lessons are a separate service in this small kit and are not included in monthly_sales; the official lab combines four revenue sources with UNION ALL.

SELECT * FROM guest_spend ORDER BY id;
SELECT SUM(total) AS registered_spend FROM guest_spend;
SELECT SUM(amount) AS all_admission_sales FROM sales;

Pause and investigate: Explain any difference using an identifiable source row. Would replacing UNION ALL with UNION preserve two separate identical purchases?

TOPIC 3 OF 8

Choose what a rank means

ROW_NUMBER gives each row a unique position. RANK gives peers a shared position and leaves gaps; DENSE_RANK counts distinct ordered levels. Peer equality uses every expression in the window ORDER BY.

To preserve equal-spend ties, rank by total alone. To pick exactly N names reproducibly, use ROW_NUMBER ordered by total descending and then the unique id. Adding id to RANK breaks the spend ties instead of merely stabilizing their display.

The outer ORDER BY controls presentation. The ORDER BY inside OVER controls the calculation. PARTITION BY restarts that calculation for each city without collapsing the guests.

SELECT id,name,city,total,
 RANK() OVER (PARTITION BY city ORDER BY total DESC) AS rnk,
 DENSE_RANK() OVER (PARTITION BY city ORDER BY total DESC) AS dense,
 ROW_NUMBER() OVER (PARTITION BY city ORDER BY total DESC,id) AS position
FROM guest_spend ORDER BY city,total DESC,id;

Pause and investigate: In Harbor, predict the rank assigned to Bea and Cam. Which ordering belongs to the business rule and which makes a chosen list reproducible?

TOPIC 4 OF 8

Calculate first, filter second

Window functions operate after WHERE filters the input. A window expression cannot be placed directly in WHERE. Compute the position in a CTE or subquery, then filter that named column outside.

A request for exactly two people per city is different from every person tied in the top two rank positions. ROW_NUMBER can return at most two per city, and exactly two only when a city contains at least two guests.

WITH ordered AS (
 SELECT id,name,city,total,
 ROW_NUMBER() OVER (PARTITION BY city ORDER BY total DESC,id) AS position
 FROM guest_spend
)
SELECT city,name,total,position FROM ordered
WHERE position<=2 ORDER BY city,position;

Pause and investigate: Count results per city, not just the overall result count. Would changing ROW_NUMBER to RANK meet the same request?

TOPIC 5 OF 8

Make the running-total frame explicit

SUM(amount) OVER () repeats the grand total. An ordered cumulative window can instead show progress. On one row per unique month, the usual ordered sum is straightforward.

At transaction grain, dates can tie. The default ordered frame includes peers with the same ordering value. For one addition per sale, use sale_date plus the unique id and an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame.

An outer sort alone cannot repair an unordered window sum. Check the intermediate totals as well as the final total; several incorrect queries share the same last number.

SELECT id,sale_date,amount,
 SUM(amount) OVER (
 ORDER BY sale_date,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS running_total
FROM sales ORDER BY sale_date,id;

Pause and investigate: Compare with SUM(amount) OVER (ORDER BY sale_date). At which pairs of sales does the default frame jump?

TOPIC 6 OF 8

Previous row is not always previous month

LAG retrieves the previous row in the window order. The first row has no predecessor and normally receives NULL. Do not silently turn that unknown comparison into a change from zero.

The rehearsal has no February admissions. Its monthly view omits February, so March compares with January. Name that output previous observed month unless you explicitly build a complete calendar and establish what a missing month means.

If source completeness confirms zero sales in February, a calendar scaffold and LEFT JOIN can supply a zero month before LAG. Missing data is not automatically zero. LEAD looks in the other direction.

WITH compared AS (
 SELECT month,total,
 LAG(month) OVER (ORDER BY month) AS previous_observed_month,
 LAG(total) OVER (ORDER BY month) AS previous_total
 FROM monthly_sales
)
SELECT *,total-previous_total AS change
FROM compared ORDER BY month;

Pause and investigate: For March, identify the two actual months being compared. Why could an accurate numeric subtraction still have a misleading label?

TOPIC 7 OF 8

Compare each lesson with its coach

GROUP BY coach_id reduces lessons to coach summaries. AVG(price) OVER (PARTITION BY coach_id) retains each lesson while adding its coach-wide average.

Leave ORDER BY out of this OVER when the benchmark should cover all that coach’s lessons. Adding ORDER BY lesson_date without a full-partition frame turns it into an accumulating benchmark.

Compute an alias in a CTE when another expression needs it. In this fixture, prices are whole dollars but averages can be fractional; ROUND the displayed comparison rather than changing the source values.

WITH compared AS (
 SELECT id,coach_id,price,
 AVG(price) OVER (PARTITION BY coach_id) AS coach_avg
 FROM lessons
)
SELECT id,coach_id,price,ROUND(coach_avg,2) AS coach_avg,
 ROUND(price-coach_avg,2) AS difference
FROM compared ORDER BY coach_id,id;

Pause and investigate: Predict how many rows remain. Does the coach with no lessons belong in this lesson-level result or in coach_summary?

TOPIC 8 OF 8

Test the whole unit of work

A lesson booking and its balance debit should succeed together. In one connection, BEGIN, perform both writes, verify affected rows and balances, then COMMIT when correct or ROLLBACK when not. A zero-row UPDATE can succeed syntactically while failing the intended task.

For the default CHECK failure used in this kit, SQLite aborts the failing statement and leaves the explicit transaction open. Earlier successful statements are still pending. Inspect the error and transaction state; do not assume every error behaves identically.

Run this successful booking rehearsal on a copy. Verify the lesson, balance and live view before COMMIT, then query again afterward. Reopen a fresh copy for the team challenges. For the deliberately failing transfer, enter each statement separately so your SQL tool does not skip the observation or rollback.

PRAGMA foreign_keys=ON;
BEGIN;
INSERT INTO lessons VALUES (5,1,3,'2026-03-10',60);
UPDATE guests SET balance=balance-60 WHERE id=1;
SELECT changes() AS guests_debited;
SELECT id,balance FROM guests WHERE id=1;
SELECT * FROM coach_summary WHERE id=3;
COMMIT;
SELECT id,balance FROM guests WHERE id=1;
SELECT * FROM coach_summary WHERE id=3;

Pause and investigate: Why does changes() need to be checked immediately after the UPDATE? What evidence would tell you to roll back rather than commit?