Learn with Abdou
Open workshop →

LEARN WITH ABDOU / LAB 11 PRACTICE / VERSION 2026-09-09

Harbor Snow: build a reporting layer

Team: __________________________ Date: ______________

Lab 11 preparation with eight readings and six team investigations. Run SQL in modern desktop SQLite; keep predictions and evidence here.

Workshop and kit: learnwithabdou.com/courses/mis3220/labs/reporting-layer

Before you begin

Read the official Pre-Lab 11 and complete its Canvas check. Extract the rehearsal kit; make a copy of harbor_snow.db and open it in desktop SQLite 3.25 or newer. Check SELECT sqlite_version(); and set PRAGMA foreign_keys=ON; in your connection. Read setup.sql and views.sql. This fixture is original practice; the actual class lab uses spirit_mountain.db.

Your investigation routine

Rotate query writer, evidence checker and explainer after each question. Predict before running. Record the SQL as text, observed rows or exact errors, and your explanation. Start each question on a fresh rehearsal copy. Use questions 1–3 for 15–20 minutes or all six for 35–45 minutes.

Reading map

  1. Save a definition, keep the report live
  2. Keep quiet guests and anonymous sales
  3. Choose what a rank means
  4. Calculate first, filter second
  5. Make the running-total frame explicit
  6. Previous row is not always previous month
  7. Compare each lesson with its coach
  8. Test the whole unit of work

Window functions run in desktop SQLite. The website stores your notes; it does not execute or grade these queries. For a failing transfer, run each statement separately in the same connection and inspect the pending balances before ROLLBACK.

Success means evidence

Can you preserve a guest with no sales, explain a tie policy, verify intermediate totals, identify a missing month, retain lesson-level rows, and show what a failed transaction did?

Harbor Snow schema

Original practice data. Row counts are for a fresh kit copy. The official class database is Spirit Mountain, downloaded from Canvas.

guests · 6 rows

id INTEGER PK; name TEXT NOT NULL; city TEXT NOT NULL; balance INTEGER NOT NULL CHECK >= 0

One registered guest. Balance is prepaid whole-dollar credit.

sales · 8 rows

id INTEGER PK; guest_id INTEGER nullable FK → guests.id; sale_date TEXT NOT NULL; amount INTEGER NOT NULL CHECK >= 0

One admission purchase. NULL guest_id is anonymous. Two identical-looking purchases remain two sales. Dates use YYYY-MM-DD.

coaches · 3 rows

id INTEGER PK; name TEXT NOT NULL

One coach, including a coach with no lessons.

lessons · 4 rows

id INTEGER PK; guest_id INTEGER NOT NULL FK → guests.id; coach_id INTEGER NOT NULL FK → coaches.id; lesson_date TEXT NOT NULL; price INTEGER NOT NULL CHECK >= 0

One lesson. These fees are separate from the admission sales report.

Saved views

guest_spend (id, name, city, purchases, total)
6 rows: one per guest, based on guests LEFT JOIN sales.

monthly_sales (month, total)
4 rows: November, December, January and March. Admissions only; no February row.

coach_summary (id, name, lessons, revenue)
3 rows: one per coach, including zero lessons.

Build order: guests + coaches → sales + lessons → views → report queries. Foreign keys must be enabled in each desktop connection. SQL definitions are in setup.sql and views.sql.

1. The welcome report loses a guest

The manager needs every registered guest, including those with no purchases. Repair this query to return id, name, purchases and total ordered by id. Identify the missing guest and explain why guest totals do not account for all admission revenue.

SELECT g.id,g.name,COUNT(*) AS purchases,SUM(s.amount) AS total
FROM guests g JOIN sales s ON s.guest_id=g.id
GROUP BY g.id,g.name ORDER BY g.id;

Prediction / corrected SQL / result evidence / explanation:

2. The two-name invitation has three names

Choose exactly two guests per city for a feedback invitation. Repair the selection, retaining the output columns city, name, total and position. Count each city’s results and explain whom your tie-breaker selects. Give a different business request for which the original query would be appropriate.

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

Prediction / corrected SQL / result evidence / explanation:

3. The till total jumps before the next sale

The clerk wants one cumulative addition per admission sale. Return id, sale_date, amount and running_total in sale_date/id order. Repair the window, identify two intermediate numbers that change, and explain why the final total alone cannot validate it.

SELECT id,sale_date,amount,
 SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales ORDER BY sale_date,id;

Prediction / corrected SQL / result evidence / explanation:

4. The March headline names the wrong month

Run the query. For March, state which month LAG actually reads. Return month, total, previous_observed_month, previous_total and change with honest labels. Outline how you would compare consecutive calendar months if February was confirmed to have zero sales. Explain why filling unknown months with zero is risky.

SELECT month,total,LAG(total) OVER (ORDER BY month) AS previous_month
FROM monthly_sales ORDER BY month;

Prediction / corrected SQL / result evidence / explanation:

5. The benchmark changes while the coach teaches

Return each lesson id, coach_id, price and coach_avg, ordered by coach_id and id. The benchmark should be that coach’s average over every lesson in the fixture. Repair the query and name a row that exposes the error. Explain why GROUP BY alone cannot return the required grain.

SELECT id,coach_id,price,
 ROUND(AVG(price) OVER (PARTITION BY coach_id ORDER BY lesson_date,id),2) AS coach_avg
FROM lessons ORDER BY coach_id,id;

Prediction / corrected SQL / result evidence / explanation:

6. The error does not undo the first write

Use a fresh rehearsal copy and one SQLite connection. First record balances for ids 2 and 3. Enter the statements below one at a time; the second UPDATE should fail. Before closing the connection, record both balances and the exact error. Run ROLLBACK, record balances again, and explain what would happen without BEGIN. Do not replace ROLLBACK with COMMIT.

SELECT id,balance FROM guests WHERE id IN (2,3) ORDER BY id;
BEGIN;
UPDATE guests SET balance=balance+70 WHERE id=3;
UPDATE guests SET balance=balance-70 WHERE id=2;
-- STOP: inspect the error and both balances here.
-- Then ROLLBACK and inspect both balances again.

Prediction / corrected SQL / result evidence / explanation:

Your class lab and client transfer

Return to learnwithabdou.com/courses/mis3220/labs/lab-11. Open the official handout, Spirit Mountain file and report template from Canvas. Practice questions 1–6 do not replace official Q1–Q15.

Keep the official sequence

Q1–Q5: saved views and summaries. Q6–Q12: rankings, partitions, cumulative totals, lesson comparisons and LAG. Q13–Q15: commits, failures and rollback evidence. Include Check the AI, your client query and personal reflections. Submit your individual Lab11_LastName.docx to Canvas by its deadline.

Milestone 3 check during class

Have your team’s schema and load scripts ready to demonstrate the build from an empty database. Pause the lab for the check, then resume. Keep your actual client data locally.

Plan the reporting layer for Milestone 4

Client / requirement: ______________________________________________

View name and one-row meaning:

Source tables / NULL rules / which zero-activity records must remain:

Ranking tie policy or time-window rule, and one counterexample to test:

View owner: ______________ Query owner: ______________ Reviewer: ______________

Evidence to capture and remaining work:

Agree on at least one reusable view and a query that uses it; plan the portfolio’s subquery, CTE or window-function requirement using your client brief. The official assignment controls the exact requirements.