Ratings and reviews

1-- core.app_store_connect.ratings_and_reviews
2-- Written reviews per day and territory: how many, average stars, how many were
3-- negative, and how many you answered.
4--
5-- Grain: one row per review day (UTC) x app x territory (three-letter code).
6-- Depends on: staging.app_store_connect.customer_reviews
7--
8-- Average rating is shipped as a sum and a count so it rolls up correctly. It
9-- covers written reviews only; Apple's API does not return star-only ratings, so
10-- this will not match the rating shown on your App Store page.
11--
12-- Territory is Apple's three-letter code ('USA'); the other models in this folder
13-- use two-letter codes ('US'). Map one to the other before joining.
14
15SELECT
16    created_date                                        AS date_day,
17    app_apple_id,
18    territory_alpha3,
19
20    COUNT(*)                                            AS reviews,
21    SUM(rating)                                         AS rating_sum,
22    ROUND(SAFE_DIVIDE(SUM(rating), COUNT(rating)), 2)   AS avg_rating,
23    COUNTIF(rating >= 4)                                AS positive_reviews,
24    COUNTIF(rating <= 2)                                AS negative_reviews,
25    COUNTIF(response_id IS NOT NULL)                    AS reviews_replied,
26    COUNTIF(rating <= 2 AND response_id IS NULL)        AS negative_reviews_unanswered
27FROM {{staging.app_store_connect.customer_reviews}}
28GROUP BY created_date, app_apple_id, territory_alpha3