Investigate Spanner failure rates in telemetry about services-engineering HOT 4 OPEN

WITH 
    d as (
        SELECT
            timestamp_trunc(submission_timestamp,hour) as hour,
            case when s.failure_reason is null then concat('success', '_', case when payload.sync_node_type is null then 'unknown' else payload.sync_node_type end)
                else concat(s.failure_reason.code, '_', case when payload.sync_node_type is null then 'unknown' else payload.sync_node_type end) end as node_type_sync_failure_reason,
            count(*) as syncs_with_name,
            count(distinct payload.uid) as users_with_name
        from `moz-fx-data-shared-prod.telemetry_live.sync_v4`
        CROSS JOIN UNNEST(payload.syncs) s
        where s.failure_reason.name = "nserror"
        AND date(submission_timestamp) >= date(2020,1,9)
        AND normalized_channel in ('nightly', 'beta', 'release')
        AND safe_cast(substr(application.version,1,2) as int64) >= 73 
        group by 1,2 order by 1,2
    ),
    
    totals as (
       SELECT
            timestamp_trunc(submission_timestamp,hour) as hour,
            case when payload.sync_node_type is null then 'unknown' else payload.sync_node_type end as sync_node_type,
            count(*) as total_syncs,
            count(distinct payload.uid) as total_users
        from `moz-fx-data-shared-prod.telemetry_live.sync_v4`
        CROSS JOIN UNNEST(payload.syncs) s
        where date(submission_timestamp) >= date(2020,1,9)
        AND normalized_channel in ('nightly', 'beta', 'release')
        AND safe_cast(substr(application.version,1,2) as int64) >= 73 
        group by 1,2
    
    )
    
SELECT
    d.*,
    t.total_users,
    t.total_syncs,
    t.sync_node_type,
    d.syncs_with_name / t.total_syncs as proportion_of_syncs_with_failure_reason,
    d.users_with_name / t.total_users as proportion_of_users_with_failure_reason
        
FROM d 
INNER JOIN totals t
ON d.hour = t.hour AND split(d.node_type_sync_failure_reason, '_')[OFFSET(1)] = t.sync_node_type
WHERE t.sync_node_type != 'unknown'
order by 1,2