40 lines
1.1 KiB
SQL
40 lines
1.1 KiB
SQL
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
|
|
);
|