Booking lead time

1-- core.seven_rooms.booking_lead_time
2-- How far ahead guests book, by who took the booking and the reservation type.
3-- Grain: reservation date x venue x booked by x reservation type x lead time band.
4--
5-- Lead time is days between the booking being created and the reservation date.
6-- booked_by is the SevenRooms user or channel recorded on the reservation, as
7-- SevenRooms names it; reservation_type is SevenRooms' own field. Canceled
8-- reservations are included and counted separately, since late cancellations
9-- are part of the picture.
10
11WITH bookings AS (
12    SELECT
13        *,
14        DATE_DIFF(reservation_date, DATE(created_at), DAY) AS lead_days
15    FROM {{staging.seven_rooms.reservation}}
16    WHERE created_at IS NOT NULL
17)
18
19SELECT
20    reservation_date,
21    venue_id,
22    booked_by,
23    reservation_type,
24    CASE
25        WHEN lead_days <= 0  THEN 'same_day'
26        WHEN lead_days <= 2  THEN '1_2_days'
27        WHEN lead_days <= 7  THEN '3_7_days'
28        WHEN lead_days <= 30 THEN '8_30_days'
29        ELSE 'over_30_days'
30    END                                         AS lead_time_band,
31    COUNT(*)                                    AS reservations,
32    COUNTIF(outcome = 'canceled')               AS canceled,
33    COUNTIF(outcome = 'no_show')                AS no_shows,
34    SUM(IF(outcome != 'canceled', party_size, 0)) AS covers_booked,
35    SUM(GREATEST(lead_days, 0))                 AS lead_days_total
36FROM bookings
37GROUP BY 1, 2, 3, 4, 5