Users

SELECT i.email,
       ANY_VALUE(i.full_name)                            AS full_name,
       ANY_VALUE(s.role)                                 AS role,
       ANY_VALUE(i.email_domain)                         AS email_domain,
       ANY_VALUE(i.is_internal_staff)                    AS is_internal_staff,
       COUNT(DISTINCT i.day)                             AS active_days,
       SUM(s.sessions)                                   AS sessions,
       SUM(s.distinct_sessions)                          AS distinct_sessions,
       SUM(i.sum_active_seconds)                         AS active_seconds,
       ROUND(SUM(i.sum_active_seconds)/3600.0, 2)        AS active_hours,
       ROUND(SUM(i.sum_active_seconds)/60.0, 1)          AS active_minutes
FROM `amazoneast.customerio_data.fact_session_intervals_daily` i
LEFT JOIN (
  SELECT day, email,
         SUM(sessions) AS sessions,
         COUNT(*)      AS distinct_sessions,  -- proxy for concurrent-browser footprint
         ANY_VALUE(role) AS role
  FROM `amazoneast.customerio_data.fact_sessions_daily`
  WHERE day BETWEEN DATE '2026-06-17' AND DATE '2026-06-24'
  GROUP BY day, email
) s USING (day, email)
WHERE i.day BETWEEN DATE '2026-06-17' AND DATE '2026-06-24'
GROUP BY i.email
ORDER BY active_seconds DESC
LIMIT 5000