Meetings by day

1-- core.calendly.meetings_by_day
2-- Calendly's "Scheduled events" analytics per host and event type, as a daily
3-- time series: what was booked, what took place, what was canceled or no-showed.
4--
5-- Grain: day x calendly_account x host x event type.
6-- Depends on: core.calendly.scheduled_events, staging.calendly.scheduled_event_host,
7--             staging.calendly.organization_membership
8--
9-- TWO DATE AXES, NAMED APART. `booked_meetings` lands on the day the meeting was
10-- booked. Every other measure lands on the day the meeting was scheduled to take
11-- place. Calendly's analytics page offers the same toggle; mixing them in one
12-- column is how a team ends up arguing about a number that is right twice.
13--
14-- HOSTS FAN OUT ON PURPOSE. A collective meeting with three hosts is one meeting in
15-- each host's row. Summing across hosts therefore overcounts group meetings; for
16-- organisation totals read core.calendly.scheduled_events directly, or sum
17-- org_meetings_scheduled, which counts each meeting once (on its first host
18-- alphabetically).
19--
20-- TIME ZONE: days are cut in UTC. Change the 'UTC' literal in the first CTE to
21-- your reporting zone (e.g. 'Europe/Copenhagen') if late-evening meetings land on
22-- the wrong day.
23--
24-- Built on a spine of every observed host x event type pair, so a quiet day returns
25-- zeros instead of a gap a line chart would interpolate over.
26
27WITH params AS (
28    SELECT 'UTC' AS tz
29),
30
31meeting_host AS (
32    SELECT
33        e.*,
34        h.host_user_uri,
35        h.host_name,
36        h.host_email,
37        -- One host per meeting carries the org-level count, so it can be summed
38        -- across hosts without double counting.
39        ROW_NUMBER() OVER (
40            PARTITION BY e.calendly_account, e.event_uri ORDER BY h.host_name, h.host_user_uri