How to order and limit for a unique column value SQL

Question

if I run the following query in sql (bigquery googleSQL) it will give me a list of sales per country for the current date

SELECT
    date,
    product,
    country,
    sum(total_sales) as total_sales
    FROM
    `international_sales`
    WHERE 
    date = '2024-10-19'
    AND
    total_sales > 0
    AND product in ( 'Product A', 'Product B')

date	product	country	total_sales
2024-10-19	Product A	United States	22222
2024-10-19	Product B	Canada	111111
2024-10-19	Product B	Mexico	333333

I want to be able to modify this so I only get the top ten highest countries for each product. if I was only querying one product it would be as simple as adding

order by total_sales desc limit 10;

but is made more difficult by the fact that I'm trying to query multiple products at once. How can I get around this?

Answer 1

As commented by @Paul Maxwell, for your requirement, you can include a row number calculation such as given below,

row_number() over(partition by date, product, country order by total_sales) as rn then filter for where rn <= 10 .

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future. Feel free to edit this answer for additional information.