There are multiple suppliers and each has sold multiple products. How would I find the percentage of the revenue of one of their products against that supplier's total sales in SQL?
Here is an example of data I have to work with:
| supplier | product | price of product | quantity sold |
|---|---|---|---|
| Supplier A | Apples | $2 | 1 |
| Supplier A | Shoes | $3 | 2 |
| Supplier B | Shirts | $1 | 3 |
The revenue of Supplier A's apples should be $2, their total revenue is $8. So the percentage of Supplier A's apples against total sales should be 25%.
You would use divide values . . . and use window functions:
select supplier, product, sum(price * quantity),
sum(price * quantity) / sum(sum(price * quantity)) over (partition by supplier) as ratio
from t
group by supplier, product;