How do you calculate the minimum number of sales it took to reach a sales goal of 100 for each month?

Question

Write a query to find out how many sales at a minimum it took each month to reach a cumulative sale of more than 100.

Table Code:

DROP TABLE IF EXISTS q1sales;

CREATE TABLE q1sales (
year double precision,
month integer,
sales integer
);

INSERT INTO q1sales VALUES 
(2019, 1, 37),
(2019, 1, 63),
(2019, 1, 22),
(2019, 1, 27),
(2019, 2, 27),
(2019, 2, 40),
(2019, 2, 76),
(2019, 2, 24),
(2019, 3, 46),
(2019, 3, 74),
(2019, 3, 23),
(2019, 3, 95);

Expected output:

year month min_num_of_sales
2019     1                3
2019     2                2
2019     3                2

For example, during the month of January (1), it took 3 sales(37,63,22 -or- 37+63+27) to reach over 100 in sales.

Here is what I have tried:

select year, month, (
select 
count(s) filter(where month = 1) and s in 
(select sum(s) as sums from sale2 where sums > 100)
from sale2
) 
from sale2;

But this is not correct, and I am not sure where to start with this question.

@Strawberry: given the `filter` clause to the aggregate funtion, this has to be Postgres. — GMB, Oct 16 '20 at 16:15

GMB · Accepted Answer · 2020-10-16T16:23:49.340

You can use window functions. The idea is to enumerate the rows in each month, starting from the greatest sale, and to compute the corresponding running sum. You can then filter on the row when the threshold is reached:

select year, month, rn min_num_of_sales
from (
    select s.*, 
        sum(sales) over(partition by year, month order by sales desc) running_sales,
        row_number() over(partition by year, month order by sales desc) rn
    from q1sales s
) t
where running_sales > 100 and running_sales - sales <= 100

Demo on DB Fiddle:

year | month | min_num_of_sales
:--- | ----: | ---------------:
2019 |     1 |                3
2019 |     2 |                2
2019 |     3 |                2

How do you calculate the minimum number of sales it took to reach a sales goal of 100 for each month?

1 Answers1