There are two Tables - orders and item_line

orders

item_line

The question is: Find the products contributing to the 50% of the total sales.

My take on this is -

SELECT i.product_name,SUM(o.total_amount)AS 'Net Sales'
FROM item_line i
JOIN orders o on o.order_id = i.order_id
GROUP BY i.product_name
HAVING SUM(o.total_amount) = (SUM(o.total_amount)*0.5);

But this is not correct. SUM windows functions need to be used, but how?

CodePudding user response：

Try the following, explanation is within the query comments:

-- find the the total sales for each product
WITH product_sales AS
(
  SELECT product_id, product_name, 
         SUM(quantity * unit_price) AS product_tot_sales
  FROM item_line
  GROUP BY product_id, product_name
),
-- find the running sales percentage for each product starting from porduct with highest sales value
running_percentage AS
(
  SELECT product_id, product_name, product_tot_sales,
         SUM(product_tot_sales) OVER (ORDER BY product_tot_sales DESC) /
         SUM(product_tot_sales) OVER () AS running_sales_percentage,
         SUM(product_tot_sales) OVER () AS tot_sales
  FROM product_sales
)
-- select products that have a running sales percentage less than the min(running_sales_percentage) where running_sales_percentage >= 0.5
-- this will select all of products that contributes of 0.5 of the total sales
SELECT product_id, product_name, product_tot_sales,
       tot_sales,
       running_sales_percentage 
FROM running_percentage
WHERE running_sales_percentage <= 
      (
       SELECT MIN(running_sales_percentage) 
       FROM running_percentage 
       WHERE running_sales_percentage >= 0.5
      )

You don't need a join with orders table, all data you need is existed in the item_line table.

See demo.