The Data Detective

Beginner · 90 min build · 🏆 milestone project

You have finished Part I. Now you will use it the way analysts actually use SQL: take a messy-looking business question, turn it into a query, and prove the answer from the grid.

The cafe has exported one week of point-of-sale line items into a single table, cafe_sales. Your job is to answer 15 business questions with only the tools you already have: SELECT, WHERE, ORDER BY, LIMIT, expressions, aliases, CASE, aggregate functions, GROUP BY, and HAVING.

What you'll build

You will produce a query notebook with four sections:

  • Extract checks: prove you understand the table before trusting it.
  • Sales pulse: find the week total, slowest day, rush hour, and leading branch.
  • Menu and baskets: find top products, category performance, and multi-line tickets.
  • Operations: explain payment mix, loyalty spend, discounts, and barista workload.

The table is deliberately single-table. A ticket can appear on several rows because each row is one line item. That means ticket_id is not unique, but line_id is. Use COUNT(*) when you mean line rows, and COUNT(DISTINCT ticket_id) when you mean tickets.

Use this revenue convention throughout:

  • Gross line sales: quantity * unit_price
  • Discount dollars: quantity * unit_price * discount_pct / 100.0
  • Net line sales: quantity * unit_price * (100 - discount_pct) / 100.0

For the two-decimal money checks, cast the final total to numeric(8,2). You learned casts in 3.4; the deeper money-and-rounding lesson comes later.

Workspace

Run the starter queries first. Then replace the TODO comments with your own answers.

sql — playgroundlive
⌘/Ctrl + Enter to run

Checkpoint 1: trust the extract

Answer these first. They are not busywork; they are how you avoid building a polished report on misunderstood data.

  1. How many line items and distinct tickets are in the extract?
  2. What date range is covered, and how many weekdays appear?
  3. Which branch/channel combinations appear, and which one has the most line rows?

Acceptance criteria:

  • The extract has 90 line items and 42 distinct tickets.
  • The date range is 2026-04-06 through 2026-04-12, covering 7 weekdays.
  • There are 9 branch/channel combinations; Center + counter has the most line rows, with 22.

Checkpoint 2: sales pulse

Now answer the manager's first "how was the week?" questions.

  1. What were gross sales, discount dollars, and net sales for the week?
  2. Which weekday was slowest by distinct ticket count?
  3. Which hour sold the most units?
  4. Which branch led the week by net sales?

Acceptance criteria:

  • Gross sales are 481.50, discount dollars are 13.92, and net sales are 467.59.
  • Sunday is the slowest weekday with 4 tickets.
  • Hour 12 sold the most units, with 21.
  • Center leads net sales with 198.20.

Checkpoint 3: menu and baskets

This is the product readout: what sold, where it sold, and which tickets deserve a closer look.

  1. What are the top 5 item names by units sold?
  2. Which category has the highest gross sales?
  3. Which branch/category pairs have gross sales of at least 35.00?
  4. Which tickets have exactly 3 line rows, so the team can inspect likely bundles?

Acceptance criteria:

  • The top item by units is Cookie with 12 units. The next three are Cappuccino, Cold Brew, and Espresso with 10 units each when sorted by item name as the tie-breaker.
  • The highest-grossing category is coffee with 144.00.
  • There are 5 branch/category pairs at or above 35.00 gross sales; the largest is Center + food at 72.00.
  • There are 7 tickets with exactly 3 line rows: 1004, 1011, 1015, 1021, 1029, 1036, and 1042.

Checkpoint 4: operations report

Finish with questions the shift leads can act on.

  1. What is the payment method mix by distinct tickets and net sales?
  2. Which loyalty tier has the highest net sales?
  3. Which channel accounts for the discount dollars?
  4. Which baristas handled the most distinct tickets, and what gross sales did they handle?

Acceptance criteria:

  • card leads payment methods with 24 tickets and 279.19 net sales.
  • gold leads loyalty tiers with 136.43 net sales.
  • delivery accounts for all 13.92 discount dollars; counter and mobile should both show 0.00.
  • Ada, Ken, and Priya each handled 9 distinct tickets. Their gross sales are 108.35, 94.50, and 94.70 respectively.

Stretch goals

Try these after the required 15 answers pass:

  • Use CASE to create dayparts: morning before 11, lunch from 11 through 13, afternoon after 13. Which daypart has the highest net sales?
  • Use FILTER to put counter, mobile, and delivery ticket counts side by side in one row.
  • Use HAVING to list item names with at least 8 units sold, sorted by net sales.
  • Re-run your branch report once with gross sales and once with net sales. Which branch changes the most after discounts?

How to get unstuck

If a query feels tangled, peel it back like 4.4 taught: start with FROM cafe_sales, add the row-level WHERE, then add grouping, then HAVING, then sorting and limiting.

Use 2.3 when a "top" or "slowest" answer looks unstable: it needs ORDER BY before LIMIT. Use 4.1 when a count feels off: line rows and tickets are different questions. Use 4.2 when your result has too many or too few rows: read the grain of the GROUP BY. Use 4.3 when you are filtering groups: aggregate conditions belong in HAVING.

+50 XP on completion