DROP VIEW IF EXISTS geo_monitoring_archive.daily_model_site_citation_rank;

CREATE VIEW geo_monitoring_archive.daily_model_site_citation_rank AS
SELECT
  business_date,
  model,
  ai_platform_id,
  site,
  hosts,
  citation_count,
  cited_run_count,
  unique_url_count,
  brand_count,
  rank() OVER (
    PARTITION BY business_date, model
    ORDER BY citation_count DESC, cited_run_count DESC, unique_url_count DESC, site ASC
  ) AS site_rank
FROM
(
  SELECT
    cf.business_date AS business_date,
    ifNull(nullIf(r.provider_model, ''), cf.ai_platform_id) AS model,
    cf.ai_platform_id AS ai_platform_id,
    cutToFirstSignificantSubdomain(cf.host) AS site,
    groupUniqArray(cf.host) AS hosts,
    count() AS citation_count,
    uniqExact(cf.run_id) AS cited_run_count,
    uniqExact(cf.normalized_url) AS unique_url_count,
    uniqExact(cf.brand_id) AS brand_count
  FROM geo_monitoring_archive.monitoring_citation_facts AS cf
  LEFT JOIN geo_monitoring_archive.question_monitor_runs AS r
    ON cf.tenant_id = r.tenant_id
   AND cf.run_id = r.id
  GROUP BY
    cf.business_date,
    model,
    cf.ai_platform_id,
    site
);