rjguyah

WITH 
-- 1. Aggregate the standard Query Logs (Daily Grain)
query_log_aggregation AS (
    SELECT 
        query_date AS activity_date,
        dbid,
        SUM(vector_search_query_cnt) AS total_vector_hits,
        SUM(CASE WHEN REGEXP_CONTAINS(user_agent_name, r'(?i)graphrag-python|LLAMAINDEX|LLM-Graph-Builder') THEN query_count ELSE 0 END) AS official_framework_hits,
        SUM(CASE WHEN REGEXP_CONTAINS(tx_metadata_app, r'(?i)aura-agent') OR REGEXP_CONTAINS(user_agent_name, r'(?i)mcp|bot|snorlax-snorlax-v3-rollout') THEN query_count ELSE 0 END) AS inferred_agentic_protocol_hits,
        SUM(CASE WHEN REGEXP_CONTAINS(user_agent_name, r'(?i)python|javascript|java|dotnet|neo4rs') AND vector_search_query_cnt > 0 THEN vector_search_query_cnt ELSE 0 END) AS diy_production_hits,

rjguyah

WITH tier_lookup AS (
  SELECT 
    aba, 
    ARRAY_AGG(DISTINCT tier_category IGNORE NULLS) as tier_list
  FROM `neo4j-padw-prod.public_billing.v_billing_prepaid_aba_monthly` 
  GROUP BY 1
),
deal_base AS (
  SELECT 
    d.aba,