How often does the model get it right?

The 22 questions and their gold SQL

The first ten paraphrase the brief's business questions with every parameter stated, so each has one right answer. Ten more cover the rest of the schema, and two ask for data the database does not hold: the right answer there is to decline. All were written before any model was run against them.

1. b01-payment-methodsBrief question 1

   How many museum tickets were sold with each payment method for orders placed between 1 July 2019 and 30 June 2020 inclusive? Return the payment method and the number of tickets.

   Tickets are order-line quantities, not orders; four joins.

   Gold SQL and answer

   SELECT p.method, sum(l.quantity) AS tickets
   FROM purchase_order o
   JOIN payment p    ON p.payment_id = o.payment_id
   JOIN order_line l ON l.order_id = o.order_id
   JOIN product pr   ON pr.product_id = l.product_id
   WHERE pr.code IN ('TICKET_ONLINE', 'TICKET_ON_SITE')
     AND date(o.ordered_at) BETWEEN '2019-07-01' AND '2020-06-30'
   GROUP BY p.method

2. b02-walkers-entranceBrief question 2

   For visits from 30 August 2019 to 29 February 2020 inclusive, which entrance had the most visitors who arrived on foot? Return the entrance name and that number of visitors.

   Transport is recorded only on first entries; re-entries must not count.

   Gold SQL and answer

   SELECT en.name, count(*) AS walkers
   FROM museum_visit v
   JOIN entrance en ON en.entrance_id = v.entrance_id
   WHERE v.transport_mode = 'Walk'
     AND v.visit_date BETWEEN '2019-08-30' AND '2020-02-29'
   GROUP BY en.entrance_id
   ORDER BY walkers DESC
   LIMIT 1

3. b03-audio-guide-shareBrief question 3

   What percentage of museum visitors in calendar year 2019 hired at least one audio guide? Round to one decimal place.

   A visitor with two hires counts once; integer division gives 0.

   Gold SQL and answer

   SELECT round(100.0 * count(DISTINCT h.ticket_id) / count(DISTINCT v.ticket_id), 1) AS pct
   FROM museum_visit v
   LEFT JOIN audio_guide_hire h ON h.ticket_id = v.ticket_id
   WHERE v.visit_date BETWEEN '2019-01-01' AND '2019-12-31'

4. b04-guide-minutesBrief question 4

   For every audio guide hired on 6 August 2019, list the device serial number and how many whole minutes it was out (return time minus hire time, rounded to the nearest minute).

   Date arithmetic on text timestamps with julianday().

   Gold SQL and answer

   SELECT serial_number,
          CAST(round((julianday(returned_at) - julianday(issued_at)) * 1440) AS INTEGER) AS minutes
   FROM audio_guide_hire
   WHERE date(issued_at) = '2019-08-06'

5. b05-wing-routeBrief question 5

   Take each ticket's wing scans in time order and merge consecutive scans of the same wing. Which route is most common, and how many tickets followed it? Write the route as the wing names joined by ' > ' (for example 'Denon > Sully').

   Needs a window function and an ordered string aggregate; the 2020 design could not answer it.

   Gold SQL and answer

   WITH scans AS (
     SELECT ticket_id, scanned_at, scan_id, wing_id,
            lag(wing_id) OVER (PARTITION BY ticket_id ORDER BY scanned_at, scan_id) AS previous_wing
     FROM wing_scan
   ),
   routes AS (
     SELECT sc.ticket_id, group_concat(w.name, ' > ' ORDER BY sc.scanned_at, sc.scan_id) AS route
     FROM scans sc
     JOIN wing w ON w.wing_id = sc.wing_id
     WHERE sc.previous_wing IS NULL OR sc.previous_wing <> sc.wing_id
     GROUP BY sc.ticket_id
   )
   SELECT route, count(*) AS tickets
   FROM routes
   GROUP BY route
   ORDER BY tickets DESC
   LIMIT 1

6. b06-all-three-wingsBrief question 6

   How many tickets were scanned into all three wings?

   Repeat visits to a wing must not count twice.

   Gold SQL and answer

   SELECT count(*) AS tickets
   FROM (SELECT ticket_id FROM wing_scan GROUP BY ticket_id HAVING count(DISTINCT wing_id) = 3)

7. b07-top-exhibitionsBrief question 7

   Which ten special exhibitions had the most admissions at the Hall Napoleon door? Return each title and its number of admissions.

   Attendance is admissions, not bookings; four joins.

   Gold SQL and answer

   SELECT e.title, count(h.scan_id) AS admissions
   FROM exhibition e
   JOIN exhibition_slot s     ON s.exhibition_id = e.exhibition_id
   JOIN exhibition_booking b  ON b.slot_id = s.slot_id
   JOIN hall_napoleon_scan h  ON h.booking_id = b.booking_id
   GROUP BY e.exhibition_id
   ORDER BY admissions DESC
   LIMIT 10

8. b08-popular-slotsBrief question 8Order scored

   Across all exhibitions and dates, which five 15-minute start times had the most Hall Napoleon admissions? Return the start time and admissions, most first.

   A ranking, so order is scored.

   Gold SQL and answer

   SELECT s.start_time, count(h.scan_id) AS admissions
   FROM exhibition_slot s
   JOIN exhibition_booking b ON b.slot_id = s.slot_id
   JOIN hall_napoleon_scan h ON h.booking_id = b.booking_id
   GROUP BY s.start_time
   ORDER BY admissions DESC
   LIMIT 5

9. b09-unused-group-ticketsBrief question 9

   Consider online orders that bought 30 or more tickets. In total, how many of the tickets in those orders were never scanned at an entrance?

   An anti-join, and the order size lives in order_line.

   Gold SQL and answer

   SELECT count(*) AS unused
   FROM purchase_order o
   JOIN order_line l ON l.order_id = o.order_id
   JOIN product p    ON p.product_id = l.product_id AND p.code = 'TICKET_ONLINE'
   JOIN ticket t     ON t.order_id = o.order_id
   WHERE o.channel = 'online'
     AND l.quantity >= 30
     AND NOT EXISTS (SELECT 1 FROM entry_scan e WHERE e.ticket_id = t.ticket_id)

10. b10-winter-morningsBrief question 10

    In December, January and February (all years), which weekday from Monday to Friday had the most visitors whose first entry was before 12:00? Return the weekday name (for example 'Tuesday') and the number of visitors.

    SQLite numbers weekdays from Sunday = 0; needs a CASE to name them.

    Gold SQL and answer

    SELECT CASE strftime('%w', visit_date)
             WHEN '1' THEN 'Monday' WHEN '2' THEN 'Tuesday' WHEN '3' THEN 'Wednesday'
             WHEN '4' THEN 'Thursday' WHEN '5' THEN 'Friday' END AS weekday,
           count(*) AS visitors
    FROM museum_visit
    WHERE CAST(strftime('%m', visit_date) AS INTEGER) IN (12, 1, 2)
      AND strftime('%w', visit_date) NOT IN ('0', '6')
      AND time(first_entry_at) < '12:00:00'
    GROUP BY strftime('%w', visit_date)
    ORDER BY visitors DESC
    LIMIT 1

11. x01-tickets-soldExtra

    How many museum tickets were sold in total, online and on site?

    Sum of quantities, not a count of rows.

    Gold SQL and answer

    SELECT sum(l.quantity) AS tickets
    FROM order_line l
    JOIN product p ON p.product_id = l.product_id
    WHERE p.code IN ('TICKET_ONLINE', 'TICKET_ON_SITE')

12. x02-revenue-2019Extra

    What was the total revenue in euros from all products for orders placed in 2019? Use quantity times the unit price charged, and round to two decimal places.

    Money is stored in cents.

    Gold SQL and answer

    SELECT round(sum(l.quantity * l.unit_price_cents) / 100.0, 2) AS revenue_eur
    FROM order_line l
    JOIN purchase_order o ON o.order_id = l.order_id
    WHERE strftime('%Y', o.ordered_at) = '2019'

13. x03-top-languageExtra

    Which audio-guide language was chosen most often? Return the language name and the number of hires.

    A lookup join for the name.

    Gold SQL and answer

    SELECT lang.name, count(*) AS hires
    FROM audio_guide_hire h
    JOIN language lang ON lang.language_code = h.language_code
    GROUP BY lang.language_code
    ORDER BY hires DESC
    LIMIT 1

14. x04-devices-in-serviceExtra

    How many audio-guide devices are still in service, that is, have never been retired?

    NULL means still in service.

    Gold SQL and answer

    SELECT count(*) AS devices FROM audio_guide_device WHERE retired_on IS NULL

15. x05-hires-by-wingExtra

    For each wing, how many audio guides were hired there? Return the wing name and the number of hires.

    Two wing columns; the hire wing is issued_wing_id.

    Gold SQL and answer

    SELECT w.name, count(*) AS hires
    FROM audio_guide_hire h
    JOIN wing w ON w.wing_id = h.issued_wing_id
    GROUP BY w.wing_id

16. x06-busiest-monthExtra

    Which calendar month had the most museum visitors? Return the month as 'YYYY-MM' and the number of visitors.

    Visitors are first entries, not every scan.

    Gold SQL and answer

    SELECT strftime('%Y-%m', visit_date) AS month, count(*) AS visitors
    FROM museum_visit
    GROUP BY month
    ORDER BY visitors DESC
    LIMIT 1

17. x07-full-slotsExtra

    How many exhibition time slots were fully booked, with no places left?

    The slot_availability view answers it directly.

    Gold SQL and answer

    SELECT count(*) AS full_slots FROM slot_availability WHERE remaining = 0

18. x08-cash-shareExtra

    What percentage of all payments were made in cash? Round to one decimal place.

    Integer division gives 0 without the 100.0.

    Gold SQL and answer

    SELECT round(100.0 * sum(method = 'cash') / count(*), 1) AS cash_pct FROM payment

19. x09-re-entriesExtra

    How many tickets were scanned at an entrance more than once, that is, re-entered the museum?

    Grouping before counting.

    Gold SQL and answer

    SELECT count(*) AS tickets
    FROM (SELECT ticket_id FROM entry_scan GROUP BY ticket_id HAVING count(*) > 1)

20. x10-top-bankExtra

    Which bank issued the cards behind the most card payments? Return the bank's name and the number of card payments.

    Card details live in a subtype table.

    Gold SQL and answer

    SELECT fi.name, count(*) AS payments
    FROM card_payment cp
    JOIN financial_institution fi ON fi.institution_id = cp.institution_id
    GROUP BY fi.institution_id
    ORDER BY payments DESC
    LIMIT 1

21. u01-visitor-ageUnanswerable

    What is the average age of visitors who hired an audio guide?

    The database records no ages; the right answer is to decline.

22. u02-guide-complaintsUnanswerable

    Which audio-guide language received the most complaints from visitors?

    There is no feedback or complaints data; the right answer is to decline.