Ten questions, answered

-- Tickets sold per payment method in the current financial year
WITH params AS (SELECT '2019-07-01' AS fy_start, '2020-06-30' AS fy_end)
SELECT p.method        AS payment_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
CROSS JOIN params
WHERE pr.code IN ('TICKET_ONLINE', 'TICKET_ON_SITE')
  AND date(o.ordered_at) BETWEEN params.fy_start AND params.fy_end
GROUP BY p.method
ORDER BY tickets DESC;

-- Entrances ranked by visitors who walked to the museum, last six months
WITH params AS (SELECT '2020-02-29' AS today)
SELECT en.name  AS entrance,
       count(*) AS pedestrian_visitors
FROM museum_visit v
JOIN entrance en ON en.entrance_id = v.entrance_id
CROSS JOIN params
WHERE v.transport_mode = 'Walk'
  AND v.visit_date >  date(params.today, '-6 months')
  AND v.visit_date <= params.today
GROUP BY en.name
ORDER BY pedestrian_visitors DESC;

-- Share of each day's visitors who hired an audio guide, averaged by month
WITH daily AS (
  SELECT v.visit_date,
         count(DISTINCT v.ticket_id) AS visitors,
         count(DISTINCT h.ticket_id) AS hired_guide
  FROM museum_visit v
  LEFT JOIN audio_guide_hire h ON h.ticket_id = v.ticket_id
  GROUP BY v.visit_date
)
SELECT strftime('%Y-%m', visit_date)                     AS month,
       count(*)                                          AS days,
       sum(visitors)                                     AS visitors,
       sum(hired_guide)                                  AS hired_guide,
       round(avg(100.0 * hired_guide / visitors), 1)     AS avg_daily_pct
FROM daily
WHERE visit_date > date('2020-02-29', '-12 months')
GROUP BY month
ORDER BY month;

-- Minutes each audio guide was out on one day
WITH params AS (SELECT '2019-08-06' AS day)
SELECT h.serial_number,
       lang.name                       AS language,
       time(h.issued_at)               AS issued,
       time(h.returned_at)             AS returned,
       CAST(round((julianday(h.returned_at) - julianday(h.issued_at)) * 1440) AS INTEGER) AS minutes_in_use
FROM audio_guide_hire h
JOIN language lang ON lang.language_code = h.language_code
CROSS JOIN params
WHERE date(h.issued_at) = params.day
ORDER BY h.issued_at;

-- Most common routes through the wings
WITH scans AS (
  SELECT s.ticket_id, s.scanned_at, s.scan_id, s.wing_id,
         lag(s.wing_id) OVER (PARTITION BY s.ticket_id ORDER BY s.scanned_at, s.scan_id) AS previous_wing
  FROM wing_scan s
),
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 visitors,
       round(100.0 * count(*) / (SELECT count(*) FROM routes), 1) AS pct
FROM routes
GROUP BY route
ORDER BY visitors DESC, route
LIMIT 10;

-- Visitors who scanned into Richelieu, Denon and Sully on their visit
WITH wings_seen AS (
  SELECT ticket_id, count(DISTINCT wing_id) AS wings
  FROM wing_scan
  GROUP BY ticket_id
),
per_year AS (
  SELECT strftime('%Y', v.visit_date) AS year,
         count(*)                    AS visitors,
         sum(ws.wings = 3)           AS saw_all_three
  FROM museum_visit v
  LEFT JOIN wings_seen ws ON ws.ticket_id = v.ticket_id
  GROUP BY year
)
SELECT year, visitors, saw_all_three,
       round(100.0 * saw_all_three / visitors, 1) AS pct
FROM per_year
UNION ALL
SELECT 'All years', sum(visitors), sum(saw_all_three),
       round(100.0 * sum(saw_all_three) / sum(visitors), 1)
FROM per_year;

-- Ten best-attended Hall Napoleon exhibitions, last five years
WITH params AS (SELECT '2020-02-29' AS today)
SELECT e.title,
       e.opens_on,
       e.closes_on,
       count(h.scan_id) AS attendance
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
CROSS JOIN params
WHERE s.slot_date > date(params.today, '-5 years')
GROUP BY e.exhibition_id
ORDER BY attendance DESC
LIMIT 10;

-- 15-minute slots ranked by admissions
SELECT s.start_time            AS slot,
       count(h.scan_id)        AS admitted,
       count(b.booking_id)     AS booked,
       round(100.0 * count(h.scan_id) / count(b.booking_id), 1) AS turned_up_pct
FROM exhibition_slot s
JOIN exhibition_booking b      ON b.slot_id = s.slot_id
LEFT JOIN hall_napoleon_scan h ON h.booking_id = b.booking_id
GROUP BY s.start_time
ORDER BY admitted DESC, slot
LIMIT 10;

-- Large online orders and their unused tickets
SELECT o.order_id,
       date(o.ordered_at)              AS ordered_on,
       l.quantity                      AS tickets,
       sum(v.ticket_id IS NULL)        AS unused,
       sum(sum(v.ticket_id IS NULL)) OVER () AS unused_all_orders
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
LEFT JOIN museum_visit v ON v.ticket_id = t.ticket_id
WHERE o.channel = 'online'
  AND l.quantity >= 30
GROUP BY o.order_id
ORDER BY unused DESC, o.order_id
LIMIT 15;

-- Winter weekday mornings ranked by visitors entering before noon
SELECT CASE strftime('%w', v.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 morning_visitors,
       count(DISTINCT v.visit_date) AS mornings,
       round(1.0 * count(*) / count(DISTINCT v.visit_date), 2) AS per_morning
FROM museum_visit v
WHERE CAST(strftime('%m', v.visit_date) AS INTEGER) IN (12, 1, 2)
  AND strftime('%w', v.visit_date) NOT IN ('0', '6')
  AND time(v.first_entry_at) < '12:00:00'
GROUP BY strftime('%w', v.visit_date)
ORDER BY morning_visitors DESC;