Select two columns group by (city,sku) , but just show the top 5 registers by (city)

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

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