CODEWITHSUNDEEP
sqlgoogle-bigquery
bunny
bunny

Reputation: 354

Calculate top two performing product categories from Sales data

I am trying to build a KPI of top 2 performing product categories for each customer. I have sales data with following relevant columns - customerid, product, product_category, order_qty, product_amt , order_date

I am using legacy SQL syntax in BQ.

Upvotes: 0

Views: 238

Answers (1)

SarfarazSoomro
SarfarazSoomro

Reputation: 413

This is a possible solution...

SELECT
  customer_id,
  product_category,
  order_qty
FROM (
  SELECT
    customerid, 
    product_category, 
    SUM(order_qty) AS order_qty,
    ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY order_qty DESC) AS rn
  FROM
    [project:dataset.table] 
  GROUP BY
    1, 2
)
WHERE
  rn <= 2
ORDER BY
  1, 3 DESC

Upvotes: 4

Related Questions