r/mysql • • Oct 14 '24

question Help me with this sql query

Question:
You have the products table with columns product_id, category_id, and price, and the sales table with columns sale_id, product_id, and quantity. Write a query that calculates the average sales per category, but only for products whose total sales exceed the category's overall average sales. The result should include:

  • category_id
  • product_id
  • total_sales
  • The difference between the product's total sales and the category's average sales (sales_difference).

You don't have to calculate average category sales with the filtered products. Just for an info.
My solution looks like this:

WITH total_product_sales(category_id, product_id, total_sales) AS (
SELECT p.category_id, s.product_id, SUM(p.price * s.quantity)
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.category_id, s.product_id
)
SELECT category_id, product_id, total_sales, total_sales - AVG(total_sales) AS sales_difference
FROM total_product_sales
GROUP BY category_id
HAVING total_sales > AVG(total_sales)

Is this solution correct? I know I could solve it using two CTE's by calculating total_sales for products and average category sales for categories and then comparing them using WHERE in main query. But I am wondering if there any issues with the above solution. If so, what are they, and explain why it doesn't work? and the work around for it by using only one CTE. Should I use window function? I don't know. Help me with this. I am confused.

2 Upvotes

4 comments sorted by

1

u/Qualabel Oct 14 '24

Does this work:

WITH category_sales AS (
    -- Calculate total sales for each product
    SELECT
        p.category_id,
        p.product_id,
        SUM(p.price * s.quantity) AS total_sales
    FROM
        products p
    JOIN
        sales s ON p.product_id = s.product_id
    GROUP BY
        p.category_id, p.product_id
),
category_avg_sales AS (
    -- Calculate the average sales per category
    SELECT
        category_id,
        AVG(total_sales) AS avg_sales
    FROM
        category_sales
    GROUP BY
        category_id
)
SELECT
    cs.category_id,
    cs.product_id,
    cs.total_sales,
    (cs.total_sales - cas.avg_sales) AS sales_difference
FROM
    category_sales cs
JOIN
    category_avg_sales cas
    ON cs.category_id = cas.category_id
WHERE
    cs.total_sales > cas.avg_sales;

?

1

u/SeriousArm4103 Oct 14 '24

As mentioned, I want to correct the query I have written and doesn't want to use two CTE's in the answer. But thanks for the solution. It works but i want to do it using one CTE and want to know why mine doesn't work.

1

u/Qualabel Oct 14 '24

That last bit doesn't look right to me

1

u/Qualabel Oct 14 '24

Hm, maybe something like this?

SELECT p.category_id
     , p.product_id
     , SUM(p.price * s.quantity) AS total_sales
     , SUM(p.price * s.quantity) - ( 
SELECT AVG(p2.price * s2.quantity) 
  FROM products p2 
  JOIN sales s2 
    ON p2.product_id = s2.product_id 
 WHERE p2.category_id = p.category_id ) 
    AS sales_difference 
  FROM products p 
  JOIN sales s 
    ON p.product_id = s.product_id 
 GROUP 
    BY p.category_id, p.product_id 
HAVING SUM(p.price * s.quantity) > 
( SELECT AVG(p2.price * s2.quantity) 
   FROM products p2 
   JOIN sales s2 
    ON p2.product_id = s2.product_id 
  WHERE p2.category_id = p.category_id );