Consent platforms

SELECT 1 AS ok FROM site_metric_bags LIMIT 0;

WITH flat AS (
                SELECT CAST(p.key AS VARCHAR) AS cat, 1.0::DOUBLE AS n, NULL::DOUBLE AS size, s.crux."all".lcp AS cwv_val
                FROM sites s, UNNEST(s.entities.providers) AS u(p)
                WHERE p.key IS NOT NULL
                  AND CAST(p.key AS VARCHAR) <> ''
                  AND p.category = 'consent'
                  AND regexp_matches(CAST(p.key AS VARCHAR), '^[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
            ),
            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;