Deduplicar registros en una tabla de keywords

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

-- Deduplicar keywords conservando el registro con más clics
-- Usa ROW_NUMBER para asignar un ranking por grupo
WITH ranked AS (
  SELECT
    keyword,
    url,
    clicks,
    impressions,
    position,
    data_date,
    -- Asigna un número de fila por keyword, priorizando más clics
    ROW_NUMBER() OVER (
      PARTITION BY LOWER(TRIM(keyword))
      ORDER BY clicks DESC, data_date DESC
    ) AS rn
  FROM
    `your-project.your_dataset.keywords_data`
)
SELECT
  keyword,
  url,
  clicks,
  impressions,
  position,
  data_date
FROM
  ranked
WHERE
  -- Conserva solo el primer registro de cada grupo
  rn = 1
ORDER BY
  clicks DESC
LIMIT 100