I need to do a query to show the top seller products group by city. The “trap” is, I just can show the top 5 by city:
I start using this query:
select ORD.shipping_address_city city, ORDL.sku, sum(ORDL.price) SALES from database.ORDER ORD inner join database.ORDER_LINE ORDL on ORD.ID = ORDL.order_id WHERE shipping_address_city is not null group by ORD.shipping_address_city, ORDL.sku order by ORD.shipping_address_city, SALES desc , ORDL.sku
This query is showing me a result like that:
city sku SALES New York FOUN-SUN-08 4225 New York FOUN-SUN-07 3900 New York FOUN-BTR-08 3462.5 New York FOUN-SUN-09 3325 New York GEMM-BTR-08 2977.5 New York JO-BLE-09 2731.25 New York DEM-BLK-08 2730 New York GEMM-SBL-08 2675 New York FOUN-BTR-07 2300 New York GEMM-SBL-07 2065 New York LINE-SBL-07 2035.5 Malibu JO-HAZ-07 95 Malibu STUD-SAB-07 68 Malibu NOR-LIT-07 65 Malibu NOR-TAR-07 65 Malibu GEMM-BTR-07 50 Malibu GEMM-SBL-07 50 Malibu RILE-BTR-07 50 Malibu RILE-SBL-07 50 Malibu SOLI-NO17-07 45 Toronto SOLI-NO19-06 100 Toronto MUN-UTY-T13 68 Toronto BJN-WOLF-T11/12 55 Toronto FOUN-TRO-06 50 Toronto MB-LIL-06 50 Toronto KTKE-BTR-T13 44 Toronto MINI-ZINK-T13 44
But I need to show just the top 5 by city, like that:
city sku SALES New York FOUN-SUN-08 4225 New York FOUN-SUN-07 3900 New York FOUN-BTR-08 3462.5 New York FOUN-SUN-09 3325 New York GEMM-BTR-08 2977.5 Malibu JO-HAZ-07 95 Malibu STUD-SAB-07 68 Malibu NOR-LIT-07 65 Malibu NOR-TAR-07 65 Malibu GEMM-BTR-07 50 Toronto SOLI-NO19-06 100 Toronto MUN-UTY-T13 68 Toronto BJN-WOLF-T11/12 55 Toronto FOUN-TRO-06 50 Toronto MB-LIL-06 50
Advertisement
Answer
Below is for BigQuery Standard SQL
I assume your initial query properly reflects your business logic – so I didn’t want to touch it – rather I propose to wrap your code with below (with exception for ORDER BY which I moved out to apply to final output)
SELECT sale.* FROM ( SELECT ARRAY_AGG(t ORDER BY sales DESC LIMIT 5) top_cities FROM ( -- start of your initial query here select ORD.shipping_address_city city, ORDL.sku, sum(ORDL.price) SALES from database.ORDER ORD inner join database.ORDER_LINE ORDL on ORD.ID = ORDL.order_id WHERE shipping_address_city is not null group by ORD.shipping_address_city, ORDL.sku -- end of your initial query here ) t GROUP BY city ) t, UNNEST(top_cities) sale -- ORDER BY city, sales DESC, sku
Note: obviously above approach can be applied directly to your initial query – but I am leaving it to you