Behind every figure.
The SQL behind "Ask an AI for a roofer", with each result written under its header. Two scripts finish the job: matcher.py and lists.py.
Download queries.sql · Back to the study
-- =====================================================================================
-- How AI assistants recommend local businesses - the queries behind every figure
-- 8ight research, published 2026-10-05 (Toronto). Study page: https://8ight.ai/research/how-ai-recommends-local-businesses/
--
-- Every figure in the study comes from one of the queries below, or from one of the two
-- scripts (matcher.py, lists.py) run on a query's output. All were re-run on 2026-10-06
-- between 01:31 and 02:15 UTC, read only, and the values in the study are from that run.
-- Most queries have no end date: run later, they would include rows added after that time.
--
-- To publish this copy we changed three things and nothing else:
-- 1. Internal table and column names are replaced by descriptive ones:
-- maps_businesses = the records of businesses we found through Google Maps searches
-- (name, city, website, Google rating and review count when found)
-- question_set = the list a recurring question belongs to
-- 2. Four internal lists appear by name only; their contents are not published:
-- study_excluded_trades one trade left out of every figure under a standing company rule
-- study_excluded_pairs one trade-and-city pair (24 checks) left out to avoid a conflict of interest
-- study_question_sets the recurring question lists used in the study
-- test_question_sets question lists we use only for testing (left out of the failure count)
-- 3. Comments are rewritten for readers.
-- The filters, dates, joins and arithmetic are exactly as run.
--
-- Two kinds of data:
-- geo_public_checks one row per check: a trade, a city, a business from our Google Maps searches,
-- and the full answers of ChatGPT, Claude and Gemini to
-- "Who is the best {trade} in {city}? Name specific businesses."
-- Only checks we ran ourselves (internal); checks run by visitors to our
-- free tool may contain personal data and are never used.
-- geo_visibility_runs recurring questions asked on a schedule (source stability and one Reddit count only).
-- =====================================================================================
-- -------------------------------------------------------------------------------------
-- BASE, repeated inside each check query so each one runs on its own:
-- g every check we ran ourselves, exclusions applied, one spelling per city
-- ("toronto ontario" and "toronto" were stored as two cities; they are one)
-- b one row per business checked (its latest check)
-- -------------------------------------------------------------------------------------
-- =====================================================================================
-- S1 The sample: businesses, trade-and-place pairs, trades, places, dates, by country
-- Result: 2,976 businesses, 516 pairs, 39 trades, 49 places, 2026-09-10 to 2026-09-30;
-- Canada (Greater Toronto Area) 749 / 94 / 9 / 17; United States 2,227 / 422 / 34 / 32.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
b as (
select distinct on (lower(trim(business)), trade_norm, city_c) *,
case when city_c in ('toronto','vaughan','oakville','mississauga','scarborough','burlington','north york','brampton','etobicoke',
'richmond hill','aurora','newmarket','whitby','pickering','thornhill','markham','ajax') then 'Canada' else 'United States' end country
from g order by lower(trim(business)), trade_norm, city_c, created_at desc)
select coalesce(country, 'ALL') country, count(*) businesses, count(distinct (trade_norm, city_c)) trade_city_pairs,
count(distinct trade_norm) trades, count(distinct city_c) places, min(created_at)::date first_check, max(created_at)::date last_check
from b group by rollup(country) order by 1;
-- =====================================================================================
-- S2 Fresh answers per engine and model (which engine actually answered)
-- Result: ChatGPT gpt-5.6-terra 1,398 answered of 1,399, gpt-5.6-sol 14 of 14;
-- Claude Sonnet 4.6 200 of 1,393, three other Claude models our checks used later in September
-- 18 of 20 (218 of 1,413 in all);
-- Gemini 2.5 Flash 20 of 1,413.
-- The model is not stored with each answer. The periods below come from our own deployment
-- history, and the study says so.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%'))
select e->>'engine' engine,
case when e->>'engine' = 'chatgpt' then case when g.created_at < '2026-09-29 18:14+00' then 'gpt-5.6-terra' else 'gpt-5.6-sol' end
when e->>'engine' = 'claude' then case when g.created_at < '2026-09-15 15:34+00' then 'claude-sonnet-4-6'
when g.created_at < '2026-09-24 08:19+00' then 'claude-opus-5.x'
when g.created_at < '2026-09-29 18:14+00' then 'claude-haiku-4-5' else 'claude-opus-5-5' end
else 'gemini-2.5-flash' end model_window,
count(*) fresh_answers, count(*) filter (where e->>'status' = 'ok') ok,
count(distinct (g.trade_norm, g.city_c)) filter (where e->>'status' = 'ok') pairs_ok
from g, jsonb_array_elements(g.engines) e
where g.cached_from is null
group by 1, 2 order by 1, 2;
-- =====================================================================================
-- S3 Fresh answers by day (the "Dates" paragraph)
-- Result: ChatGPT 412 on 2026-09-10 and 979 on 2026-09-11 (1,391 of 1,412); Claude 199 on
-- 2026-09-10 (of 218); 14 for each engine on 2026-09-30; the rest one a day.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%'))
select (g.created_at at time zone 'UTC')::date d, e->>'engine' eng, count(*) filter (where e->>'status' = 'ok') ok
from g, jsonb_array_elements(g.engines) e
where g.cached_from is null
group by 1, 2 order by 1, 2;
-- =====================================================================================
-- S4 Where the businesses came from: how many we can trace by name to our Google Maps records
-- Result: 2,976 businesses; 2,903 match a Google Maps record by name (2,821 by name and city);
-- 2,858 of them carry a Google rating from that search. 73 match no record we hold.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
b as (select distinct on (lower(trim(business)), trade_norm, city_c) lower(trim(business)) bk, city_c
from g order by lower(trim(business)), trade_norm, city_c, created_at desc),
m as (select distinct lower(trim(business)) bk, trim(split_part(lower(city), ',', 1)) ck, (rating is not null) has_rating
from maps_businesses)
select count(*) total,
count(*) filter (where (bk, city_c) in (select bk, ck from m)) traced_name_and_city,
count(*) filter (where bk in (select bk from m)) traced_by_name,
count(*) filter (where bk in (select bk from m where has_rating)) traced_with_rating,
count(*) filter (where bk not in (select bk from m)) not_traced
from b;
-- =====================================================================================
-- D1 Every business and every engine's full answer (input to matcher.py)
-- Run once per engine ('chatgpt', 'claude', 'gemini'); save the rows as answers-<engine>.txt; then
-- python3 matcher.py answers-chatgpt.txt answers-claude.txt answers-gemini.txt
-- matcher.py prints F1, F2, F5 and F6 below.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
b as (select distinct on (lower(trim(business)), trade_norm, city_c) * from g order by lower(trim(business)), trade_norm, city_c, created_at desc),
o as (select distinct on (lower(trim(business)), trim(split_part(lower(city), ',', 1)))
lower(trim(business)) bk, trim(split_part(lower(city), ',', 1)) ck, rating, reviews
from maps_businesses where reviews is not null
order by lower(trim(business)), trim(split_part(lower(city), ',', 1)), created_at desc)
select json_build_object('id', b.id, 'business', b.business, 'trade', b.trade_norm, 'city', b.city_c, 'eng', e->>'engine',
'st', e->>'status', 'txt', b.answers -> (e->>'engine') ->> 'text', 'reviews', o.reviews, 'rating', o.rating)::text j
from b left join o on o.bk = lower(trim(b.business)) and o.ck = b.city_c, jsonb_array_elements(b.engines) e
where e->>'engine' = 'chatgpt';
-- =====================================================================================
-- F1 How often each engine names a business we checked (D1 + matcher.py)
-- Result: ChatGPT 369 of 2,975 (12.4%); Claude 198 of 1,776 (11.1%); Gemini 17 of 232 (7.3%,
-- too few Gemini answers to compare).
-- F2 Do ChatGPT and Claude name the same businesses? (D1 + matcher.py)
-- Result: 1,775 businesses answered by both; 294 named by at least one; 81 by both (27.6%);
-- 96 by ChatGPT only, 117 by Claude only. 198 answered by all three engines;
-- 29 named by any, 2 by all three.
-- F5 Google reviews and being named by ChatGPT (D1 + matcher.py)
-- Result: under 25 reviews 3.4% (15 of 435); 25-99 10.0% (90 of 903); 100-249 13.9% (108 of 775);
-- 250-999 20.9% (110 of 527); 1,000+ 22.2% (30 of 135). Median reviews: named 175, not
-- named 97. Median rating 4.9 for both. Most reviewed business in a place (places with 4+
-- businesses checked that have a review count): 65 of 285 named (22.8%); the others 215 of
-- 2,085 (10.3%). 2,775 businesses have a review count from the Google Maps search that found them.
-- F6 By trade (D1 + matcher.py)
-- Result: the table in section 2.3.
-- =====================================================================================
-- =====================================================================================
-- F2b Lists of names, ChatGPT and Claude, same question, same moment
-- Our check stored up to the first eight names it picked out of each answer (column "named").
-- Save the rows as named.txt, then: python3 lists.py
-- Result: 214 questions in 211 trade-and-place pairs; of all businesses named by either engine,
-- a median 12% were named by both (mean 16%); for 24% of questions the lists shared none.
-- Median names after cleaning: ChatGPT 5, Claude 7 (means 5.0 and 6.7). Stored lists at the
-- eight-name limit: Claude 164 of 214, ChatGPT 110. 18.9% of 11,393 stored entries were
-- dropped as not a business (an address, a directory, a summary line).
-- F3 The same question asked of ChatGPT twice (same output, lists.py)
-- Result: 267 trade-and-place pairs; median gap 39 minutes (61% within an hour); of all
-- businesses named in either answer, a median 29% were named in both; 9% shared none.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%'))
select g.id, g.trade_norm, g.city_c, g.created_at, e->>'engine' eng, (e->'named')::text named
from g, jsonb_array_elements(g.engines) e
where g.cached_from is null and e->>'status' = 'ok'
order by g.created_at;
-- =====================================================================================
-- F4 What the answers cite, by kind of source (fresh answers; ChatGPT and Claude)
-- A source is the website domain of a link the engine cited, www removed. Gemini's links come
-- back as Google redirect addresses, so Gemini is left out of every source figure.
-- "A business website" = a domain from our Google Maps records (maps_businesses).
-- Result: ChatGPT (1,412 answers): a business website 68.1%, directory or review site 65.3%,
-- government or licensing site 25.1%, social network 0.6%, Reddit or forum 0.4%,
-- Google or a map 0.2%. Claude (218): directory or review site 99.5%, a business
-- website 45.0%, social network 11.0%, Google or a map 0.9%, government 0, Reddit 0.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
a as (
select g.id, k eng, g.created_at,
array(select distinct regexp_replace(lower(substring(x from '^https?://([^/?#]+)')), '^www\.', '')
from jsonb_array_elements_text(coalesce(v->'citations', '[]'::jsonb)) x) d
from g, jsonb_each(g.answers) t(k, v)
where g.cached_from is null and k in ('chatgpt','claude')
and coalesce(v->>'text', '') <> '' and v->>'error' is null),
biz as (select distinct regexp_replace(lower(substring(website from '^https?://([^/?#]+)')), '^www\.', '') dom
from maps_businesses where website ~ '^https?://'),
c as (
select a.id, a.eng, dom,
case
when dom ~ '(^|\.)(yelp\.|homestars\.|bbb\.org|angi\.com|angieslist|homeadvisor|yellowpages\.|yp\.com|thumbtack|houzz\.|expertise\.com|threebestrated|porch\.com|bark\.com|homeguide|movebuddha|consumeraffairs|local\.yahoo|trustanalytica|bestpickreports|downtobid|gymslistshq|carfax\.com|trustpilot|birdeye|mapquest|manta\.com|superpages|cylex|n49\.|opendi|brownbook|merchantcircle|buildzoom|networx|fixr|upcity|clutch\.co|bestprosintown|topratedlocal|chamberofcommerce|tripadvisor|fresha|mindbody|classpass|vagaro|booksy|nextdoor|care\.com|zocdoc|healthgrades|ratemds|realself|groupon|bestplumbers|bestrated)'
then 'directory or review site'
when dom ~ '(\.gov$|\.gov\.[a-z]{2}$|^tico\.ca$|\.gc\.ca$|(^|\.)ontario\.ca$|\.state\.[a-z]{2}\.us$)' then 'government or licensing board'
when dom ~ '(^|\.)(facebook|instagram|youtube|tiktok|linkedin|twitter|x\.com|pinterest|threads\.net)' then 'social network'
when dom ~ '(^|\.)(reddit|quora|stackexchange)\.' then 'Reddit or forum'
when dom ~ '(^|\.)(google\.|goo\.gl|g\.co$|g\.page|maps\.apple|bing\.com)' then 'Google or a map'
when dom in (select dom from biz) then 'a business website'
else 'other'
end kind
from a, unnest(a.d) dom where dom is not null)
select eng, kind,
count(distinct id) answers_citing,
(select count(*) from a where a.eng = c.eng) answers_total,
round(100.0 * count(distinct id) / (select count(*) from a where a.eng = c.eng), 1) pct_of_answers,
count(*) citations,
round(100.0 * count(*) / sum(count(*)) over (partition by eng), 1) pct_of_citations
from c group by 1, 2 order by 1, 4 desc;
-- F4b The top 40 "other" domains, read by hand: national franchise brands' own sites,
-- manufacturers' certified-contractor pages, a tree-care association, local businesses we had not
-- found on Google Maps, smaller directories and ranking sites our list does not include,
-- encyclopaedia and news pages. Same CTEs as F4, final select:
-- select dom, count(distinct id) answers, string_agg(distinct eng, ',') engines
-- from c where kind = 'other' group by 1 order by 2 desc limit 40;
-- =====================================================================================
-- G1 Which government pages ChatGPT cited (behind "most of them licensing or registration pages")
-- Output: one row per answer that cites a government address, with its government URLs.
-- Each answer was then sorted by this keyword rule (Python, case-insensitive, on the joined URLs):
-- licen|contractor|registr|verify|lookup|checklicense|conreg|roc\.az\.gov|rld\.nm\.gov|cslb|tdlr|
-- tico\.ca|fmcsa|household-goods|movers|/cib|pestboard|certified-arborist|wrecker|hauler|
-- transporters|directory|motor-carrier|qualified-contractors|bccocontractor|llv/search|
-- psc\.nebraska|dli\.mn\.gov|mda\.state\.mn\.us/licensing|board-and-code|approved-roll-off|pest-control
-- and we read the full list of addresses.
-- Result: 355 answers; 250 (about 7 in 10) cite a licensing board, a licence look-up or a list of
-- registered or approved contractors; the rest are mostly permit rules, consumer guides and
-- regulations, plus a few unrelated pages. None is a tourism page.
-- =====================================================================================
with g as (
select p.*
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
a as (
select g.id, x url
from g, jsonb_each(g.answers) t(k, v), jsonb_array_elements_text(coalesce(v->'citations', '[]'::jsonb)) x
where g.cached_from is null and k = 'chatgpt'
and coalesce(v->>'text', '') <> '' and v->>'error' is null)
select id::text, string_agg(lower(url), ' ') urls
from a
where regexp_replace(lower(substring(url from '^https?://([^/?#]+)')), '^www\.', '') ~ '(\.gov$|\.gov\.[a-z]{2}$|^tico\.ca$|\.gc\.ca$|(^|\.)ontario\.ca$|\.state\.[a-z]{2}\.us$)'
group by 1;
-- =====================================================================================
-- P1 Platform by platform: share of fresh answers citing each (ChatGPT and Claude)
-- Result: Yelp ChatGPT 0 of 1,412, Claude 202 of 218 (92.7%); BBB 38.7% / 6.4%;
-- a government or licensing site 25.1% / 0; Expertise.com 16.3% / 22.0%; Angi 11.8% / 25.2%;
-- Thumbtack 3.7% / 19.3%; HomeGuide 0.1% / 23.4%; ClassPass 1.3% / 30.3%;
-- Yellow Pages 0.2% / 12.4%; Facebook 0 / 8.7%; Reddit 6 of 1,412 (0.4%) / 0 of 218;
-- Google maps or business pages 0.2% / 0.5%.
-- pct_text_mentions is a loose word match, read only for Yelp and Google: Google is mentioned in
-- the text of 23.2% of ChatGPT answers and 43.1% of Claude's.
-- =====================================================================================
with g as (
select p.*, regexp_replace(p.city_norm, ' (ontario|florida|texas|arizona|utah|kentucky|tennessee|missouri|minnesota|nebraska|california|nevada|wisconsin|georgia|new mexico|north carolina|colorado|oklahoma|ohio|indiana|pennsylvania)$', '') city_c
from geo_public_checks p
where p.internal
and p.trade_norm not in (select trade from study_excluded_trades)
and not exists (select 1 from study_excluded_pairs x where x.trade = p.trade_norm and p.city_norm like x.city || '%')),
a as (
select g.id, k eng, g.answers -> k ->> 'text' txt,
array(select distinct regexp_replace(lower(substring(x from '^https?://([^/?#]+)')), '^www\.', '')
from jsonb_array_elements_text(coalesce(v->'citations', '[]'::jsonb)) x) d
from g, jsonb_each(g.answers) t(k, v)
where g.cached_from is null and k in ('chatgpt','claude')
and coalesce(v->>'text', '') <> '' and v->>'error' is null),
pl(name, pat, word) as (values
('Yelp','^yelp\.','yelp'), ('HomeStars','^homestars\.','homestars'), ('BBB','^bbb\.org$','better business bureau|bbb'),
('Angi','^angi\.com$','angi'), ('Google (maps / business pages)','^(google\.|maps\.google|g\.co$|g\.page|business\.google)','google'),
('Reddit','reddit\.com$','reddit'), ('Facebook','facebook\.com$','facebook'), ('Instagram','instagram\.com$','instagram'),
('Yellow Pages','yellowpages\.','yellow pages'), ('Thumbtack','thumbtack\.com$','thumbtack'), ('Houzz','houzz\.','houzz'),
('Nextdoor','nextdoor\.','nextdoor'), ('HomeGuide','homeguide\.com$','homeguide'), ('Expertise.com','expertise\.com$','expertise\.com'),
('ThreeBestRated','threebestrated\.','threebestrated|three best rated'), ('ConsumerAffairs','consumeraffairs\.com$','consumeraffairs'),
('Best Pick Reports','bestpickreports\.com$','best pick'), ('ClassPass','classpass\.com$','classpass'), ('Mindbody','mindbody','mindbody'),
('Wikipedia','wikipedia\.org$','wikipedia'),
('a government or licensing site','(\.gov$|\.gov\.[a-z]{2}$|^tico\.ca$|\.gc\.ca$|(^|\.)ontario\.ca$|\.state\.[a-z]{2}\.us$)','licens'))
select pl.name, a.eng,
count(*) filter (where exists (select 1 from unnest(a.d) z where z ~ pl.pat)) answers_citing,
count(*) answers_total,
round(100.0 * count(*) filter (where exists (select 1 from unnest(a.d) z where z ~ pl.pat)) / count(*), 1) pct_citing,
round(100.0 * count(*) filter (where lower(a.txt) ~ ('\m(' || pl.word || ')')) / count(*), 1) pct_text_mentions
from a cross join pl
group by 1, 2 order by 2, 3 desc;
-- =====================================================================================
-- T3 The recurring questions: size and dates
-- Result: 2,527 answers to 120 questions, 2026-07-01 to 2026-10-05 (ChatGPT 1,354 from 2026-08-23;
-- Claude 1,173). ChatGPT answers before 2026-08-23 are left out: the model then is not certain.
-- =====================================================================================
select coalesce(r.engine, 'ALL') engine, count(*) answers, count(distinct r.query_id) questions,
min(r.run_at)::date first_run, max(r.run_at)::date last_run, count(distinct r.run_at::date) run_days,
count(*) filter (where jsonb_array_length(r.citations) > 0) with_citations
from geo_visibility_runs r
join geo_target_queries q on q.id = r.query_id
where r.error is null
and r.question_set in (select question_set from study_question_sets)
and not exists (select 1 from study_excluded_trades x where q.query ~* x.pattern)
and r.engine in ('chatgpt','claude')
and r.run_at >= '2026-07-01'
and not (r.engine = 'chatgpt' and r.run_at < '2026-08-23 03:00+00')
group by rollup(r.engine) order by 1;
-- =====================================================================================
-- T4 Recurring questions: when each was first asked
-- Result: 120 questions; 36 first asked before 2026-09-22, 84 on or after.
-- =====================================================================================
with r as (
select r.query_id, min(r.run_at) first_run
from geo_visibility_runs r
join geo_target_queries q on q.id = r.query_id
where r.error is null
and r.question_set in (select question_set from study_question_sets)
and not exists (select 1 from study_excluded_trades x where q.query ~* x.pattern)
and r.engine in ('chatgpt','claude')
and r.run_at >= '2026-07-01' and not (r.engine = 'chatgpt' and r.run_at < '2026-08-23 03:00+00')
group by 1)
select count(*) questions, count(*) filter (where first_run < '2026-09-22') first_asked_before_22_sep,
count(*) filter (where first_run >= '2026-09-22') first_asked_22_sep_or_later
from r;
-- =====================================================================================
-- T1b Recurring questions: how often Reddit (and Yelp) is cited
-- Result: ChatGPT cited Reddit in 1 of 1,354 answers, Claude in 0 of 1,173.
-- =====================================================================================
with r as (
select r.id, r.engine, r.run_at, r.response_full,
case when r.run_at < '2026-09-29 18:14+00' then 'earlier' else 'current' end per,
array(select distinct regexp_replace(lower(substring(x from '^https?://([^/?#]+)')), '^www\.', '')
from jsonb_array_elements_text(r.citations) x) d
from geo_visibility_runs r
join geo_target_queries q on q.id = r.query_id
where r.error is null
and r.question_set in (select question_set from study_question_sets)
and not exists (select 1 from study_excluded_trades x where q.query ~* x.pattern)
and r.engine in ('chatgpt','claude')
and r.run_at >= '2026-07-01'
and not (r.engine = 'chatgpt' and r.run_at < '2026-08-23 03:00+00'))
select engine, per, count(*) answers,
count(*) filter (where exists (select 1 from unnest(d) z where z ~ '^yelp\.')) cites_yelp,
count(*) filter (where response_full ilike '%yelp%') text_mentions_yelp,
count(*) filter (where exists (select 1 from unnest(d) z where z ~ 'reddit\.com$')) cites_reddit
from r group by rollup(engine, per) order by 1, 2;
-- =====================================================================================
-- T2 Recurring questions: the same question re-asked, how much of the cited source list survives
-- Overlap = sources in both answers / sources in either.
-- Result: ChatGPT (gpt-5.6-terra) 1,073 consecutive pairs a median 1 day apart: median 29%.
-- Claude (Sonnet 4.6) 805 pairs a day apart: 78%; 34 pairs a week apart: 67%.
-- =====================================================================================
with r as (
select r.query_id, r.engine, r.run_at,
case when r.engine = 'chatgpt' then case when r.run_at < '2026-09-29 18:14+00' then 'gpt-5.6-terra' else 'gpt-5.6-sol' end
when r.engine = 'claude' then case when r.run_at < '2026-09-15 15:34+00' then 'claude-sonnet-4-6'
when r.run_at < '2026-09-24 08:19+00' then 'claude-opus-5.x'
when r.run_at < '2026-09-29 18:14+00' then 'claude-haiku-4-5' else 'claude-opus-5-5' end end era,
array(select distinct regexp_replace(lower(substring(x from '^https?://([^/?#]+)')), '^www\.', '')
from jsonb_array_elements_text(r.citations) x) d
from geo_visibility_runs r
join geo_target_queries q on q.id = r.query_id
where r.error is null
and r.question_set in (select question_set from study_question_sets)
and not exists (select 1 from study_excluded_trades x where q.query ~* x.pattern)
and r.engine in ('chatgpt','claude')
and r.run_at >= '2026-07-01' and not (r.engine = 'chatgpt' and r.run_at < '2026-08-23 03:00+00')),
p as (select engine, era, run_at, d, lag(d) over w pd, lag(era) over w pera, lag(run_at) over w prun
from r window w as (partition by query_id, engine order by run_at)),
j as (select engine, case when era = pera then era else 'model changed between runs' end era_,
extract(epoch from run_at - prun) / 86400 gap,
(select count(*) from (select unnest(d) intersect select unnest(pd)) i)::numeric
/ nullif((select count(*) from (select unnest(d) union select unnest(pd)) u), 0) jac
from p where pd is not null)
select engine, era_, case when gap >= 6 then 'about a week apart' else 'under 6 days apart' end spacing,
count(jac) pairs, round(percentile_cont(0.5) within group (order by jac)::numeric, 2) median_overlap,
round(avg((jac = 0)::int), 2) share_no_shared_source, round(percentile_cont(0.5) within group (order by gap)::numeric, 1) median_gap_days
from j where jac is not null group by 1, 2, 3 order by 1, 2, 3;
-- =====================================================================================
-- W1 What failed: scheduled AI checks that returned an error, 1 July to 5 October 2026
-- Counts every scheduled check we ran (all question lists except test lists), not only this study's.
-- Result: 5,046 of 13,473 attempts failed (37.5%); 4,720 of them for billing, quota or a spending
-- cap on our side. By engine: Gemini 67.7%, Claude 37.5%, ChatGPT 7.1%.
-- =====================================================================================
select coalesce(r.engine, 'ALL') engine, count(*) attempts, count(*) filter (where r.error is not null) failed,
round(100.0 * count(*) filter (where r.error is not null) / count(*), 1) pct_failed,
count(*) filter (where r.error ilike any (array['%credit%','%balance%','%prepay%','%quota%','%billing%','%spend%'])) billing_or_cap
from geo_visibility_runs r
where r.question_set not in (select question_set from test_question_sets)
and r.run_at >= '2026-07-01' and r.run_at < '2026-10-06 00:00+00'
group by rollup(r.engine) order by 1;
