I have a table in big query with the following fields:

1. (Integer) run_id
2. (Integer) project_id
3. (Integer) clicks
4. (Integer) impressions
5. (Float) ctr
6. (Float) position
7. (String) query
8. (String) country
9. (String) page

Each page has many keywords with x impressions. I want to get the top 5 keywords of each page on this table from the last run. With this query I get the top 5 keywords to a specific page, but how do I have to write the query to get all pages with there 5 top keywords?

SELECT  a.page, a.query, a.impressions
FROM `strategic-kite-11111.test_data.gsc_data` a,
`strategic-kite-11111.test_data.runs` b
WHERE a.run_id = b.id
AND a.page = "https://www.xxxxx.com"
AND b.end_date = "2022-04-24"
AND b.project_id = a.project_id
ORDER BY a.impressions DESC
LIMIT 5

THX for your help best regards Michael

CodePudding user response：

I think this would work for you:

select  t.page, t.query, t.impressions from (
SELECT  a.page, a.query, a.impressions,
 row_number() over(partition by a.page order by b.end_date desc, a.impressions desc) rn
FROM `strategic-kite-11111.test_data.gsc_data` a,
`strategic-kite-11111.test_data.runs` b
WHERE a.run_id = b.id
AND b.project_id = a.project_id
) t where t.rn<=5