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