Citation Queries

SELECT engine,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       countIf(check_failed = 1) AS failed,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY engine
ORDER BY citation_rate

SELECT prompt_topic AS topic,
       uniqExact(prompt_id) AS prompts,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY topic
ORDER BY citation_rate ASC, prompts DESC

SELECT toStartOfDay(created_at) AS day,
       engine,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       countIf(check_failed = 1) AS failed,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day, engine
ORDER BY day, engine

SELECT cited_domain,
       max(is_ours) AS is_target_domain,
       count() AS answers_citing,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines
FROM (
    SELECT prompt_id,
           engine,
           arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(top_cited_domains)))) AS cited_domain,
           has(arrayMap(u -> domain(JSONExtractString(u)), JSONExtractArrayRaw(toString(target_urls))), cited_domain) AS is_ours
    FROM system.aeo_citation_checks
    WHERE created_at >= now() - INTERVAL 7 DAY
      AND check_failed = 0
)
GROUP BY cited_domain
ORDER BY answers_citing DESC
LIMIT 25

SELECT prompt_id,
       any(prompt_text) AS prompt,
       any(prompt_topic) AS topic,
       countIf(check_failed = 0) AS answered,
       uniqExactIf(engine, check_failed = 0) AS engines_answered,
       max(created_at) AS last_checked
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 14 DAY
GROUP BY prompt_id
HAVING answered > 0 AND countIf(cited = 1) = 0
ORDER BY answered DESC, last_checked DESC
LIMIT 100

SELECT prompt_id,
       prompt_text AS prompt,
       engine,
       cited = 1 AS latest_cited,
       check_failed = 1 AS latest_failed,
       target_best_position AS latest_position,
       created_at AS last_checked
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
ORDER BY prompt_id, engine, created_at DESC
LIMIT 1 BY prompt_id, engine

SELECT url,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines,
       count() AS answers
FROM (
    SELECT prompt_id,
           engine,
           arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(target_urls)))) AS url
    FROM system.aeo_citation_checks
    WHERE created_at >= now() - INTERVAL 30 DAY
      AND cited = 1
)
GROUP BY url
ORDER BY prompts DESC
LIMIT 50

SELECT lower(trim(q)) AS search_query,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines,
       count() AS times
FROM (
    SELECT prompt_id,
           engine,
           arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(search_queries)))) AS q
    FROM system.aeo_citation_checks
    WHERE created_at >= now() - INTERVAL 30 DAY
      AND check_failed = 0
)
GROUP BY search_query
ORDER BY times DESC
LIMIT 100

SELECT engine,
       countIf(cited = 1) AS cited_checks,
       round(avgIf(target_best_position, cited = 1), 2) AS avg_best_position,
       countIf(cited = 1 AND target_best_position = 1) AS cited_first
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY engine

SELECT engine,
       brand_sentiment,
       count() AS answers
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
  AND check_failed = 0
  AND brand_sentiment IS NOT NULL
GROUP BY engine, brand_sentiment
ORDER BY engine, answers DESC

SELECT toStartOfDay(created_at) AS day,
       engine,
       count() AS checks,
       countIf(check_failed = 1) AS failed,
       anyIf(error, check_failed = 1) AS sample_error
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 14 DAY
GROUP BY day, engine
ORDER BY day DESC, engine