Usage and stability

1-- core.app_store_connect.usage_and_stability
2-- Sessions, time in app and crashes per app version: App Analytics' Usage and
3-- Crashes views side by side, so a release that crashes more per session shows up
4-- the day it ships.
5--
6-- Grain: one row per day x app x app version x device.
7-- Depends on: staging.app_store_connect.sessions, staging.app_store_connect.crashes
8--
9-- Rates are computed from summed counts in the same row. unique_devices is not
10-- carried: Apple counts it per row of its own file and it cannot be added up.
11-- Both inputs cover opted-in users only, so crashes per session compares like with
12-- like.
13
14WITH sessions AS (
15    SELECT
16        date_day,
17        app_apple_id,
18        ANY_VALUE(app_name)         AS app_name,
19        app_version,
20        device,
21        SUM(sessions)               AS sessions,
22        SUM(total_session_seconds)  AS total_session_seconds
23    FROM {{staging.app_store_connect.sessions}}
24    GROUP BY date_day, app_apple_id, app_version, device
25),
26
27crashes AS (
28    SELECT
29        date_day,
30        app_apple_id,
31        ANY_VALUE(app_name)         AS app_name,
32        app_version,
33        device,
34        SUM(crashes)                AS crashes
35    FROM {{staging.app_store_connect.crashes}}
36    GROUP BY date_day, app_apple_id, app_version, device
37)
38
39SELECT
40    COALESCE(s.date_day, c.date_day)            AS date_day,