Tenant activity daily

1-- core.auth0.tenant_activity_daily
2-- The Auth0 Dashboard, by day: logins, signups and leaked-password detections as
3-- Auth0 counts them, next to the same activity rebuilt from the tenant log, with
4-- failures broken down by reason and daily active users.
5--
6-- Grain: one row per UTC day.
7-- Depends on: staging.auth0.stats_daily, staging.auth0.logs
8--
9-- TWO SOURCES, ON PURPOSE. `auth0_logins` and `auth0_signups` are Auth0's own
10-- counters (GET /stats/daily) and go back as far as Auth0 reports them. Everything
11-- prefixed `log_` is counted from the tenant log, which only exists from the day the
12-- connector first ran minus your plan's retention. On days with both, log_logins and
13-- auth0_logins should be close; a persistent gap usually means a login type Auth0
14-- counts that is not mapped to 'login_success' in staging.auth0.logs.
15
16WITH log_daily AS (
17    SELECT
18        event_date                                                          AS date_day,
19        COUNTIF(event_category = 'login_success')                           AS log_logins,
20        COUNTIF(event_category = 'login_failure')                           AS log_failed_logins,
21        COUNTIF(login_failure_reason = 'wrong_password')                    AS log_failed_logins_wrong_password,
22        COUNTIF(login_failure_reason = 'unknown_user')                      AS log_failed_logins_unknown_user,
23        COUNTIF(login_failure_reason = 'other')                             AS log_failed_logins_other,
24        COUNTIF(event_category = 'signup_success')                          AS log_signups,
25        COUNTIF(event_category = 'signup_failure')                          AS log_failed_signups,
26        COUNTIF(event_category = 'silent_auth_success')                     AS log_silent_auths,
27        COUNTIF(event_category = 'blocked_by_attack_protection')            AS log_attack_protection_blocks,
28        -- A user counts as active on a day when they authenticated in any way:
29        -- an interactive login, a silent re-authentication or a token exchange.
30        COUNT(DISTINCT IF(
31            event_category IN ('login_success', 'silent_auth_success', 'user_token_exchange_success'),
32            user_id, NULL))                                                 AS daily_active_users,
33        COUNT(DISTINCT IF(event_category = 'login_success', user_id, NULL)) AS users_logging_in
34    FROM {{staging.auth0.logs}}
35    GROUP BY 1
36)
37
38SELECT
39    COALESCE(s.date_day, l.date_day)        AS date_day,
40