Subscriptions daily

1-- core.google_play.subscriptions_daily
2-- New, canceled and active subscriptions per subscription product and buyer
3-- country, by day: the subscription counts behind Play Console's Subscriptions
4-- overview, with base plans and offers added together.
5--
6-- Grain: one row per day x package x product x country.
7-- Depends on: staging.google_play.subscriptions_by_country
8--
9-- ACTIVE IS A LEVEL. active_subscriptions is the count on that day. For a week or
10-- a month take the last day, not the sum. new_ and canceled_ are daily flows and
11-- do sum over a period.
12--
13-- A product and country with no row on a day had nothing reported that day; if a
14-- dashboard needs a value for every day, carry the last active_subscriptions
15-- forward rather than reading the gap as zero.
16--
17-- Revenue for these subscriptions is in earnings_monthly, joined on package and
18-- product: Play's product_id here is the sku_id there.
19
20SELECT
21    date_day,
22    package_name,
23    product_id,
24    country,
25    SUM(new_subscriptions)                                  AS new_subscriptions,
26    SUM(canceled_subscriptions)                             AS canceled_subscriptions,
27    SUM(new_subscriptions) - SUM(canceled_subscriptions)    AS net_new_subscriptions,
28    SUM(active_subscriptions)                               AS active_subscriptions
29FROM {{staging.google_play.subscriptions_by_country}}
30GROUP BY date_day, package_name, product_id, country