-- BigQuery query for HTTP Archive CSS + HTML observables
-- Generated: 2026-02-18
-- Scans parsed_css ONCE for CSS properties, selectors, and at-rules
-- Scans pages for HTML element usage via custom_metrics.element_count
-- Uses efficient single-scan approach with REGEXP_EXTRACT_ALL on JSON strings

DECLARE crawl_date DATE DEFAULT '2026-01-01';

-- =============================================================================
-- LOOKUP TABLES: observable names and their web-feature ID mappings
-- =============================================================================
WITH css_property_lookup AS (
  SELECT * FROM UNNEST([
    STRUCT('align-content' AS observable, 'flexbox,grid,align-content-block' AS feature_ids),
    STRUCT('align-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
    STRUCT('align-self' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
    STRUCT('display' AS observable, 'flexbox,grid,display-animation,mathml,two-value-display' AS feature_ids),
    STRUCT('flex' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-basis' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-direction' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-flow' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-grow' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-shrink' AS observable, 'flexbox' AS feature_ids),
    STRUCT('flex-wrap' AS observable, 'flexbox' AS feature_ids),
    STRUCT('justify-content' AS observable, 'flexbox,grid' AS feature_ids),
    STRUCT('justify-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
    STRUCT('order' AS observable, 'flexbox' AS feature_ids),
    STRUCT('place-content' AS observable, 'flexbox,grid' AS feature_ids),
    STRUCT('place-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
    STRUCT('place-self' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
    STRUCT('position' AS observable, 'flexbox' AS feature_ids),
    STRUCT('outline' AS observable, 'outline' AS feature_ids),
    STRUCT('column-gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
    STRUCT('gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
    STRUCT('row-gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
    STRUCT('appearance' AS observable, 'appearance,customizable-select' AS feature_ids),
    STRUCT('background-clip' AS observable, 'background-clip-text' AS feature_ids),
    STRUCT('clip-path' AS observable, 'clip-path,clip-path-boxes' AS feature_ids),
    STRUCT('grid' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-area' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-auto-columns' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-auto-flow' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-auto-rows' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-column' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-column-end' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-column-start' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-row' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-row-end' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-row-start' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-template' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-template-areas' AS observable, 'grid' AS feature_ids),
    STRUCT('grid-template-columns' AS observable, 'grid,subgrid' AS feature_ids),
    STRUCT('grid-template-rows' AS observable, 'grid,subgrid' AS feature_ids),
    STRUCT('justify-self' AS observable, 'grid,anchor-positioning' AS feature_ids),
    STRUCT('scrollbar-width' AS observable, 'scrollbar-width' AS feature_ids),
    STRUCT('will-change' AS observable, 'will-change' AS feature_ids),
    STRUCT('text-indent' AS observable, 'text-indent' AS feature_ids),
    STRUCT('mask' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-clip' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-composite' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-image' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-mode' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-origin' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-position' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-repeat' AS observable, 'masks' AS feature_ids),
    STRUCT('mask-size' AS observable, 'masks' AS feature_ids),
    STRUCT('block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('border-block' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-end' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-end-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-end-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-end-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-start' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-start-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-start-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-start-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-block-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-end-end-radius' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-end-start-radius' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-end' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-end-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-end-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-end-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-start' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-start-color' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-start-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-start-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-style' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-inline-width' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-start-end-radius' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('border-start-start-radius' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('clear' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('float' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-block' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-block-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-block-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-inline' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-inline-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('inset-inline-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-block' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-block-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-block-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-inline' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-inline-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('margin-inline-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('max-block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('max-inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('min-block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('min-inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
    STRUCT('overflow-block' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('overflow-inline' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-block' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-block-end' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-block-start' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-inline' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-inline-end' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('padding-inline-start' AS observable, 'logical-properties' AS feature_ids),
    STRUCT('forced-color-adjust' AS observable, 'forced-colors' AS feature_ids),
    STRUCT('aspect-ratio' AS observable, 'aspect-ratio' AS feature_ids),
    STRUCT('backdrop-filter' AS observable, 'backdrop-filter' AS feature_ids),
    STRUCT('text-wrap' AS observable, 'text-wrap,text-wrap-balance,text-wrap-pretty' AS feature_ids),
    STRUCT('overflow-x' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
    STRUCT('overflow-y' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
    STRUCT('overflow' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
    STRUCT('color-scheme' AS observable, 'color-scheme' AS feature_ids),
    STRUCT('rotate' AS observable, 'individual-transforms' AS feature_ids),
    STRUCT('scale' AS observable, 'individual-transforms' AS feature_ids),
    STRUCT('translate' AS observable, 'individual-transforms' AS feature_ids),
    STRUCT('scrollbar-color' AS observable, 'scrollbar-color' AS feature_ids),
    STRUCT('text-underline-offset' AS observable, 'text-underline-offset' AS feature_ids),
    STRUCT('scroll-margin' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-block' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-block-end' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-block-start' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-bottom' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-inline' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-inline-end' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-inline-start' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-left' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-right' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-margin-top' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-block' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-block-end' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-block-start' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-bottom' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-inline' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-inline-end' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-inline-start' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-left' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-right' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-padding-top' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-snap-align' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-snap-stop' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('scroll-snap-type' AS observable, 'scroll-snap' AS feature_ids),
    STRUCT('container' AS observable, 'container-queries' AS feature_ids),
    STRUCT('container-name' AS observable, 'container-queries' AS feature_ids),
    STRUCT('container-type' AS observable, 'container-queries,container-scroll-state-queries' AS feature_ids),
    STRUCT('scrollbar-gutter' AS observable, 'scrollbar-gutter' AS feature_ids),
    STRUCT('hyphens' AS observable, 'hyphens' AS feature_ids),
    STRUCT('quotes' AS observable, 'quotes' AS feature_ids),
    STRUCT('content-visibility' AS observable, 'content-visibility,display-animation' AS feature_ids),
    STRUCT('contain-intrinsic-block-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
    STRUCT('contain-intrinsic-height' AS observable, 'contain-intrinsic-size' AS feature_ids),
    STRUCT('contain-intrinsic-inline-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
    STRUCT('contain-intrinsic-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
    STRUCT('contain-intrinsic-width' AS observable, 'contain-intrinsic-size' AS feature_ids),
    STRUCT('border-image' AS observable, 'border-image' AS feature_ids),
    STRUCT('border-image-outset' AS observable, 'border-image' AS feature_ids),
    STRUCT('border-image-repeat' AS observable, 'border-image' AS feature_ids),
    STRUCT('border-image-slice' AS observable, 'border-image' AS feature_ids),
    STRUCT('border-image-source' AS observable, 'border-image' AS feature_ids),
    STRUCT('border-image-width' AS observable, 'border-image' AS feature_ids),
    STRUCT('line-break' AS observable, 'line-break' AS feature_ids),
    STRUCT('transform-box' AS observable, 'transform-box' AS feature_ids),
    STRUCT('animation-range' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('animation-range-end' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('animation-range-start' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('animation-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('animation' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('scroll-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('scroll-timeline-axis' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('scroll-timeline-name' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('timeline-scope' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('view-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('view-timeline-axis' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('view-timeline-inset' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('view-timeline-name' AS observable, 'scroll-driven-animations' AS feature_ids),
    STRUCT('interpolate-size' AS observable, 'interpolate-size' AS feature_ids),
    STRUCT('print-color-adjust' AS observable, 'print-color-adjust' AS feature_ids),
    STRUCT('box-decoration-break' AS observable, 'box-decoration-break' AS feature_ids),
    STRUCT('accent-color' AS observable, 'accent-color' AS feature_ids),
    STRUCT('font-optical-sizing' AS observable, 'font-optical-sizing' AS feature_ids),
    STRUCT('text-spacing-trim' AS observable, 'text-spacing-trim' AS feature_ids),
    STRUCT('font-size-adjust' AS observable, 'font-size-adjust' AS feature_ids),
    STRUCT('font-synthesis' AS observable, 'font-synthesis' AS feature_ids),
    STRUCT('field-sizing' AS observable, 'field-sizing' AS feature_ids),
    STRUCT('paint-order' AS observable, 'paint-order' AS feature_ids),
    STRUCT('font-size' AS observable, 'mathml' AS feature_ids),
    STRUCT('math-depth' AS observable, 'mathml' AS feature_ids),
    STRUCT('math-shift' AS observable, 'mathml' AS feature_ids),
    STRUCT('math-style' AS observable, 'mathml' AS feature_ids),
    STRUCT('text-transform' AS observable, 'mathml' AS feature_ids),
    STRUCT('text-box' AS observable, 'text-box' AS feature_ids),
    STRUCT('text-box-edge' AS observable, 'text-box' AS feature_ids),
    STRUCT('text-box-trim' AS observable, 'text-box' AS feature_ids),
    STRUCT('white-space-collapse' AS observable, 'white-space-collapse' AS feature_ids),
    STRUCT('transition-behavior' AS observable, 'transition-behavior' AS feature_ids),
    STRUCT('transition' AS observable, 'transition-behavior' AS feature_ids),
    STRUCT('view-transition-name' AS observable, 'view-transitions' AS feature_ids),
    STRUCT('counter-set' AS observable, 'counter-set' AS feature_ids),
    STRUCT('font-language-override' AS observable, 'font-language-override' AS feature_ids),
    STRUCT('background-image' AS observable, 'image-set' AS feature_ids),
    STRUCT('content' AS observable, 'image-set' AS feature_ids),
    STRUCT('offset' AS observable, 'motion-path' AS feature_ids),
    STRUCT('offset-anchor' AS observable, 'motion-path' AS feature_ids),
    STRUCT('offset-distance' AS observable, 'motion-path' AS feature_ids),
    STRUCT('offset-path' AS observable, 'motion-path' AS feature_ids),
    STRUCT('offset-position' AS observable, 'motion-path' AS feature_ids),
    STRUCT('offset-rotate' AS observable, 'motion-path' AS feature_ids),
    STRUCT('animation-composition' AS observable, 'animation-composition' AS feature_ids),
    STRUCT('anchor-name' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('anchor-scope' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('bottom' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('height' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('left' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('margin-bottom' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('margin-left' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('margin-right' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('margin-top' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('margin' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('max-height' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('max-width' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('min-height' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('min-width' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-anchor' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-area' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-try' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-try-fallbacks' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-try-order' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('position-visibility' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('right' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('top' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('width' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('font-variant-alternates' AS observable, 'font-variant-alternates' AS feature_ids),
    STRUCT('text-justify' AS observable, 'text-justify' AS feature_ids),
    STRUCT('hyphenate-character' AS observable, 'hyphenate-character' AS feature_ids),
    STRUCT('font-variant-position' AS observable, 'font-variant-position' AS feature_ids),
    STRUCT('text-emphasis' AS observable, 'text-emphasis' AS feature_ids),
    STRUCT('text-emphasis-color' AS observable, 'text-emphasis' AS feature_ids),
    STRUCT('text-emphasis-position' AS observable, 'text-emphasis' AS feature_ids),
    STRUCT('text-emphasis-style' AS observable, 'text-emphasis' AS feature_ids),
    STRUCT('image-orientation' AS observable, 'image-orientation' AS feature_ids),
    STRUCT('ruby-position' AS observable, 'ruby-position' AS feature_ids),
    STRUCT('ruby-align' AS observable, 'ruby-align' AS feature_ids),
    STRUCT('hyphenate-limit-chars' AS observable, 'hyphenate-limit-chars' AS feature_ids),
    STRUCT('font-synthesis-weight' AS observable, 'font-synthesis-weight' AS feature_ids),
    STRUCT('text-wrap-style' AS observable, 'text-wrap-style' AS feature_ids),
    STRUCT('initial-letter' AS observable, 'initial-letter' AS feature_ids),
    STRUCT('view-transition-class' AS observable, 'view-transition-class' AS feature_ids),
    STRUCT('font-variant-emoji' AS observable, 'font-variant-emoji' AS feature_ids),
    STRUCT('font-palette' AS observable, 'font-palette,font-palette-animation' AS feature_ids),
    STRUCT('font-family' AS observable, 'font-family-math' AS feature_ids),
    STRUCT('scroll-marker-group' AS observable, 'scroll-markers' AS feature_ids),
    STRUCT('word-break' AS observable, 'word-break-auto-phrase' AS feature_ids),
    STRUCT('overlay' AS observable, 'overlay' AS feature_ids),
    STRUCT('object-view-box' AS observable, 'object-view-box' AS feature_ids),
    STRUCT('contain' AS observable, 'contain-inline-size' AS feature_ids),
    STRUCT('reading-flow' AS observable, 'reading-flow' AS feature_ids),
    STRUCT('reading-order' AS observable, 'reading-flow' AS feature_ids),
    STRUCT('font-synthesis-style' AS observable, 'font-synthesis-style' AS feature_ids),
    STRUCT('direction' AS observable, 'vertical-form-controls' AS feature_ids),
    STRUCT('writing-mode' AS observable, 'vertical-form-controls' AS feature_ids),
    STRUCT('interactivity' AS observable, 'interactivity' AS feature_ids),
    STRUCT('baseline-source' AS observable, 'baseline-source' AS feature_ids),
    STRUCT('scroll-initial-target' AS observable, 'scroll-initial-target' AS feature_ids),
    STRUCT('font-synthesis-small-caps' AS observable, 'font-synthesis-small-caps' AS feature_ids)
  ])
),

css_selector_lookup AS (
  SELECT * FROM UNNEST([
    STRUCT('not' AS observable, 'not' AS feature_ids, ':not' AS search_pattern),
    STRUCT('slotted' AS observable, 'slot' AS feature_ids, '::slotted' AS search_pattern),
    STRUCT('focus-visible' AS observable, 'focus-visible' AS feature_ids, ':focus-visible' AS search_pattern),
    STRUCT('has' AS observable, 'has' AS feature_ids, ':has' AS search_pattern),
    STRUCT('where' AS observable, 'where' AS feature_ids, ':where' AS search_pattern),
    STRUCT('is' AS observable, 'is' AS feature_ids, ':is' AS search_pattern),
    STRUCT('marker' AS observable, 'marker' AS feature_ids, '::marker' AS search_pattern),
    STRUCT('nesting' AS observable, 'nesting' AS feature_ids, '&' AS search_pattern),
    STRUCT('file-selector-button' AS observable, 'file-selector-button' AS feature_ids, '::file-selector-button' AS search_pattern),
    STRUCT('dir' AS observable, 'dir-pseudo' AS feature_ids, ':dir' AS search_pattern),
    STRUCT('cue' AS observable, 'webvtt' AS feature_ids, '::cue' AS search_pattern),
    STRUCT('nth-child' AS observable, 'nth-child-of' AS feature_ids, ':nth-child' AS search_pattern),
    STRUCT('nth-last-child' AS observable, 'nth-child-of' AS feature_ids, ':nth-last-child' AS search_pattern),
    STRUCT('modal' AS observable, 'modal' AS feature_ids, ':modal' AS search_pattern),
    STRUCT('autofill' AS observable, 'autofill' AS feature_ids, ':autofill' AS search_pattern),
    STRUCT('backdrop' AS observable, 'popover' AS feature_ids, '::backdrop' AS search_pattern),
    STRUCT('popover-open' AS observable, 'popover' AS feature_ids, ':popover-open' AS search_pattern),
    STRUCT('view-transition' AS observable, 'view-transitions' AS feature_ids, '::view-transition' AS search_pattern),
    STRUCT('view-transition-group' AS observable, 'view-transitions' AS feature_ids, '::view-transition-group' AS search_pattern),
    STRUCT('view-transition-image-pair' AS observable, 'view-transitions' AS feature_ids, '::view-transition-image-pair' AS search_pattern),
    STRUCT('view-transition-new' AS observable, 'view-transitions' AS feature_ids, '::view-transition-new' AS search_pattern),
    STRUCT('view-transition-old' AS observable, 'view-transitions' AS feature_ids, '::view-transition-old' AS search_pattern),
    STRUCT('grammar-error' AS observable, 'spelling-grammar-error' AS feature_ids, '::grammar-error' AS search_pattern),
    STRUCT('spelling-error' AS observable, 'spelling-grammar-error' AS feature_ids, '::spelling-error' AS search_pattern),
    STRUCT('details-content' AS observable, 'details-content' AS feature_ids, '::details-content' AS search_pattern),
    STRUCT('user-invalid' AS observable, 'user-pseudos' AS feature_ids, ':user-invalid' AS search_pattern),
    STRUCT('user-valid' AS observable, 'user-pseudos' AS feature_ids, ':user-valid' AS search_pattern),
    STRUCT('future' AS observable, 'time-relative-selectors' AS feature_ids, ':future' AS search_pattern),
    STRUCT('past' AS observable, 'time-relative-selectors' AS feature_ids, ':past' AS search_pattern),
    STRUCT('state' AS observable, 'state' AS feature_ids, ':state' AS search_pattern),
    STRUCT('active-view-transition' AS observable, 'active-view-transition' AS feature_ids, ':active-view-transition' AS search_pattern),
    STRUCT('active-view-transition-type' AS observable, 'active-view-transition' AS feature_ids, ':active-view-transition-type' AS search_pattern),
    STRUCT('target-text' AS observable, 'target-text' AS feature_ids, '::target-text' AS search_pattern),
    STRUCT('highlight' AS observable, 'highlight' AS feature_ids, '::highlight' AS search_pattern),
    STRUCT('scroll-marker' AS observable, 'scroll-markers' AS feature_ids, '::scroll-marker' AS search_pattern),
    STRUCT('scroll-marker-group' AS observable, 'scroll-markers' AS feature_ids, '::scroll-marker-group' AS search_pattern),
    STRUCT('picture-in-picture' AS observable, 'picture-in-picture' AS feature_ids, ':picture-in-picture' AS search_pattern),
    STRUCT('checkmark' AS observable, 'customizable-select' AS feature_ids, '::checkmark' AS search_pattern),
    STRUCT('picker' AS observable, 'customizable-select' AS feature_ids, '::picker' AS search_pattern),
    STRUCT('picker-icon' AS observable, 'customizable-select' AS feature_ids, '::picker-icon' AS search_pattern),
    STRUCT('scroll-button' AS observable, 'scroll-buttons' AS feature_ids, '::scroll-button' AS search_pattern),
    STRUCT('xr-overlay' AS observable, 'webxr-dom-overlays' AS feature_ids, ':xr-overlay' AS search_pattern),
    STRUCT('column' AS observable, 'column-pseudo' AS feature_ids, '::column' AS search_pattern),
    STRUCT('has-slotted' AS observable, 'has-slotted' AS feature_ids, ':has-slotted' AS search_pattern),
    STRUCT('selection' AS observable, 'text-decoration-selection' AS feature_ids, '::selection' AS search_pattern)
  ])
),

css_atrule_lookup AS (
  SELECT * FROM UNNEST([
    STRUCT('media' AS observable, 'forced-colors,prefers-contrast,display-mode,media-query-range-syntax,dynamic-range,prefers-reduced-transparency,device-posture,update,scripting,overflow' AS feature_ids),
    STRUCT('container' AS observable, 'container-queries,container-style-queries,container-scroll-state-queries' AS feature_ids),
    STRUCT('view-transition' AS observable, 'cross-document-view-transitions' AS feature_ids),
    STRUCT('font-face' AS observable, 'font-metric-overrides,font-size-adjust' AS feature_ids),
    STRUCT('property' AS observable, 'registered-custom-properties' AS feature_ids),
    STRUCT('import' AS observable, 'cascade-layers' AS feature_ids),
    STRUCT('layer' AS observable, 'cascade-layers' AS feature_ids),
    STRUCT('starting-style' AS observable, 'starting-style' AS feature_ids),
    STRUCT('page' AS observable, 'page-orientation' AS feature_ids),
    STRUCT('position-try' AS observable, 'anchor-positioning' AS feature_ids),
    STRUCT('font-feature-values' AS observable, 'font-variant-alternates' AS feature_ids),
    STRUCT('counter-style' AS observable, 'counter-style' AS feature_ids),
    STRUCT('scope' AS observable, 'scope' AS feature_ids),
    STRUCT('font-palette-values' AS observable, 'font-palette' AS feature_ids)
  ])
),

html_element_lookup AS (
  SELECT * FROM UNNEST([
    STRUCT('slot' AS observable, 'slot' AS feature_ids),
    STRUCT('canvas' AS observable, 'canvas' AS feature_ids),
    STRUCT('a' AS observable, 'referrer-policy,attribution-reporting,fencedframe,scroll-to-text-fragment' AS feature_ids),
    STRUCT('area' AS observable, 'referrer-policy,attribution-reporting' AS feature_ids),
    STRUCT('iframe' AS observable, 'referrer-policy,attribution-reporting,storage-access,screen-wake-lock,compute-pressure,accelerometer,window-management,iframe-credentialless,idle-detection,web-otp,picture-in-picture,web-midi,webusb,serial,webhid,local-fonts,gamepad,web-bluetooth' AS feature_ids),
    STRUCT('img' AS observable, 'referrer-policy,fetch-priority,aspect-ratio,attribution-reporting,sizes-auto' AS feature_ids),
    STRUCT('link' AS observable, 'referrer-policy,fetch-priority,manifest,blocking-render,link-rel-expect,compression-dictionary-transport' AS feature_ids),
    STRUCT('script' AS observable, 'referrer-policy,fetch-priority,js-modules,attribution-reporting,speculation-rules,blocking-render,import-maps' AS feature_ids),
    STRUCT('video' AS observable, 'aspect-ratio,remote-playback,picture-in-picture' AS feature_ids),
    STRUCT('template' AS observable, 'template,declarative-shadow-dom' AS feature_ids),
    STRUCT('meta' AS observable, 'color-scheme,meta-application-title' AS feature_ids),
    STRUCT('style' AS observable, 'blocking-render' AS feature_ids),
    STRUCT('dialog' AS observable, 'dialog,dialog-closedby' AS feature_ids),
    STRUCT('button' AS observable, 'popover,anchor-positioning' AS feature_ids),
    STRUCT('input' AS observable, 'popover,anchor-positioning' AS feature_ids),
    STRUCT('datalist' AS observable, 'datalist' AS feature_ids),
    STRUCT('fencedframe' AS observable, 'fencedframe' AS feature_ids),
    STRUCT('audio' AS observable, 'remote-playback' AS feature_ids),
    STRUCT('search' AS observable, 'search' AS feature_ids),
    STRUCT('details' AS observable, 'details-name' AS feature_ids),
    STRUCT('selectedcontent' AS observable, 'customizable-select' AS feature_ids)
  ])
),

-- =============================================================================
-- SINGLE SCAN of parsed_css: extract properties, selector blocks, and at-rule types
-- Converts the css JSON column to a string ONCE per row, then extracts all three
-- categories in a single pass over the table.
-- =============================================================================
css_raw AS (
  SELECT
    page,
    TO_JSON_STRING(css) AS css_str
  FROM `httparchive.crawl.parsed_css`
  WHERE date = crawl_date
    AND client = 'desktop'
    AND is_root_page = TRUE
),

-- Extract distinct CSS properties per page
page_css_properties AS (
  SELECT page, prop
  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"property":"([^"]+)"')) AS prop
  GROUP BY page, prop
),

-- Extract selector blocks per page (the content inside "selectors":[...])
-- Each sel_block is a string like '"h1","h2",".foo:not(.bar)"'
page_css_selectors AS (
  SELECT page, sel_block
  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"selectors":\[([^\]]+)\]')) AS sel_block
  GROUP BY page, sel_block
),

-- Extract distinct at-rule types per page
page_css_atrules AS (
  SELECT page, atrule
  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"type":"([^"]+)"')) AS atrule
  GROUP BY page, atrule
),

-- =============================================================================
-- CSS PROPERTY RESULTS: join extracted properties with lookup
-- =============================================================================
css_property_results AS (
  SELECT
    lk.feature_ids,
    lk.observable,
    'css_property' AS type,
    COUNT(DISTINCT pcp.page) AS pages_using
  FROM css_property_lookup lk
  LEFT JOIN page_css_properties pcp ON pcp.prop = lk.observable
  GROUP BY lk.feature_ids, lk.observable
),

-- =============================================================================
-- CSS SELECTOR RESULTS: search selector blocks for patterns
-- For each page, check if any selector block contains the search pattern
-- =============================================================================
css_selector_results AS (
  SELECT
    lk.feature_ids,
    lk.observable,
    'css_selector' AS type,
    COUNT(DISTINCT pcs.page) AS pages_using
  FROM css_selector_lookup lk
  LEFT JOIN page_css_selectors pcs
    ON pcs.sel_block LIKE CONCAT('%', lk.search_pattern, '%')
  GROUP BY lk.feature_ids, lk.observable
),

-- =============================================================================
-- CSS AT-RULE RESULTS: join extracted at-rule types with lookup
-- =============================================================================
css_atrule_results AS (
  SELECT
    lk.feature_ids,
    lk.observable,
    'css_atrule' AS type,
    COUNT(DISTINCT pca.page) AS pages_using
  FROM css_atrule_lookup lk
  LEFT JOIN page_css_atrules pca ON pca.atrule = lk.observable
  GROUP BY lk.feature_ids, lk.observable
),

-- =============================================================================
-- HTML ELEMENTS: extract from custom_metrics.element_count in pages table
-- element_count is a JSON object like {"div": 500, "span": 200, "dialog": 1}
-- =============================================================================
page_html_elements AS (
  SELECT
    page,
    LOWER(elem_name) AS elem_name
  FROM
    `httparchive.crawl.pages`,
    UNNEST(
      REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(custom_metrics.element_count),
        r'"([^"]+)"\s*:'
      )
    ) AS elem_name
  WHERE date = crawl_date
    AND client = 'desktop'
    AND is_root_page = TRUE
  GROUP BY page, elem_name
),

html_element_results AS (
  SELECT
    lk.feature_ids,
    lk.observable,
    'html_element' AS type,
    COUNT(DISTINCT phe.page) AS pages_using
  FROM html_element_lookup lk
  LEFT JOIN page_html_elements phe ON phe.elem_name = lk.observable
  GROUP BY lk.feature_ids, lk.observable
),

-- =============================================================================
-- TOTAL PAGES: for computing percentages
-- =============================================================================
total_css_pages AS (
  SELECT COUNT(DISTINCT page) AS total_pages
  FROM `httparchive.crawl.parsed_css`
  WHERE date = crawl_date
    AND client = 'desktop'
    AND is_root_page = TRUE
),

total_html_pages AS (
  SELECT COUNT(DISTINCT page) AS total_pages
  FROM `httparchive.crawl.pages`
  WHERE date = crawl_date
    AND client = 'desktop'
    AND is_root_page = TRUE
),

-- =============================================================================
-- COMBINE all results
-- =============================================================================
all_results AS (
  SELECT feature_ids, observable, type, pages_using FROM css_property_results
  UNION ALL
  SELECT feature_ids, observable, type, pages_using FROM css_selector_results
  UNION ALL
  SELECT feature_ids, observable, type, pages_using FROM css_atrule_results
  UNION ALL
  SELECT feature_ids, observable, type, pages_using FROM html_element_results
)

SELECT
  r.feature_ids,
  r.observable,
  r.type,
  r.pages_using,
  CASE
    WHEN r.type = 'html_element' THEN th.total_pages
    ELSE tc.total_pages
  END AS total_pages,
  SAFE_DIVIDE(r.pages_using * 100.0,
    CASE
      WHEN r.type = 'html_element' THEN th.total_pages
      ELSE tc.total_pages
    END
  ) AS pct
FROM all_results r
CROSS JOIN total_css_pages tc
CROSS JOIN total_html_pages th
ORDER BY pct DESC;
