Media source

SELECT 1 AS ok FROM site_metric_bags LIMIT 0;

WITH flat AS (
                SELECT je.key AS cat, TRY_CAST(json_extract(je.value, '$.n') AS DOUBLE) AS n, TRY_CAST(json_extract(je.value, '$.size') AS DOUBLE) AS size, crux."desktop".lcp AS cwv_val
                FROM sites s, json_each(to_json(s.m.media.source)) AS je
                WHERE m.media.source IS NOT NULL
                  AND je.value IS NOT NULL
                  AND CAST(je.value AS VARCHAR) NOT IN ('null', 'NULL')
                  AND regexp_matches(je.key, '^[a-zA-Z0-9_+.\-]+$')
            ),
            totals AS (
                SELECT cat, SUM(n) AS fleet_n, SUM(size) AS fleet_size,
                    COUNT(*) FILTER (WHERE n IS NOT NULL) AS sample,
                    COUNT(*) FILTER (WHERE n > 0) AS with_any,
                    quantile_disc(n, 0.95) AS p95
                FROM flat GROUP BY cat HAVING COUNT(*) >= (SELECT COUNT(*) FROM sites WHERE m.media.source IS NOT NULL) * 0.005
            ),
            widths AS (
                SELECT *, GREATEST(1, CAST(round(COALESCE(p95, 0)) AS INTEGER)) AS cap,
                    LEAST(GREATEST(1, CAST(round(COALESCE(p95, 0)) AS INTEGER)), 12 - 1) AS regular_buckets
                FROM totals
            ),
            widths2 AS (
                SELECT *, GREATEST(1, CAST(ceil(cap * 1.0 / regular_buckets) AS INTEGER)) AS width FROM widths
            ),
            binned AS (
                SELECT f.cat, f.cwv_val, w.fleet_n, w.fleet_size, w.sample, w.with_any, w.width, w.regular_buckets,
                    CASE WHEN f.n IS NULL THEN NULL
                        ELSE LEAST(CAST(FLOOR(f.n * 1.0 / w.width) AS INTEGER), w.regular_buckets) END AS bucket_idx
                FROM flat f JOIN widths2 w ON f.cat = w.cat
            )
            SELECT cat, fleet_n, fleet_size, sample, with_any, width, regular_buckets, bucket_idx,
                COUNT(*) AS n, quantile_disc(cwv_val, 0.5) AS median,
                COUNT(*) FILTER (WHERE cwv_val IS NOT NULL) AS total_cwv,
                COUNT(*) FILTER (WHERE cwv_val IS NOT NULL AND cwv_val <= 2500) AS good
            FROM binned WHERE bucket_idx IS NOT NULL
            GROUP BY cat, fleet_n, fleet_size, sample, with_any, width, regular_buckets, bucket_idx
            ORDER BY cat, bucket_idx;