CODEWITHSUNDEEP
sqldjangopostgresqlcumulative-sum
zeta
zeta

Reputation: 39

Cumulative sum SQL query for calculate sales contribution with Postgres

Based on this table I need to calculate how much each products contributes to the total sales and be able to filter the for these products that constitute the 80% of contribution 

| prod_id | sales date | units_sold | total_price |
|---------+------------+------------+-------------|
|       1 | 2020-04-20 |         10 |      100.00 |
|       1 | 2020-04-19 |          2 |       20.00 |
|       2 | 2020-04-20 |          5 |       15.00 |
|       2 | 2020-04-19 |          5 |       15.00 |
|       3 | 2020-04-20 |         25 |       25.00 |
|       3 | 2020-04-19 |         25 |       25.00 |

Considering the total Sales is equal to $200 the Contribution % is calculated as:

The Accumulate Contribution % column is the accumulated sum of Contribution % ordering by this column from higher to lower values

The resulting query should looks similar to the one below 

| prod_id | units_sold | total_price | Contribution % | Accumulate Contribution % |
|---------+------------+-------------+----------------+---------------------------|
|       1 |         12 |         120 |            0.6 |                       1.0 |
|       3 |         50 |          50 |           0.25 |                       0.4 |
|       2 |         10 |          30 |           0.15 |                      0.15 |

Upvotes: 0

Views: 937

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If I understand correctly, this is an aggregation with a cumulative sum:

select prod_id, sum(units_sold), sum(total_price),
       sum(total_price) / sum(sum(total_price)) over () as contribution_ratio,
       sum(total_price) / sum(sum(total_price)) over (order by sum(total_price) as accumulated
from t
group by prod_id
order by sum(total_price) desc;

Upvotes: 1

Related Questions