Covers by day

1-- core.seven_rooms.covers_by_day
2-- Reservations, covers, cancellations, no-shows and spend per day, venue and
3-- shift. The daily covers report a SevenRooms venue runs on.
4-- Grain: reservation date x venue x shift category.
5--
6--   covers_booked   party size of reservations that were not canceled
7--   covers_arrived  guests SevenRooms recorded as arrived
8--   no_show_rate    no-shows over reservations that were not canceled
9-- Rates are carried with their numerator and denominator; to roll up, sum both
10-- and divide. Spend is in the venue's currency, and only reflects spend
11-- SevenRooms knows about (prepayments, and POS checks if a POS is connected).
12-- Future dates are included, so this also shows what is on the books.
13
14SELECT
15    r.reservation_date,
16    r.venue_id,
17    v.venue_name,
18    v.currency,
19    r.shift_category,
20    COUNT(*)                                                       AS reservations,
21    COUNTIF(r.outcome = 'canceled')                                AS canceled,
22    COUNTIF(r.outcome = 'no_show')                                 AS no_shows,
23    COUNTIF(r.outcome != 'canceled')                               AS reservations_not_canceled,
24    SUM(IF(r.outcome != 'canceled', r.party_size, 0))              AS covers_booked,
25    SUM(IF(r.outcome = 'booked', COALESCE(r.arrived_guests, 0), 0)) AS covers_arrived,
26    SAFE_DIVIDE(COUNTIF(r.outcome = 'no_show'), COUNTIF(r.outcome != 'canceled')) AS no_show_rate,
27    SAFE_DIVIDE(COUNTIF(r.outcome = 'canceled'), COUNT(*))         AS cancellation_rate,
28    COUNTIF(r.is_vip AND r.outcome != 'canceled')                  AS vip_reservations,
29    ROUND(SUM(r.net_spend), 2)                                     AS net_spend,
30    ROUND(SUM(r.gross_spend), 2)                                   AS gross_spend,
31    SAFE_DIVIDE(SUM(r.net_spend),
32                SUM(IF(r.outcome = 'booked', COALESCE(r.arrived_guests, 0), 0))) AS net_spend_per_arrived_cover
33FROM {{staging.seven_rooms.reservation}} r
34LEFT JOIN {{staging.seven_rooms.venue}} v USING (venue_id)
35GROUP BY 1, 2, 3, 4, 5