Reviews monthly

1-- core.google_my_business_profile.reviews_monthly
2-- Review volume, rating and response per location per month, plus the running
3-- review count and average rating as of each month end.
4--
5-- Grain: month x location_id, every month from a location's first review to the
6-- current month (months without reviews are kept so the running figures are
7-- continuous).
8-- Depends on: staging.google_my_business_profile.review, .location
9--
10-- The running average is rebuilt from the reviews still on the profile. Google
11-- removes deleted and filtered reviews from the API, so history can shift a
12-- little and the latest month may differ from the rating shown on Maps, which
13-- Google rounds and computes itself.
14
15WITH reviews AS (
16    SELECT
17        location_id,
18        DATE_TRUNC(DATE(created_at), MONTH) AS month,
19        star_rating,
20        reply_comment IS NOT NULL           AS has_reply,
21        TIMESTAMP_DIFF(reply_updated_at, created_at, HOUR) AS hours_to_reply
22    FROM {{staging.google_my_business_profile.review}}
23    WHERE created_at IS NOT NULL
24),
25
26spine AS (
27    SELECT location_id, month
28    FROM (
29        SELECT location_id, MIN(month) AS first_month
30        FROM reviews
31        GROUP BY 1
32    ),
33    UNNEST(GENERATE_DATE_ARRAY(first_month, DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)) AS month
34),
35
36monthly AS (
37    SELECT
38        location_id,
39        month,
40        COUNT(*)                                   AS new_reviews,