To Calculate Value based on the stock available On Snowflake

Question

I have the below table

ID	Week_No	Value	Available_Stock
1	1	200	1000
1	2	300	1000
1	3	100	1000
1	4	400	1000
1	5	500	1000

I want a table like this

ID	SUM(VALUE)	Stock_Lasts_Weeks	Available_Stock
1	800	4 Weeks	1000

As my Available_Stock is 1000 and on the 5th week my Value exceeds 1000 my stock would lasts for 4 weeks only. I would like to calculate this on Snowflake. I will have to add the value and check if the value exceeds the available_stock then the previous_week before exceeding should be chosen.

Answer 1

To calculate the running total across weeks, use the window function sum():

row number() was used to enumerate rows in order to get number of weeks the stock has last.

with cte as (
  select *, sum(Value) over (order by Week_No) as running_sum,
            row_number() over (order by Week_No) as rn
  from mytable
  where ID = 1
)
select ID, running_sum, rn as Stock_Lasts_Weeks, Available_Stock
from cte
where running_sum <= Available_Stock
order by Week_No desc
limit 1;

If you want to handle multiple products at time then try this query :

with cte as (
  select *, sum(Value) over (partition by ID order by Week_No) as running_sum,
            row_number() over (partition by ID order by Week_No) as rn
  from mytable
),
cte2 as (
  select *, row_number() over (partition by ID order by running_sum desc) as rn_rs
  from cte
  where running_sum <= Available_Stock
)
select ID, running_sum, rn as Stock_Lasts_Weeks, Available_Stock
from cte2
where rn_rs = 1;

Answer 2

SelVazi has a good answer if you have only one ID/product. If there are multiple products, you can use the following query:

with cte as (
  select *, sum(Value) over (partition by ID order by Week_No) as running_sum,
            row_number() over (partition by ID order by Week_No) as rn
  from StockData
)
select ID, MAX(running_sum) , MAX(rn) as Stock_Lasts_Weeks, ANY_VALUE( Available_Stock )
from cte
where running_sum <= Available_Stock
group by ID
order by ID;

+----+------------------+-------------------+------------------------------+
| ID | MAX(RUNNING_SUM) | STOCK_LASTS_WEEKS | ANY_VALUE( AVAILABLE_STOCK ) |
+----+------------------+-------------------+------------------------------+
|  1 |            1,000 |                 4 |                        1,000 |
|  2 |            1,200 |                 3 |                        1,200 |
+----+------------------+-------------------+------------------------------+

Answer 3

You can use two QUALIFY clauses:

• one to filter out all "value" values that exceed your running sum
• one to filter the last "value" for each partition

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY week_no) AS rn
    FROM tab
    QUALIFY available_stock - SUM(value) OVER(PARTITION BY id ORDER BY week_no) < 0
)
SELECT id, 
       value, 
       COUNT(value) OVER(PARTITION BY id) AS Stock_Lasts_Weeks, 
       available_stock
FROM cte
QUALIFY rn = MAX(rn) OVER(PARTITION BY id ORDER BY week_no)