Window functions para ranking de URLs por categoría

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

-- Window functions: ranking, comparación y acumulados por categoría
-- Cada función de ventana añade una perspectiva analítica diferente
WITH urls_categorias AS (
  SELECT
    url,
    -- Extraer la categoría del primer segmento del path
    REGEXP_EXTRACT(url, r'https?://[^/]+/([^/]+)') AS categoria,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones
  FROM
    `your-project.your_dataset.search_data`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  GROUP BY
    url
  HAVING
    clics >= 5
)
SELECT
  url,
  categoria,
  clics,
  -- Ranking dentro de cada categoría
  RANK() OVER (PARTITION BY categoria ORDER BY clics DESC) AS ranking,
  -- Total de clics de la categoría
  SUM(clics) OVER (PARTITION BY categoria) AS clics_categoria,
  -- Porcentaje de clics respecto a su categoría
  ROUND(clics * 100.0 / SUM(clics) OVER (PARTITION BY categoria), 2) AS pct_categoria,
  -- Diferencia con la URL anterior en el ranking
  clics - LAG(clics, 1, 0) OVER (
    PARTITION BY categoria ORDER BY clics DESC
  ) AS dif_con_anterior,
  -- Acumulado de clics dentro de la categoría
  SUM(clics) OVER (
    PARTITION BY categoria ORDER BY clics DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS clics_acumulados
FROM
  urls_categorias
WHERE
  categoria IS NOT NULL
QUALIFY
  ranking <= 5
ORDER BY
  categoria, ranking