WITH sessions AS (
  SELECT COALESCE(NULLIF(attributes___user_agent___original, ''), resource___user_agent___original) AS ua, attributes___session___id AS session,
    MAX(CASE WHEN {{error_filter}} THEN 1 ELSE 0 END) AS errored
  FROM otel_logs_and_spans WHERE {{query_ast_filters}} AND {{browser_filter}}
  GROUP BY 1, 2
), families AS (
  SELECT session, errored,
    CASE WHEN ua LIKE '%Edg/%' OR ua LIKE '%Edge/%' THEN 'Edge' WHEN ua LIKE '%OPR/%' OR ua LIKE '%Opera%' THEN 'Opera' WHEN ua LIKE '%SamsungBrowser%' THEN 'Samsung Internet'
      WHEN ua LIKE '%Firefox/%' THEN 'Firefox' WHEN ua LIKE '%Chrome/%' OR ua LIKE '%CriOS/%' THEN 'Chrome' WHEN ua LIKE '%Safari/%' THEN 'Safari' ELSE 'Other' END AS browser,
    CASE WHEN ua LIKE '%Windows%' THEN 'Windows' WHEN ua LIKE '%iPhone%' OR ua LIKE '%iPad%' OR ua LIKE '%iPod%' THEN 'iOS' WHEN ua LIKE '%Mac OS X%' OR ua LIKE '%Macintosh%' THEN 'macOS'
      WHEN ua LIKE '%Android%' THEN 'Android' WHEN ua LIKE '%CrOS%' THEN 'ChromeOS' WHEN ua LIKE '%Linux%' OR ua LIKE '%X11%' THEN 'Linux' ELSE 'Other' END AS os,
    CASE WHEN ua LIKE '%iPad%' OR (ua LIKE '%Android%' AND ua NOT LIKE '%Mobile%') THEN 'Tablet' WHEN ua LIKE '%Mobile%' OR ua LIKE '%iPhone%' THEN 'Mobile' ELSE 'Desktop' END AS device
  FROM sessions WHERE ua IS NOT NULL
), counted AS (
  SELECT 1 AS d, 'Browser' AS dimension, browser AS name, COUNT(DISTINCT session) AS n, SUM(errored) AS errored FROM families GROUP BY browser
  UNION ALL SELECT 2, 'Operating system', os, COUNT(DISTINCT session), SUM(errored) FROM families GROUP BY os
  UNION ALL SELECT 3, 'Device', device, COUNT(DISTINCT session), SUM(errored) FROM families GROUP BY device
)
SELECT dimension, name, n::text AS sessions, errored::text AS errored FROM counted ORDER BY d, n DESC