Top 20 Ranking Results Report Query And Template

This report includes only one row per ranking position.

WITH

-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.nozzle_reidemo.rankings t
WHERE requested >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)  GROUP BY keyword_id, requested
),

latest_filtered_rankings_results AS (
  SELECT
    requested AS date,
    phrase,
    device,
    -- location_type,
    -- location,
    (SELECT STRING_AGG(kg, '; ') FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- ')) AS keyword_groups,
    keyword_metrics.adwords_search_volume AS search_volume,

    -- result data
    result.title.text as title,
    result.url.domain as domain,
    result.url.url as url,
    result.rank AS rank,

  FROM latest_filtered_rankings
  JOIN UNNEST(results) AS result
  JOIN nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id USING (keyword_id)
  WHERE result.rank BETWEEN 1 AND 20
  AND result.item_rank = 0
)

SELECT * FROM latest_filtered_rankings_results
ORDER BY phrase, device, date, rank