2021-12-10

Tech Programing

程式人小天地

Bigquery SQL – List the top vendors in each country, for each year in the dataset

2 min read


I need to join 2 tables and extract the top vendor by country by year.

The original ORDERS table looks like this :

country_name date_local vendor_id gmv_local
Taiwan 2012-10-02 2870 559.6
Taiwan 2012-10-02 3812 573.5
Singapore 2012-10-02 941 778.6
Singapore 2014-10-02 13 120.6
Thailand 2014-10-02 227 563.6

This table is merged with the vendor table
id vendor_name country_name
2870 A House Taiwan
941 B House Singapore
227 C House Thailand

I would like to extract the year from the “date_local” column into timestamp format, where the items in the Year column will look like “2012-01-01T00:00:00” from the original date format of “2012-10-02”

Then I would like to list out the top 2 vendors by year for each country in total gmv.

The resulting table should look like this:
year country_name vendor_name total_gmv
2012-01-01T00:00:00 Singapore A House 1119.76
2012-01-01T00:00:00 Singapore B House 819.63
2012-01-01T00:00:00 Taiwan C House 119.6
2012-01-01T00:00:00 Taiwan D House 119.6
2012-01-01T00:00:00 Bangkok 9 House 119.6
2014-01-01T00:00:00 Singapore A House 2119.76
2014-01-01T00:00:00 Singapore B House 1819.63
2014-01-01T00:00:00 Taiwan C House 1019.6
2014-01-01T00:00:00 Taiwan D House 919.6
2014-01-01T00:00:00 Bangkok 9 House 189.6

Based on, among others, some previous guidance here, I’ve come up with the following query:

SELECT
Ord.country_name,
vn.vendor_name,
EXTRACT(year FROM date_local) AS year,
ROUND(SUM(Ord.gmv_local), 2) AS total_gmv
FROM ORDERS AS Ord
LEFT JOIN primeval-falcon-306603.foodpanda_BI_Exercise.Vendors AS vn
ON Ord.vendor_id = vn.id
GROUP BY
Ord.country_name,
vn.vendor_name,
EXTRACT(year FROM date_local)
QUALIFY ROW_NUMBER() OVER (PARTITION BY country_name, EXTRACT(year FROM date_local)
ORDER BY total_gmv DESC) <= 2
ORDER BY
Ord.country_name DESC,
total_gmv DESC;

But keep getting this error message: SELECT list expression references column date_local which is neither grouped nor aggregated at [4:23]

So unfortunately not even able to see if I’m getting the result I’m looking for.

Any advice would be much appreciated



Source link

資料來源:Stackoverflow

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *