data/httparchive-query.sql at main · burrito.space/servo-readiness-report

burrito.space / servo-readiness-report
fork atom
this repo has no description
fork atom
servo-readiness-report / data / httparchive-query.sql
at main 546 lines 36 kB view raw
wrap content
burrito.tngl.sh Add HTTP Archive usage pipeline, dashboard, and supporting documents 4w ago
3c4ff5fd
  1-- BigQuery query for HTTP Archive CSS + HTML observables
  2-- Generated: 2026-02-18
  3-- Scans parsed_css ONCE for CSS properties, selectors, and at-rules
  4-- Scans pages for HTML element usage via custom_metrics.element_count
  5-- Uses efficient single-scan approach with REGEXP_EXTRACT_ALL on JSON strings
  6
  7DECLARE crawl_date DATE DEFAULT '2026-01-01';
  8
  9-- =============================================================================
 10-- LOOKUP TABLES: observable names and their web-feature ID mappings
 11-- =============================================================================
 12WITH css_property_lookup AS (
 13  SELECT * FROM UNNEST([
 14    STRUCT('align-content' AS observable, 'flexbox,grid,align-content-block' AS feature_ids),
 15    STRUCT('align-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
 16    STRUCT('align-self' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
 17    STRUCT('display' AS observable, 'flexbox,grid,display-animation,mathml,two-value-display' AS feature_ids),
 18    STRUCT('flex' AS observable, 'flexbox' AS feature_ids),
 19    STRUCT('flex-basis' AS observable, 'flexbox' AS feature_ids),
 20    STRUCT('flex-direction' AS observable, 'flexbox' AS feature_ids),
 21    STRUCT('flex-flow' AS observable, 'flexbox' AS feature_ids),
 22    STRUCT('flex-grow' AS observable, 'flexbox' AS feature_ids),
 23    STRUCT('flex-shrink' AS observable, 'flexbox' AS feature_ids),
 24    STRUCT('flex-wrap' AS observable, 'flexbox' AS feature_ids),
 25    STRUCT('justify-content' AS observable, 'flexbox,grid' AS feature_ids),
 26    STRUCT('justify-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
 27    STRUCT('order' AS observable, 'flexbox' AS feature_ids),
 28    STRUCT('place-content' AS observable, 'flexbox,grid' AS feature_ids),
 29    STRUCT('place-items' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
 30    STRUCT('place-self' AS observable, 'flexbox,grid,anchor-positioning' AS feature_ids),
 31    STRUCT('position' AS observable, 'flexbox' AS feature_ids),
 32    STRUCT('outline' AS observable, 'outline' AS feature_ids),
 33    STRUCT('column-gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
 34    STRUCT('gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
 35    STRUCT('row-gap' AS observable, 'flexbox-gap,grid' AS feature_ids),
 36    STRUCT('appearance' AS observable, 'appearance,customizable-select' AS feature_ids),
 37    STRUCT('background-clip' AS observable, 'background-clip-text' AS feature_ids),
 38    STRUCT('clip-path' AS observable, 'clip-path,clip-path-boxes' AS feature_ids),
 39    STRUCT('grid' AS observable, 'grid' AS feature_ids),
 40    STRUCT('grid-area' AS observable, 'grid' AS feature_ids),
 41    STRUCT('grid-auto-columns' AS observable, 'grid' AS feature_ids),
 42    STRUCT('grid-auto-flow' AS observable, 'grid' AS feature_ids),
 43    STRUCT('grid-auto-rows' AS observable, 'grid' AS feature_ids),
 44    STRUCT('grid-column' AS observable, 'grid' AS feature_ids),
 45    STRUCT('grid-column-end' AS observable, 'grid' AS feature_ids),
 46    STRUCT('grid-column-start' AS observable, 'grid' AS feature_ids),
 47    STRUCT('grid-row' AS observable, 'grid' AS feature_ids),
 48    STRUCT('grid-row-end' AS observable, 'grid' AS feature_ids),
 49    STRUCT('grid-row-start' AS observable, 'grid' AS feature_ids),
 50    STRUCT('grid-template' AS observable, 'grid' AS feature_ids),
 51    STRUCT('grid-template-areas' AS observable, 'grid' AS feature_ids),
 52    STRUCT('grid-template-columns' AS observable, 'grid,subgrid' AS feature_ids),
 53    STRUCT('grid-template-rows' AS observable, 'grid,subgrid' AS feature_ids),
 54    STRUCT('justify-self' AS observable, 'grid,anchor-positioning' AS feature_ids),
 55    STRUCT('scrollbar-width' AS observable, 'scrollbar-width' AS feature_ids),
 56    STRUCT('will-change' AS observable, 'will-change' AS feature_ids),
 57    STRUCT('text-indent' AS observable, 'text-indent' AS feature_ids),
 58    STRUCT('mask' AS observable, 'masks' AS feature_ids),
 59    STRUCT('mask-clip' AS observable, 'masks' AS feature_ids),
 60    STRUCT('mask-composite' AS observable, 'masks' AS feature_ids),
 61    STRUCT('mask-image' AS observable, 'masks' AS feature_ids),
 62    STRUCT('mask-mode' AS observable, 'masks' AS feature_ids),
 63    STRUCT('mask-origin' AS observable, 'masks' AS feature_ids),
 64    STRUCT('mask-position' AS observable, 'masks' AS feature_ids),
 65    STRUCT('mask-repeat' AS observable, 'masks' AS feature_ids),
 66    STRUCT('mask-size' AS observable, 'masks' AS feature_ids),
 67    STRUCT('block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
 68    STRUCT('border-block' AS observable, 'logical-properties' AS feature_ids),
 69    STRUCT('border-block-color' AS observable, 'logical-properties' AS feature_ids),
 70    STRUCT('border-block-end' AS observable, 'logical-properties' AS feature_ids),
 71    STRUCT('border-block-end-color' AS observable, 'logical-properties' AS feature_ids),
 72    STRUCT('border-block-end-style' AS observable, 'logical-properties' AS feature_ids),
 73    STRUCT('border-block-end-width' AS observable, 'logical-properties' AS feature_ids),
 74    STRUCT('border-block-start' AS observable, 'logical-properties' AS feature_ids),
 75    STRUCT('border-block-start-color' AS observable, 'logical-properties' AS feature_ids),
 76    STRUCT('border-block-start-style' AS observable, 'logical-properties' AS feature_ids),
 77    STRUCT('border-block-start-width' AS observable, 'logical-properties' AS feature_ids),
 78    STRUCT('border-block-style' AS observable, 'logical-properties' AS feature_ids),
 79    STRUCT('border-block-width' AS observable, 'logical-properties' AS feature_ids),
 80    STRUCT('border-end-end-radius' AS observable, 'logical-properties' AS feature_ids),
 81    STRUCT('border-end-start-radius' AS observable, 'logical-properties' AS feature_ids),
 82    STRUCT('border-inline' AS observable, 'logical-properties' AS feature_ids),
 83    STRUCT('border-inline-color' AS observable, 'logical-properties' AS feature_ids),
 84    STRUCT('border-inline-end' AS observable, 'logical-properties' AS feature_ids),
 85    STRUCT('border-inline-end-color' AS observable, 'logical-properties' AS feature_ids),
 86    STRUCT('border-inline-end-style' AS observable, 'logical-properties' AS feature_ids),
 87    STRUCT('border-inline-end-width' AS observable, 'logical-properties' AS feature_ids),
 88    STRUCT('border-inline-start' AS observable, 'logical-properties' AS feature_ids),
 89    STRUCT('border-inline-start-color' AS observable, 'logical-properties' AS feature_ids),
 90    STRUCT('border-inline-start-style' AS observable, 'logical-properties' AS feature_ids),
 91    STRUCT('border-inline-start-width' AS observable, 'logical-properties' AS feature_ids),
 92    STRUCT('border-inline-style' AS observable, 'logical-properties' AS feature_ids),
 93    STRUCT('border-inline-width' AS observable, 'logical-properties' AS feature_ids),
 94    STRUCT('border-start-end-radius' AS observable, 'logical-properties' AS feature_ids),
 95    STRUCT('border-start-start-radius' AS observable, 'logical-properties' AS feature_ids),
 96    STRUCT('clear' AS observable, 'logical-properties' AS feature_ids),
 97    STRUCT('float' AS observable, 'logical-properties' AS feature_ids),
 98    STRUCT('inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
 99    STRUCT('inset' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
100    STRUCT('inset-block' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
101    STRUCT('inset-block-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
102    STRUCT('inset-block-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
103    STRUCT('inset-inline' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
104    STRUCT('inset-inline-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
105    STRUCT('inset-inline-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
106    STRUCT('margin-block' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
107    STRUCT('margin-block-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
108    STRUCT('margin-block-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
109    STRUCT('margin-inline' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
110    STRUCT('margin-inline-end' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
111    STRUCT('margin-inline-start' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
112    STRUCT('max-block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
113    STRUCT('max-inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
114    STRUCT('min-block-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
115    STRUCT('min-inline-size' AS observable, 'logical-properties,anchor-positioning' AS feature_ids),
116    STRUCT('overflow-block' AS observable, 'logical-properties' AS feature_ids),
117    STRUCT('overflow-inline' AS observable, 'logical-properties' AS feature_ids),
118    STRUCT('padding-block' AS observable, 'logical-properties' AS feature_ids),
119    STRUCT('padding-block-end' AS observable, 'logical-properties' AS feature_ids),
120    STRUCT('padding-block-start' AS observable, 'logical-properties' AS feature_ids),
121    STRUCT('padding-inline' AS observable, 'logical-properties' AS feature_ids),
122    STRUCT('padding-inline-end' AS observable, 'logical-properties' AS feature_ids),
123    STRUCT('padding-inline-start' AS observable, 'logical-properties' AS feature_ids),
124    STRUCT('forced-color-adjust' AS observable, 'forced-colors' AS feature_ids),
125    STRUCT('aspect-ratio' AS observable, 'aspect-ratio' AS feature_ids),
126    STRUCT('backdrop-filter' AS observable, 'backdrop-filter' AS feature_ids),
127    STRUCT('text-wrap' AS observable, 'text-wrap,text-wrap-balance,text-wrap-pretty' AS feature_ids),
128    STRUCT('overflow-x' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
129    STRUCT('overflow-y' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
130    STRUCT('overflow' AS observable, 'overflow-clip,overflow-shorthand' AS feature_ids),
131    STRUCT('color-scheme' AS observable, 'color-scheme' AS feature_ids),
132    STRUCT('rotate' AS observable, 'individual-transforms' AS feature_ids),
133    STRUCT('scale' AS observable, 'individual-transforms' AS feature_ids),
134    STRUCT('translate' AS observable, 'individual-transforms' AS feature_ids),
135    STRUCT('scrollbar-color' AS observable, 'scrollbar-color' AS feature_ids),
136    STRUCT('text-underline-offset' AS observable, 'text-underline-offset' AS feature_ids),
137    STRUCT('scroll-margin' AS observable, 'scroll-snap' AS feature_ids),
138    STRUCT('scroll-margin-block' AS observable, 'scroll-snap' AS feature_ids),
139    STRUCT('scroll-margin-block-end' AS observable, 'scroll-snap' AS feature_ids),
140    STRUCT('scroll-margin-block-start' AS observable, 'scroll-snap' AS feature_ids),
141    STRUCT('scroll-margin-bottom' AS observable, 'scroll-snap' AS feature_ids),
142    STRUCT('scroll-margin-inline' AS observable, 'scroll-snap' AS feature_ids),
143    STRUCT('scroll-margin-inline-end' AS observable, 'scroll-snap' AS feature_ids),
144    STRUCT('scroll-margin-inline-start' AS observable, 'scroll-snap' AS feature_ids),
145    STRUCT('scroll-margin-left' AS observable, 'scroll-snap' AS feature_ids),
146    STRUCT('scroll-margin-right' AS observable, 'scroll-snap' AS feature_ids),
147    STRUCT('scroll-margin-top' AS observable, 'scroll-snap' AS feature_ids),
148    STRUCT('scroll-padding' AS observable, 'scroll-snap' AS feature_ids),
149    STRUCT('scroll-padding-block' AS observable, 'scroll-snap' AS feature_ids),
150    STRUCT('scroll-padding-block-end' AS observable, 'scroll-snap' AS feature_ids),
151    STRUCT('scroll-padding-block-start' AS observable, 'scroll-snap' AS feature_ids),
152    STRUCT('scroll-padding-bottom' AS observable, 'scroll-snap' AS feature_ids),
153    STRUCT('scroll-padding-inline' AS observable, 'scroll-snap' AS feature_ids),
154    STRUCT('scroll-padding-inline-end' AS observable, 'scroll-snap' AS feature_ids),
155    STRUCT('scroll-padding-inline-start' AS observable, 'scroll-snap' AS feature_ids),
156    STRUCT('scroll-padding-left' AS observable, 'scroll-snap' AS feature_ids),
157    STRUCT('scroll-padding-right' AS observable, 'scroll-snap' AS feature_ids),
158    STRUCT('scroll-padding-top' AS observable, 'scroll-snap' AS feature_ids),
159    STRUCT('scroll-snap-align' AS observable, 'scroll-snap' AS feature_ids),
160    STRUCT('scroll-snap-stop' AS observable, 'scroll-snap' AS feature_ids),
161    STRUCT('scroll-snap-type' AS observable, 'scroll-snap' AS feature_ids),
162    STRUCT('container' AS observable, 'container-queries' AS feature_ids),
163    STRUCT('container-name' AS observable, 'container-queries' AS feature_ids),
164    STRUCT('container-type' AS observable, 'container-queries,container-scroll-state-queries' AS feature_ids),
165    STRUCT('scrollbar-gutter' AS observable, 'scrollbar-gutter' AS feature_ids),
166    STRUCT('hyphens' AS observable, 'hyphens' AS feature_ids),
167    STRUCT('quotes' AS observable, 'quotes' AS feature_ids),
168    STRUCT('content-visibility' AS observable, 'content-visibility,display-animation' AS feature_ids),
169    STRUCT('contain-intrinsic-block-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
170    STRUCT('contain-intrinsic-height' AS observable, 'contain-intrinsic-size' AS feature_ids),
171    STRUCT('contain-intrinsic-inline-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
172    STRUCT('contain-intrinsic-size' AS observable, 'contain-intrinsic-size' AS feature_ids),
173    STRUCT('contain-intrinsic-width' AS observable, 'contain-intrinsic-size' AS feature_ids),
174    STRUCT('border-image' AS observable, 'border-image' AS feature_ids),
175    STRUCT('border-image-outset' AS observable, 'border-image' AS feature_ids),
176    STRUCT('border-image-repeat' AS observable, 'border-image' AS feature_ids),
177    STRUCT('border-image-slice' AS observable, 'border-image' AS feature_ids),
178    STRUCT('border-image-source' AS observable, 'border-image' AS feature_ids),
179    STRUCT('border-image-width' AS observable, 'border-image' AS feature_ids),
180    STRUCT('line-break' AS observable, 'line-break' AS feature_ids),
181    STRUCT('transform-box' AS observable, 'transform-box' AS feature_ids),
182    STRUCT('animation-range' AS observable, 'scroll-driven-animations' AS feature_ids),
183    STRUCT('animation-range-end' AS observable, 'scroll-driven-animations' AS feature_ids),
184    STRUCT('animation-range-start' AS observable, 'scroll-driven-animations' AS feature_ids),
185    STRUCT('animation-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
186    STRUCT('animation' AS observable, 'scroll-driven-animations' AS feature_ids),
187    STRUCT('scroll-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
188    STRUCT('scroll-timeline-axis' AS observable, 'scroll-driven-animations' AS feature_ids),
189    STRUCT('scroll-timeline-name' AS observable, 'scroll-driven-animations' AS feature_ids),
190    STRUCT('timeline-scope' AS observable, 'scroll-driven-animations' AS feature_ids),
191    STRUCT('view-timeline' AS observable, 'scroll-driven-animations' AS feature_ids),
192    STRUCT('view-timeline-axis' AS observable, 'scroll-driven-animations' AS feature_ids),
193    STRUCT('view-timeline-inset' AS observable, 'scroll-driven-animations' AS feature_ids),
194    STRUCT('view-timeline-name' AS observable, 'scroll-driven-animations' AS feature_ids),
195    STRUCT('interpolate-size' AS observable, 'interpolate-size' AS feature_ids),
196    STRUCT('print-color-adjust' AS observable, 'print-color-adjust' AS feature_ids),
197    STRUCT('box-decoration-break' AS observable, 'box-decoration-break' AS feature_ids),
198    STRUCT('accent-color' AS observable, 'accent-color' AS feature_ids),
199    STRUCT('font-optical-sizing' AS observable, 'font-optical-sizing' AS feature_ids),
200    STRUCT('text-spacing-trim' AS observable, 'text-spacing-trim' AS feature_ids),
201    STRUCT('font-size-adjust' AS observable, 'font-size-adjust' AS feature_ids),
202    STRUCT('font-synthesis' AS observable, 'font-synthesis' AS feature_ids),
203    STRUCT('field-sizing' AS observable, 'field-sizing' AS feature_ids),
204    STRUCT('paint-order' AS observable, 'paint-order' AS feature_ids),
205    STRUCT('font-size' AS observable, 'mathml' AS feature_ids),
206    STRUCT('math-depth' AS observable, 'mathml' AS feature_ids),
207    STRUCT('math-shift' AS observable, 'mathml' AS feature_ids),
208    STRUCT('math-style' AS observable, 'mathml' AS feature_ids),
209    STRUCT('text-transform' AS observable, 'mathml' AS feature_ids),
210    STRUCT('text-box' AS observable, 'text-box' AS feature_ids),
211    STRUCT('text-box-edge' AS observable, 'text-box' AS feature_ids),
212    STRUCT('text-box-trim' AS observable, 'text-box' AS feature_ids),
213    STRUCT('white-space-collapse' AS observable, 'white-space-collapse' AS feature_ids),
214    STRUCT('transition-behavior' AS observable, 'transition-behavior' AS feature_ids),
215    STRUCT('transition' AS observable, 'transition-behavior' AS feature_ids),
216    STRUCT('view-transition-name' AS observable, 'view-transitions' AS feature_ids),
217    STRUCT('counter-set' AS observable, 'counter-set' AS feature_ids),
218    STRUCT('font-language-override' AS observable, 'font-language-override' AS feature_ids),
219    STRUCT('background-image' AS observable, 'image-set' AS feature_ids),
220    STRUCT('content' AS observable, 'image-set' AS feature_ids),
221    STRUCT('offset' AS observable, 'motion-path' AS feature_ids),
222    STRUCT('offset-anchor' AS observable, 'motion-path' AS feature_ids),
223    STRUCT('offset-distance' AS observable, 'motion-path' AS feature_ids),
224    STRUCT('offset-path' AS observable, 'motion-path' AS feature_ids),
225    STRUCT('offset-position' AS observable, 'motion-path' AS feature_ids),
226    STRUCT('offset-rotate' AS observable, 'motion-path' AS feature_ids),
227    STRUCT('animation-composition' AS observable, 'animation-composition' AS feature_ids),
228    STRUCT('anchor-name' AS observable, 'anchor-positioning' AS feature_ids),
229    STRUCT('anchor-scope' AS observable, 'anchor-positioning' AS feature_ids),
230    STRUCT('bottom' AS observable, 'anchor-positioning' AS feature_ids),
231    STRUCT('height' AS observable, 'anchor-positioning' AS feature_ids),
232    STRUCT('left' AS observable, 'anchor-positioning' AS feature_ids),
233    STRUCT('margin-bottom' AS observable, 'anchor-positioning' AS feature_ids),
234    STRUCT('margin-left' AS observable, 'anchor-positioning' AS feature_ids),
235    STRUCT('margin-right' AS observable, 'anchor-positioning' AS feature_ids),
236    STRUCT('margin-top' AS observable, 'anchor-positioning' AS feature_ids),
237    STRUCT('margin' AS observable, 'anchor-positioning' AS feature_ids),
238    STRUCT('max-height' AS observable, 'anchor-positioning' AS feature_ids),
239    STRUCT('max-width' AS observable, 'anchor-positioning' AS feature_ids),
240    STRUCT('min-height' AS observable, 'anchor-positioning' AS feature_ids),
241    STRUCT('min-width' AS observable, 'anchor-positioning' AS feature_ids),
242    STRUCT('position-anchor' AS observable, 'anchor-positioning' AS feature_ids),
243    STRUCT('position-area' AS observable, 'anchor-positioning' AS feature_ids),
244    STRUCT('position-try' AS observable, 'anchor-positioning' AS feature_ids),
245    STRUCT('position-try-fallbacks' AS observable, 'anchor-positioning' AS feature_ids),
246    STRUCT('position-try-order' AS observable, 'anchor-positioning' AS feature_ids),
247    STRUCT('position-visibility' AS observable, 'anchor-positioning' AS feature_ids),
248    STRUCT('right' AS observable, 'anchor-positioning' AS feature_ids),
249    STRUCT('top' AS observable, 'anchor-positioning' AS feature_ids),
250    STRUCT('width' AS observable, 'anchor-positioning' AS feature_ids),
251    STRUCT('font-variant-alternates' AS observable, 'font-variant-alternates' AS feature_ids),
252    STRUCT('text-justify' AS observable, 'text-justify' AS feature_ids),
253    STRUCT('hyphenate-character' AS observable, 'hyphenate-character' AS feature_ids),
254    STRUCT('font-variant-position' AS observable, 'font-variant-position' AS feature_ids),
255    STRUCT('text-emphasis' AS observable, 'text-emphasis' AS feature_ids),
256    STRUCT('text-emphasis-color' AS observable, 'text-emphasis' AS feature_ids),
257    STRUCT('text-emphasis-position' AS observable, 'text-emphasis' AS feature_ids),
258    STRUCT('text-emphasis-style' AS observable, 'text-emphasis' AS feature_ids),
259    STRUCT('image-orientation' AS observable, 'image-orientation' AS feature_ids),
260    STRUCT('ruby-position' AS observable, 'ruby-position' AS feature_ids),
261    STRUCT('ruby-align' AS observable, 'ruby-align' AS feature_ids),
262    STRUCT('hyphenate-limit-chars' AS observable, 'hyphenate-limit-chars' AS feature_ids),
263    STRUCT('font-synthesis-weight' AS observable, 'font-synthesis-weight' AS feature_ids),
264    STRUCT('text-wrap-style' AS observable, 'text-wrap-style' AS feature_ids),
265    STRUCT('initial-letter' AS observable, 'initial-letter' AS feature_ids),
266    STRUCT('view-transition-class' AS observable, 'view-transition-class' AS feature_ids),
267    STRUCT('font-variant-emoji' AS observable, 'font-variant-emoji' AS feature_ids),
268    STRUCT('font-palette' AS observable, 'font-palette,font-palette-animation' AS feature_ids),
269    STRUCT('font-family' AS observable, 'font-family-math' AS feature_ids),
270    STRUCT('scroll-marker-group' AS observable, 'scroll-markers' AS feature_ids),
271    STRUCT('word-break' AS observable, 'word-break-auto-phrase' AS feature_ids),
272    STRUCT('overlay' AS observable, 'overlay' AS feature_ids),
273    STRUCT('object-view-box' AS observable, 'object-view-box' AS feature_ids),
274    STRUCT('contain' AS observable, 'contain-inline-size' AS feature_ids),
275    STRUCT('reading-flow' AS observable, 'reading-flow' AS feature_ids),
276    STRUCT('reading-order' AS observable, 'reading-flow' AS feature_ids),
277    STRUCT('font-synthesis-style' AS observable, 'font-synthesis-style' AS feature_ids),
278    STRUCT('direction' AS observable, 'vertical-form-controls' AS feature_ids),
279    STRUCT('writing-mode' AS observable, 'vertical-form-controls' AS feature_ids),
280    STRUCT('interactivity' AS observable, 'interactivity' AS feature_ids),
281    STRUCT('baseline-source' AS observable, 'baseline-source' AS feature_ids),
282    STRUCT('scroll-initial-target' AS observable, 'scroll-initial-target' AS feature_ids),
283    STRUCT('font-synthesis-small-caps' AS observable, 'font-synthesis-small-caps' AS feature_ids)
284  ])
285),
286
287css_selector_lookup AS (
288  SELECT * FROM UNNEST([
289    STRUCT('not' AS observable, 'not' AS feature_ids, ':not' AS search_pattern),
290    STRUCT('slotted' AS observable, 'slot' AS feature_ids, '::slotted' AS search_pattern),
291    STRUCT('focus-visible' AS observable, 'focus-visible' AS feature_ids, ':focus-visible' AS search_pattern),
292    STRUCT('has' AS observable, 'has' AS feature_ids, ':has' AS search_pattern),
293    STRUCT('where' AS observable, 'where' AS feature_ids, ':where' AS search_pattern),
294    STRUCT('is' AS observable, 'is' AS feature_ids, ':is' AS search_pattern),
295    STRUCT('marker' AS observable, 'marker' AS feature_ids, '::marker' AS search_pattern),
296    STRUCT('nesting' AS observable, 'nesting' AS feature_ids, '&' AS search_pattern),
297    STRUCT('file-selector-button' AS observable, 'file-selector-button' AS feature_ids, '::file-selector-button' AS search_pattern),
298    STRUCT('dir' AS observable, 'dir-pseudo' AS feature_ids, ':dir' AS search_pattern),
299    STRUCT('cue' AS observable, 'webvtt' AS feature_ids, '::cue' AS search_pattern),
300    STRUCT('nth-child' AS observable, 'nth-child-of' AS feature_ids, ':nth-child' AS search_pattern),
301    STRUCT('nth-last-child' AS observable, 'nth-child-of' AS feature_ids, ':nth-last-child' AS search_pattern),
302    STRUCT('modal' AS observable, 'modal' AS feature_ids, ':modal' AS search_pattern),
303    STRUCT('autofill' AS observable, 'autofill' AS feature_ids, ':autofill' AS search_pattern),
304    STRUCT('backdrop' AS observable, 'popover' AS feature_ids, '::backdrop' AS search_pattern),
305    STRUCT('popover-open' AS observable, 'popover' AS feature_ids, ':popover-open' AS search_pattern),
306    STRUCT('view-transition' AS observable, 'view-transitions' AS feature_ids, '::view-transition' AS search_pattern),
307    STRUCT('view-transition-group' AS observable, 'view-transitions' AS feature_ids, '::view-transition-group' AS search_pattern),
308    STRUCT('view-transition-image-pair' AS observable, 'view-transitions' AS feature_ids, '::view-transition-image-pair' AS search_pattern),
309    STRUCT('view-transition-new' AS observable, 'view-transitions' AS feature_ids, '::view-transition-new' AS search_pattern),
310    STRUCT('view-transition-old' AS observable, 'view-transitions' AS feature_ids, '::view-transition-old' AS search_pattern),
311    STRUCT('grammar-error' AS observable, 'spelling-grammar-error' AS feature_ids, '::grammar-error' AS search_pattern),
312    STRUCT('spelling-error' AS observable, 'spelling-grammar-error' AS feature_ids, '::spelling-error' AS search_pattern),
313    STRUCT('details-content' AS observable, 'details-content' AS feature_ids, '::details-content' AS search_pattern),
314    STRUCT('user-invalid' AS observable, 'user-pseudos' AS feature_ids, ':user-invalid' AS search_pattern),
315    STRUCT('user-valid' AS observable, 'user-pseudos' AS feature_ids, ':user-valid' AS search_pattern),
316    STRUCT('future' AS observable, 'time-relative-selectors' AS feature_ids, ':future' AS search_pattern),
317    STRUCT('past' AS observable, 'time-relative-selectors' AS feature_ids, ':past' AS search_pattern),
318    STRUCT('state' AS observable, 'state' AS feature_ids, ':state' AS search_pattern),
319    STRUCT('active-view-transition' AS observable, 'active-view-transition' AS feature_ids, ':active-view-transition' AS search_pattern),
320    STRUCT('active-view-transition-type' AS observable, 'active-view-transition' AS feature_ids, ':active-view-transition-type' AS search_pattern),
321    STRUCT('target-text' AS observable, 'target-text' AS feature_ids, '::target-text' AS search_pattern),
322    STRUCT('highlight' AS observable, 'highlight' AS feature_ids, '::highlight' AS search_pattern),
323    STRUCT('scroll-marker' AS observable, 'scroll-markers' AS feature_ids, '::scroll-marker' AS search_pattern),
324    STRUCT('scroll-marker-group' AS observable, 'scroll-markers' AS feature_ids, '::scroll-marker-group' AS search_pattern),
325    STRUCT('picture-in-picture' AS observable, 'picture-in-picture' AS feature_ids, ':picture-in-picture' AS search_pattern),
326    STRUCT('checkmark' AS observable, 'customizable-select' AS feature_ids, '::checkmark' AS search_pattern),
327    STRUCT('picker' AS observable, 'customizable-select' AS feature_ids, '::picker' AS search_pattern),
328    STRUCT('picker-icon' AS observable, 'customizable-select' AS feature_ids, '::picker-icon' AS search_pattern),
329    STRUCT('scroll-button' AS observable, 'scroll-buttons' AS feature_ids, '::scroll-button' AS search_pattern),
330    STRUCT('xr-overlay' AS observable, 'webxr-dom-overlays' AS feature_ids, ':xr-overlay' AS search_pattern),
331    STRUCT('column' AS observable, 'column-pseudo' AS feature_ids, '::column' AS search_pattern),
332    STRUCT('has-slotted' AS observable, 'has-slotted' AS feature_ids, ':has-slotted' AS search_pattern),
333    STRUCT('selection' AS observable, 'text-decoration-selection' AS feature_ids, '::selection' AS search_pattern)
334  ])
335),
336
337css_atrule_lookup AS (
338  SELECT * FROM UNNEST([
339    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),
340    STRUCT('container' AS observable, 'container-queries,container-style-queries,container-scroll-state-queries' AS feature_ids),
341    STRUCT('view-transition' AS observable, 'cross-document-view-transitions' AS feature_ids),
342    STRUCT('font-face' AS observable, 'font-metric-overrides,font-size-adjust' AS feature_ids),
343    STRUCT('property' AS observable, 'registered-custom-properties' AS feature_ids),
344    STRUCT('import' AS observable, 'cascade-layers' AS feature_ids),
345    STRUCT('layer' AS observable, 'cascade-layers' AS feature_ids),
346    STRUCT('starting-style' AS observable, 'starting-style' AS feature_ids),
347    STRUCT('page' AS observable, 'page-orientation' AS feature_ids),
348    STRUCT('position-try' AS observable, 'anchor-positioning' AS feature_ids),
349    STRUCT('font-feature-values' AS observable, 'font-variant-alternates' AS feature_ids),
350    STRUCT('counter-style' AS observable, 'counter-style' AS feature_ids),
351    STRUCT('scope' AS observable, 'scope' AS feature_ids),
352    STRUCT('font-palette-values' AS observable, 'font-palette' AS feature_ids)
353  ])
354),
355
356html_element_lookup AS (
357  SELECT * FROM UNNEST([
358    STRUCT('slot' AS observable, 'slot' AS feature_ids),
359    STRUCT('canvas' AS observable, 'canvas' AS feature_ids),
360    STRUCT('a' AS observable, 'referrer-policy,attribution-reporting,fencedframe,scroll-to-text-fragment' AS feature_ids),
361    STRUCT('area' AS observable, 'referrer-policy,attribution-reporting' AS feature_ids),
362    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),
363    STRUCT('img' AS observable, 'referrer-policy,fetch-priority,aspect-ratio,attribution-reporting,sizes-auto' AS feature_ids),
364    STRUCT('link' AS observable, 'referrer-policy,fetch-priority,manifest,blocking-render,link-rel-expect,compression-dictionary-transport' AS feature_ids),
365    STRUCT('script' AS observable, 'referrer-policy,fetch-priority,js-modules,attribution-reporting,speculation-rules,blocking-render,import-maps' AS feature_ids),
366    STRUCT('video' AS observable, 'aspect-ratio,remote-playback,picture-in-picture' AS feature_ids),
367    STRUCT('template' AS observable, 'template,declarative-shadow-dom' AS feature_ids),
368    STRUCT('meta' AS observable, 'color-scheme,meta-application-title' AS feature_ids),
369    STRUCT('style' AS observable, 'blocking-render' AS feature_ids),
370    STRUCT('dialog' AS observable, 'dialog,dialog-closedby' AS feature_ids),
371    STRUCT('button' AS observable, 'popover,anchor-positioning' AS feature_ids),
372    STRUCT('input' AS observable, 'popover,anchor-positioning' AS feature_ids),
373    STRUCT('datalist' AS observable, 'datalist' AS feature_ids),
374    STRUCT('fencedframe' AS observable, 'fencedframe' AS feature_ids),
375    STRUCT('audio' AS observable, 'remote-playback' AS feature_ids),
376    STRUCT('search' AS observable, 'search' AS feature_ids),
377    STRUCT('details' AS observable, 'details-name' AS feature_ids),
378    STRUCT('selectedcontent' AS observable, 'customizable-select' AS feature_ids)
379  ])
380),
381
382-- =============================================================================
383-- SINGLE SCAN of parsed_css: extract properties, selector blocks, and at-rule types
384-- Converts the css JSON column to a string ONCE per row, then extracts all three
385-- categories in a single pass over the table.
386-- =============================================================================
387css_raw AS (
388  SELECT
389    page,
390    TO_JSON_STRING(css) AS css_str
391  FROM `httparchive.crawl.parsed_css`
392  WHERE date = crawl_date
393    AND client = 'desktop'
394    AND is_root_page = TRUE
395),
396
397-- Extract distinct CSS properties per page
398page_css_properties AS (
399  SELECT page, prop
400  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"property":"([^"]+)"')) AS prop
401  GROUP BY page, prop
402),
403
404-- Extract selector blocks per page (the content inside "selectors":[...])
405-- Each sel_block is a string like '"h1","h2",".foo:not(.bar)"'
406page_css_selectors AS (
407  SELECT page, sel_block
408  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"selectors":\[([^\]]+)\]')) AS sel_block
409  GROUP BY page, sel_block
410),
411
412-- Extract distinct at-rule types per page
413page_css_atrules AS (
414  SELECT page, atrule
415  FROM css_raw, UNNEST(REGEXP_EXTRACT_ALL(css_str, r'"type":"([^"]+)"')) AS atrule
416  GROUP BY page, atrule
417),
418
419-- =============================================================================
420-- CSS PROPERTY RESULTS: join extracted properties with lookup
421-- =============================================================================
422css_property_results AS (
423  SELECT
424    lk.feature_ids,
425    lk.observable,
426    'css_property' AS type,
427    COUNT(DISTINCT pcp.page) AS pages_using
428  FROM css_property_lookup lk
429  LEFT JOIN page_css_properties pcp ON pcp.prop = lk.observable
430  GROUP BY lk.feature_ids, lk.observable
431),
432
433-- =============================================================================
434-- CSS SELECTOR RESULTS: search selector blocks for patterns
435-- For each page, check if any selector block contains the search pattern
436-- =============================================================================
437css_selector_results AS (
438  SELECT
439    lk.feature_ids,
440    lk.observable,
441    'css_selector' AS type,
442    COUNT(DISTINCT pcs.page) AS pages_using
443  FROM css_selector_lookup lk
444  LEFT JOIN page_css_selectors pcs
445    ON pcs.sel_block LIKE CONCAT('%', lk.search_pattern, '%')
446  GROUP BY lk.feature_ids, lk.observable
447),
448
449-- =============================================================================
450-- CSS AT-RULE RESULTS: join extracted at-rule types with lookup
451-- =============================================================================
452css_atrule_results AS (
453  SELECT
454    lk.feature_ids,
455    lk.observable,
456    'css_atrule' AS type,
457    COUNT(DISTINCT pca.page) AS pages_using
458  FROM css_atrule_lookup lk
459  LEFT JOIN page_css_atrules pca ON pca.atrule = lk.observable
460  GROUP BY lk.feature_ids, lk.observable
461),
462
463-- =============================================================================
464-- HTML ELEMENTS: extract from custom_metrics.element_count in pages table
465-- element_count is a JSON object like {"div": 500, "span": 200, "dialog": 1}
466-- =============================================================================
467page_html_elements AS (
468  SELECT
469    page,
470    LOWER(elem_name) AS elem_name
471  FROM
472    `httparchive.crawl.pages`,
473    UNNEST(
474      REGEXP_EXTRACT_ALL(
475        TO_JSON_STRING(custom_metrics.element_count),
476        r'"([^"]+)"\s*:'
477      )
478    ) AS elem_name
479  WHERE date = crawl_date
480    AND client = 'desktop'
481    AND is_root_page = TRUE
482  GROUP BY page, elem_name
483),
484
485html_element_results AS (
486  SELECT
487    lk.feature_ids,
488    lk.observable,
489    'html_element' AS type,
490    COUNT(DISTINCT phe.page) AS pages_using
491  FROM html_element_lookup lk
492  LEFT JOIN page_html_elements phe ON phe.elem_name = lk.observable
493  GROUP BY lk.feature_ids, lk.observable
494),
495
496-- =============================================================================
497-- TOTAL PAGES: for computing percentages
498-- =============================================================================
499total_css_pages AS (
500  SELECT COUNT(DISTINCT page) AS total_pages
501  FROM `httparchive.crawl.parsed_css`
502  WHERE date = crawl_date
503    AND client = 'desktop'
504    AND is_root_page = TRUE
505),
506
507total_html_pages AS (
508  SELECT COUNT(DISTINCT page) AS total_pages
509  FROM `httparchive.crawl.pages`
510  WHERE date = crawl_date
511    AND client = 'desktop'
512    AND is_root_page = TRUE
513),
514
515-- =============================================================================
516-- COMBINE all results
517-- =============================================================================
518all_results AS (
519  SELECT feature_ids, observable, type, pages_using FROM css_property_results
520  UNION ALL
521  SELECT feature_ids, observable, type, pages_using FROM css_selector_results
522  UNION ALL
523  SELECT feature_ids, observable, type, pages_using FROM css_atrule_results
524  UNION ALL
525  SELECT feature_ids, observable, type, pages_using FROM html_element_results
526)
527
528SELECT
529  r.feature_ids,
530  r.observable,
531  r.type,
532  r.pages_using,
533  CASE
534    WHEN r.type = 'html_element' THEN th.total_pages
535    ELSE tc.total_pages
536  END AS total_pages,
537  SAFE_DIVIDE(r.pages_using * 100.0,
538    CASE
539      WHEN r.type = 'html_element' THEN th.total_pages
540      ELSE tc.total_pages
541    END
542  ) AS pct
543FROM all_results r
544CROSS JOIN total_css_pages tc
545CROSS JOIN total_html_pages th
546ORDER BY pct DESC;