Suppose I have the following product sales data:
Products:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Widget A | 1 |
| 2 | Widget B | 1 |
| 3 | Gizmo | 2 |
| 4 | Gadget | 2 |
| 5 | Doohickey | 3 |
Categories:
| category_id | category_name |
|---|---|
| 1 | Electronics |
| 2 | Toys |
| 3 | Home & Garden |
Sales:
| sale_id | product_id | quantity | sale_date | sale_price |
|---|---|---|---|---|
| 101 | 1 | 10 | 2024-04-01 | 19.99 |
| 102 | 2 | 5 | 2024-04-02 | 24.99 |
| 103 | 3 | 7 | 2024-04-03 | 15.99 |
| 104 | 4 | 4 | 2024-04-04 | 20.99 |
| 105 | 5 | 3 | 2024-04-05 | 29.99 |
I now want to know the sales of each product and its percentage of total sales.
I tried the following query and got the expected results, but I think the execution time is a bit long, especially when the table becomes larger, is there anything that can be optimized?(BTW, I’m using PieCloudDB Database, if you don’t know it you can test with PostgreSQL instead.)
Thanks in advance for your help
<code>WITH TotalSales AS (
SELECT SUM(sale_price * quantity) AS overall_sales
FROM sales
),
CategorySales AS (
SELECT
c.category_name,
p.product_name,
SUM(s.sale_price * s.quantity) AS category_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
CROSS JOIN TotalSales
GROUP BY c.category_name, p.product_id, p.product_name
)
SELECT
product_name,
category_name,
category_sales,
ROUND((category_sales * 100.0) / overall_sales, 2) AS percentage_of_total
FROM CategorySales, TotalSales
ORDER BY category_sales DESC;
</code>
<code>WITH TotalSales AS (
SELECT SUM(sale_price * quantity) AS overall_sales
FROM sales
),
CategorySales AS (
SELECT
c.category_name,
p.product_name,
SUM(s.sale_price * s.quantity) AS category_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
CROSS JOIN TotalSales
GROUP BY c.category_name, p.product_id, p.product_name
)
SELECT
product_name,
category_name,
category_sales,
ROUND((category_sales * 100.0) / overall_sales, 2) AS percentage_of_total
FROM CategorySales, TotalSales
ORDER BY category_sales DESC;
</code>
WITH TotalSales AS (
SELECT SUM(sale_price * quantity) AS overall_sales
FROM sales
),
CategorySales AS (
SELECT
c.category_name,
p.product_name,
SUM(s.sale_price * s.quantity) AS category_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
CROSS JOIN TotalSales
GROUP BY c.category_name, p.product_id, p.product_name
)
SELECT
product_name,
category_name,
category_sales,
ROUND((category_sales * 100.0) / overall_sales, 2) AS percentage_of_total
FROM CategorySales, TotalSales
ORDER BY category_sales DESC;