-- State of Store Speed 2026: one row per US-audience store origin (HTTP Archive mobile root page x CrUX US phone)
WITH aud AS (
  SELECT NET.HOST(origin) AS host,
    ARRAY_AGG(STRUCT(origin, rank, p75_lcp, p75_fcp, p75_ttfb, p75_inp, p75_cls, fast_lcp, slow_lcp, fast_inp, slow_inp, small_cls, large_cls, fast_ttfb, slow_ttfb)
      ORDER BY STARTS_WITH(origin,'https://') DESC, rank ASC LIMIT 1)[OFFSET(0)] AS c
  FROM `chrome-ux-report.materialized.country_summary`
  WHERE yyyymm = 202609 AND country_code = 'us' AND device = 'phone'
  GROUP BY host
), tech AS (
  SELECT NET.HOST(page) AS host, page, rank AS ha_rank,
    (SELECT STRING_AGG(t.technology, '|') FROM UNNEST(technologies) t WHERE t.technology IN ('WooCommerce','Shopify','Magento','PrestaShop','BigCommerce')) AS platforms,
    (SELECT i FROM UNNEST(technologies) t, UNNEST(t.info) i WHERE t.technology='WooCommerce' AND REGEXP_CONTAINS(i, r'^[0-9]+[.]') LIMIT 1) AS woo_v,
    (SELECT i FROM UNNEST(technologies) t, UNNEST(t.info) i WHERE t.technology='WordPress' AND REGEXP_CONTAINS(i, r'^[0-9]+[.]') LIMIT 1) AS wp_v,
    (SELECT i FROM UNNEST(technologies) t, UNNEST(t.info) i WHERE t.technology='PHP' AND REGEXP_CONTAINS(i, r'^[0-9]+[.]') LIMIT 1) AS php_v,
    (SELECT COUNT(*) FROM UNNEST(technologies) t WHERE t.technology='PHP') AS php_seen,
    (SELECT i FROM UNNEST(technologies) t, UNNEST(t.info) i WHERE t.technology='PrestaShop' AND REGEXP_CONTAINS(i, r'^[0-9]+[.]') LIMIT 1) AS ps_v,
    (SELECT i FROM UNNEST(technologies) t, UNNEST(t.info) i WHERE t.technology='Magento' AND REGEXP_CONTAINS(i, r'^[0-9]+[.]?') LIMIT 1) AS mag_v,
    (SELECT COUNT(DISTINCT t.technology) FROM UNNEST(technologies) t, UNNEST(t.categories) cat WHERE cat='WordPress plugins') AS wp_plugins,
    (SELECT COUNT(DISTINCT t.technology) FROM UNNEST(technologies) t) AS techs,
    (SELECT STRING_AGG(DISTINCT t.technology, '|') FROM UNNEST(technologies) t, UNNEST(t.categories) cat WHERE cat IN ('Hosting','PaaS','IaaS')) AS hosting,
    (SELECT STRING_AGG(DISTINCT t.technology, '|') FROM UNNEST(technologies) t, UNNEST(t.categories) cat WHERE cat = 'CDN') AS cdn,
    (SELECT STRING_AGG(DISTINCT t.technology, '|') FROM UNNEST(technologies) t, UNNEST(t.categories) cat WHERE cat IN ('Caching','Performance')) AS caching,
    SAFE_CAST(JSON_VALUE(summary, '$.bytesTotal') AS INT64) AS bytes_total,
    SAFE_CAST(JSON_VALUE(summary, '$.reqTotal') AS INT64) AS req_total,
    SAFE_CAST(JSON_VALUE(summary, '$.bytesJS') AS INT64) AS bytes_js,
    SAFE_CAST(JSON_VALUE(summary, '$.reqJS') AS INT64) AS req_js,
    SAFE_CAST(JSON_VALUE(summary, '$.bytesImg') AS INT64) AS bytes_img,
    SAFE_CAST(JSON_VALUE(summary, '$.reqThirdParty') AS INT64) AS req_3p
  FROM `httparchive.crawl.pages`
  WHERE date = '2026-09-01' AND client = 'mobile' AND is_root_page
    AND EXISTS (SELECT 1 FROM UNNEST(technologies) t WHERE t.technology IN ('WooCommerce','Shopify','Magento','PrestaShop','BigCommerce'))
)
SELECT tech.*, c.origin, c.rank AS crux_rank, c.p75_lcp, c.p75_fcp, c.p75_ttfb, c.p75_inp, c.p75_cls,
  c.fast_lcp, c.slow_lcp, c.fast_inp, c.slow_inp, c.small_cls, c.large_cls, c.fast_ttfb, c.slow_ttfb
FROM tech JOIN aud USING (host)