SELECT
  COALESCE(NULLIF(attributes___url___path, ''), NULLIF(attributes___url___full, ''), NULLIF(replace(name, 'Pageview · ', ''), ''), 'Unknown page') AS page,
  COUNT(*)::text AS requests,
  COUNT(*) FILTER (WHERE status_code = 'ERROR' OR lower(COALESCE(level, '')) = 'error' OR attributes___exception___type IS NOT NULL)::text AS errors,
  ROUND((approx_percentile(0.75, percentile_agg(duration)) / 1000000.0)::numeric, 1)::text AS p75_ms
FROM otel_logs_and_spans
WHERE project_id='{{project_id}}'
  AND hashes @> ARRAY['{{var-endpointHash}}']
  AND (resource___telemetry___sdk___language IN ('webjs', 'javascript', 'js') OR COALESCE(resource___user_agent___original, '') <> '' OR name IN ('documentLoad', 'documentFetch') OR name LIKE 'Pageview %')
  {{time_filter}}
GROUP BY page ORDER BY COUNT(*) DESC LIMIT 20

SELECT
  browser.attributes___session___id AS session_id,
  COALESCE(MAX(NULLIF(browser.attributes___url___path, '')), MAX(NULLIF(browser.attributes___url___full, '')), MAX(NULLIF(replace(browser.name, 'Pageview · ', ''), '')), 'Unknown page') AS last_page,
  CASE WHEN BOOL_OR(replay.session_id IS NOT NULL) THEN 'Available' ELSE 'Not recorded' END AS replay,
  COUNT(*) FILTER (WHERE browser.status_code = 'ERROR' OR lower(COALESCE(browser.level, '')) = 'error' OR browser.attributes___exception___type IS NOT NULL)::text AS errors,
  MAX(browser.timestamp)::text AS last_seen
FROM otel_logs_and_spans browser
LEFT JOIN projects.replay_sessions replay
  ON CAST(replay.project_id AS TEXT) = browser.project_id
  AND CAST(replay.session_id AS TEXT) = browser.attributes___session___id
  AND (replay.event_file_count > 0 OR cardinality(replay.file_keys) > 0 OR cardinality(replay.shard_keys) > 0)
WHERE browser.project_id='{{project_id}}'
  AND browser.hashes @> ARRAY['{{var-endpointHash}}']
  AND (browser.resource___telemetry___sdk___language IN ('webjs', 'javascript', 'js') OR COALESCE(browser.resource___user_agent___original, '') <> '' OR browser.name IN ('documentLoad', 'documentFetch') OR browser.name LIKE 'Pageview %')
  AND browser.attributes___session___id IS NOT NULL
  AND browser.attributes___session___id <> ''
  {{time_filter}}
GROUP BY browser.attributes___session___id
ORDER BY MAX(browser.timestamp) DESC LIMIT 50

SELECT
  CASE
    WHEN resource___user_agent___original IS NULL OR resource___user_agent___original = '' THEN 'Unknown browser'
    WHEN lower(resource___user_agent___original) LIKE '%edg/%' THEN 'Edge'
    WHEN lower(resource___user_agent___original) LIKE '%opr/%' OR lower(resource___user_agent___original) LIKE '%opera%' THEN 'Opera'
    WHEN lower(resource___user_agent___original) LIKE '%firefox/%' THEN 'Firefox'
    WHEN lower(resource___user_agent___original) LIKE '%chrome/%' OR lower(resource___user_agent___original) LIKE '%crios/%' THEN 'Chrome'
    WHEN lower(resource___user_agent___original) LIKE '%safari/%' THEN 'Safari'
    ELSE 'Other browser'
  END AS browser,
  COUNT(DISTINCT NULLIF(attributes___session___id, ''))::text AS sessions,
  COUNT(*)::text AS requests,
  COUNT(*) FILTER (WHERE status_code = 'ERROR' OR lower(COALESCE(level, '')) = 'error' OR attributes___exception___type IS NOT NULL)::text AS errors,
  ROUND((approx_percentile(0.75, percentile_agg(duration)) / 1000000.0)::numeric, 1)::text AS p75_ms
FROM otel_logs_and_spans
WHERE project_id='{{project_id}}'
  AND hashes @> ARRAY['{{var-endpointHash}}']
  AND (resource___telemetry___sdk___language IN ('webjs', 'javascript', 'js') OR COALESCE(resource___user_agent___original, '') <> '' OR name IN ('documentLoad', 'documentFetch') OR name LIKE 'Pageview %')
  {{time_filter}}
GROUP BY browser
ORDER BY COUNT(*) DESC LIMIT 20

WITH endpoint_sessions AS (
  SELECT DISTINCT attributes___session___id AS session_id
  FROM otel_logs_and_spans
  WHERE project_id='{{project_id}}'
    AND hashes @> ARRAY['{{var-endpointHash}}']
    AND attributes___session___id IS NOT NULL
    AND attributes___session___id <> ''
    AND (resource___telemetry___sdk___language IN ('webjs', 'javascript', 'js') OR COALESCE(resource___user_agent___original, '') <> '' OR name IN ('documentLoad', 'documentFetch') OR name LIKE 'Pageview %')
    {{time_filter}}
), vital_samples AS (
  SELECT
    metric_name,
    COALESCE(attributes->>'session.id', attributes->>'session_id') AS session_id,
    COALESCE(value, distribution_sum / NULLIF(distribution_count, 0)) AS vital_value
  FROM otel_metrics
  WHERE project_id='{{project_id}}'
    AND metric_name IN ('browser.web_vital.lcp', 'browser.web_vital.inp', 'browser.web_vital.cls', 'browser.web_vital.fcp', 'browser.web_vital.ttfb', 'k6.browser_web_vital_lcp', 'k6.browser_web_vital_inp', 'k6.browser_web_vital_cls', 'k6.browser_web_vital_fcp', 'k6.browser_web_vital_ttfb')
    {{time_filter}}
)
SELECT
  replace(replace(metric_name, 'browser.web_vital.', ''), 'k6.browser_web_vital_', '') AS vital,
  ROUND((approx_percentile(0.75, percentile_agg(vital_value)))::numeric, 2)::text AS p75,
  COUNT(*)::text AS samples,
  COUNT(DISTINCT vital_samples.session_id)::text AS sessions
FROM vital_samples
INNER JOIN endpoint_sessions ON endpoint_sessions.session_id = vital_samples.session_id
WHERE vital_value IS NOT NULL
GROUP BY metric_name
ORDER BY COUNT(*) DESC

hashes[*]=="37bdca0d" AND (resource.telemetry.sdk.language in ("webjs", "javascript", "js") or resource.user_agent.original != "" or name in ("documentLoad", "documentFetch") or name startswith "Pageview ")