Logins by application

1-- core.auth0.logins_by_application
2-- Login and signup activity per application and connection, from the tenant log.
3-- Answers "which app is failing logins" and "which connection do people sign up
4-- with", which Auth0's Dashboard only shows tenant-wide.
5--
6-- Grain: one row per UTC day x client (application) x connection.
7-- Depends on: staging.auth0.logs
8--
9-- Events without a client or connection (some failures happen before Auth0 has
10-- resolved either) keep NULL in that column rather than being dropped, so the
11-- counts sum back to tenant_activity_daily.
12
13SELECT
14    event_date                                                      AS date_day,
15    client_id,
16    ANY_VALUE(client_name)                                          AS client_name,
17    connection_name,
18    ANY_VALUE(strategy)                                             AS strategy,
19
20    COUNTIF(event_category = 'login_success')                       AS logins,
21    COUNTIF(event_category = 'login_failure')                       AS failed_logins,
22    COUNTIF(login_failure_reason = 'wrong_password')                AS failed_logins_wrong_password,
23    COUNTIF(login_failure_reason = 'unknown_user')                  AS failed_logins_unknown_user,
24    COUNTIF(event_category = 'signup_success')                      AS signups,
25    COUNTIF(event_category = 'signup_failure')                      AS failed_signups,
26    COUNTIF(event_category = 'login_success' AND is_mobile)         AS mobile_logins,
27
28    -- Distinct per row only: a user who logs in to two apps counts in both, so do
29    -- not sum this column across clients. Use tenant_activity_daily for a total.
30    COUNT(DISTINCT IF(event_category = 'login_success', user_id, NULL)) AS users_logging_in,
31
32    ROUND(SAFE_DIVIDE(
33        COUNTIF(event_category = 'login_success'),
34        COUNTIF(event_category IN ('login_success', 'login_failure'))
35    ), 4)                                                           AS login_success_rate
36FROM {{staging.auth0.logs}}
37WHERE event_category IN ('login_success', 'login_failure', 'signup_success', 'signup_failure')
38GROUP BY event_date, client_id, connection_name