How to calculate running total and compare it to another column and retrieve in HIVE?

I have a table with 3 columns 1.Week 2.Sales 3.Opening Stock. I need to calculate 4th column which is Stock cover days.

Step1: To calculate Stock cover days for week1, i need to keep adding sum of sales for week1,week2 etc until it doesn’t exceed opening stock for week1.

For example, if my week1 opening stock is 500; week1 sales is 200 & week2 sales is 100 & week3 sales is 350 the sum of sales should be only week1 and week2 which is 300 < 500.

Step2: After finding the sum of sales, i need to find the next week sales (here it is week3 = 350) and divide it by 7 which gives per day value (50)

Step3: I need to add the sum of sales until the value is less than opening stock. 4 days of stock would be 200 which when added to earlier um of sales will be 500 = opening stock for week 1.

Step4: Now i need to calculate total number of days to acheive this opening stock. we have 2 weeks considered earlier (week1 = 200 ; week2 =100). so it is 7+7=14 days Then add the number of days from step3 which is 4 in this case. So total stock cover days would be 14+4=28 days

column 4 for week1 should have the value as 28 as result.

Regards, Mechster

© Copyright 2013-2019 Analytics Vidhya