Is There A Way To Show Only The Top 2/nth Of A Query After "group By" Country? - Bigquery Sql

I am doing a query on Google Big query, I have joined the 2 tables and created a new column 'total gmv' using 'SUM' to represent the total revenue, now I wanted to show only the to

Solution 1:

Use ROW_NUMBER:

WITH cte AS (
    SELECT Ord.country_name, vn.vendor_name, ROUND(SUM(Ord.gmv_local), 2) AS total_gmv,
           ROW_NUMBER() OVER (PARTITIONBY Ord.country_name
                              ORDERBYSUM(Ord.gmv_local) DESC) rn
    FROM ORDERS AS Ord
    LEFTJOIN `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` AS vn
        ON Ord.vendor_id = vn.id
    GROUPBY Ord.country_name, vn.vendor_name
)

SELECT country_name, vendor_name, total_gmv
FROM cte
WHERE rn <=2ORDERBY country_name, total_gmv DESC;

Solution 2:

Below is the way to go - just one extra line in your code QUALIFY ROW_NUMBER() OVER(PARTITION BY country_name ORDER BY total_gmv DESC) <= 2

So, the whole quesry now will be

SELECT 
  Ord.country_name, 
  vn.vendor_name, 
  round(sum(Ord.gmv_local),2) as total_gmv 
FROM ORDERS as Ord
LEFTJOIN `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` as vn
ON Ord.vendor_id = vn.id
GROUPBY Ord.country_name, vn.vendor_name
QUALIFY ROW_NUMBER() OVER(PARTITIONBY country_name ORDERBY total_gmv DESC) <=2ORDERBY Ord.country_name desc, total_gmv desc

If applied to sample data in your question - output is