gwecode

sql · level 3 check

the market stalls' report.

Time to ship a real tool. You run the numbers for a group of market stalls. Each stall's database has an `orders` table with the columns `id`, `day`, `customer`, `amount` (in whole pounds), `status` and `basket`, a JSON list of the items bought.

Real data is messy. Some orders were `refunded`, some have no `amount`, some days had no orders at all, customers can tie on spending, and some baskets are missing or broken JSON. Your report must cope with all of it.

First, make a view called `paid` that keeps only orders whose status is `paid` and whose amount is not `NULL`. Then write three queries on it, in this order. One: the takings for every day from the first paid order to the last, with 0 for empty days and a running total. Two: the customers in the top three places by spending, ranked like a sports table, so a tie shares a place and the next place is skipped (1, 2, 2, 4). Three: how many of each item were sold, using only valid baskets, most sold first, then A to Z.

Keep these column names: `day`, `takings`, `running_total`; `place`, `customer`, `spent`; `item`, `sold`. Your report will run on five different stalls, so it must work for any stall, not only this one. Fill in each `____` and run it.

Fill in every ____ then run the checks.
  1. 1 It shows the coffee stall's empty day as 0 and keeps the running total goinguses: recursive CTE, COALESCE and running total
  2. 2 It leaves out the cheese stall's refund and the order with no amountuses: a view that filters bad rows
  3. 3 Tied flower customers share second place, and fourth place is left outuses: RANK and filtering a window
  4. 4 It counts book items from valid baskets only, skipping broken and missing onesuses: json_each and json_valid
  5. 5 It gives the whole plant stall report, gap, refund and tied items includeduses: putting it together

Take as long as you like and try as many times as you want.