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.

if I understand correctly sum(VALUE) should be 1000 not 800 ? — SelVazi, May 23 '23 at 08:52
Yes.. Let me re-phrase it.. How long will my stock last in weeks has to be calculated — Anonymous, May 23 '23 at 10:01

SelVazi · Answer 1 · 2023-05-23T10:47:03.057

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;

score 0 · Answer 2 · answered May 23 '23 at 09:37

0

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 |
+----+------------------+-------------------+------------------------------+

answered May 23 '23 at 09:37

Gokhan Atil

9,278
2
11
24

what if the available stock and few other columns are coming from a different table.. I would need to use a join right.. How do I use the group by or where clause then? – Anonymous May 23 '23 at 10:58
@Anonymous you need to JOIN these tables. You don't need to change anything on the rest if you do the join in CTE, but you can enter the name of the column insted of using "*". – Gokhan Atil May 23 '23 at 11:30
How do I calculate the Stock left in weeks where my Month_value has to reset for that month and the stock left can be calculated from the values starting from that month to how much stock is left. It can take the next month values also until the value should not go over the stock value. (Sorry, I am not able to post a new question or put this in a table) – Anonymous Jun 05 '23 at 11:06

lemon · Answer 3 · 2023-05-23T11:47:58.997

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)

To Calculate Value based on the stock available On Snowflake

3 Answers3