Extract the vendors with highest increase in total revenue from order table, divided by year/country/vendor - bigquery SQL

Question

The original ORDERS table looks like this :

order	country_name	date_local	vendor_name	gmv_local
01	Taiwan	2012-10-02	Hse A	559.6
02	Taiwan	2012-10-02	Hse B	573.5
03	Taiwan	2012-10-02	Hse A	559.6
04	Taiwan	2012-10-02	Hse B	573.5
01	Singapore	2012-10-02	Hse H	778.6
02	Singapore	2014-10-02	Hse K	120.6
03	Singapore	2012-10-02	Hse H	778.6
04	Singapore	2014-10-02	Hse K	120.6
01	Bangkok	2014-10-02	Hse D	563.6

I wanted to extract the top 2 vendors of each country with highest increase of total_gmv (a new column), total_gmv is created by ROUND(SUM(Ord.gmv_local), 2) AS total_gmv

I wanted to create a new column diff_gmv, where the total_gmv by country, by year and by vendor,

The resulting table should be something like this

Year	country_name	vendor_name	total_gmv	2012_total_gmv	diff_gmv
2014	Singapore	HSE A	2119.76	1119.76	1000
2014	Singapore	HSE A	1819.63	819.63	1000
2014	Taiwan	HSE C	1019.6	119.6	1000
2014	Taiwan	HSE D	819.6	119.6	700
2014	Bangkok	Hse D	1289.6	null	1289.6

Note that Bangkok does not have business in 2012 and thus only 1 vendor is shown

My current code (note that the original table has a LEFT JOIN, year is extracted from a timestamp)

SELECT
    EXTRACT(year FROM date_local) AS year,
    Ord.country_name,
    vn.vendor_name,
    ROUND(SUM(Ord.gmv_local), 2) AS total_gmv,
    (SUM(CASE WHEN EXTRACT(year FROM date_local) = 2014 THEN Ord.gmv_local ELSE 0 END)) - (SUM(CASE WHEN EXTRACT(year FROM date_local) = 2012 THEN Ord.gmv_local ELSE 0 END)) AS diff_gmv
FROM `Orders` AS Ord
LEFT JOIN `Vendors` AS vn
    ON Ord.vendor_id = vn.id
GROUP BY
    Ord.country_name,
    vn.vendor_name,
    year
QUALIFY ROW_NUMBER() OVER (PARTITION BY Ord.country_name ORDER BY (SUM(CASE WHEN EXTRACT(year FROM date_local) = 2014 THEN Ord.gmv_local END)) - (SUM(CASE WHEN EXTRACT(year FROM date_local) = 2012 THEN Ord.gmv_local END)) DESC) <= 2
ORDER BY
    year, Ord.country_name,
    total_gmv DESC;

My "diff_gmv" is not working and it is showing

Year	country_name	vendor_name	total_gmv	diff_gmv
2014	Singapore	HSE A	2119.76	-2119.76
2014	Singapore	HSE A	1819.63	-1819.63
2014	Taiwan	HSE C	1019.6	-1019.6
2014	Taiwan	HSE D	819.6	-819.6
2014	Bangkok	Hse D	1289.6	-1289.6

score 0 · Answer 1 · answered Jun 23 '21 at 00:17

Something like this should get you going...

with data as (
    #fill this in with your data/joined table
),
gmv_by_country_vendor_year as (
    select 
        extract(year from date_local) as year,
        country_name,
        vendor_name,
        sum(gmv_local) as total_gmv
    from data
    group by 1,2,3
),
gmv_by_country_vendor_2014 as (
    select * from gmv_by_country_vendor_year where year = 2014
),
gmv_by_country_vendor_2012 as (
    select * from gmv_by_country_vendor_year where year = 2012
),
joined as (
    select l.*, r.total_gmv as total_gmv_2012, ifnull(l.total_gmv,0) - ifnull(r.total_gmv,0) as diff_gmv
    from gmv_by_country_vendor_2014 l
    left join gmv_by_country_vendor_2012 r using(year,country_name,vendor_name)
),
ranked as (
    select *, row_number() over(partition by year,country_name,vendor_name order by diff_gmv desc) as rn
    from joined
)
select * except(rn) from ranked
where rn <= 2
order by year, country_name, diff_gmv desc

Extract the vendors with highest increase in total revenue from order table, divided by year/country/vendor - bigquery SQL

1 Answers1