CODEWITHSUNDEEP
sqlpostgresql
sqlnoobie2021
sqlnoobie2021

Reputation: 1

Comparing stock levels with WINDOW functions

I have data that looks like this:

sku stock_level date
widget 15 4/7
widget 12 4/8
widget 10 4/9
widget 9 4/10
widget 25 4/11
widget 22 4/12

I’m trying to solve a problem that’s similar to the ‘Comparing close prices with WINDOW functions’ problem given here: https://medium.datadriveninvestor.com/sql-for-stock-market-analysis-f2145031e125

Basically, I want to know the change in stock level of a widget from the previous day. I figured out that part by following the example in the link, using the LAG function.

I have a requirement that I’m trying to implement: When the change is positive (e.g. change is +16 on 4/11), I want to instead return the average change of the last 3 days, rather than +16.

This is what I have so far:

SELECT
   sku,
   created_at,
   ROUND(stock_level, 2) AS actual_level,
   ROUND(LAG(stock_level, 1) OVER (
      ORDER BY created_at), 2) AS one_day_before_level
FROM pings
WHERE sku = 'widget'
)
SELECT
   sku,
   (actual_level - one_day_before_level) AS change,
   created_at
FROM temp_table;

Returns:

sku change date
widget null 4/7
widget -3 4/8
widget -2 4/9
widget -1 4/10
widget 16 4/11
widget -3 4/12

I want to ultimately return this:

sku change date
widget null 4/7
widget -3 4/8
widget -2 4/9
widget -1 4/10
widget -2 (This is average change of widget over the last 3 days) 4/11
widget -3 4/12

What is a good method to calculate the average change of the last 3 days, whenever I encounter a positive change? Is this possible? I'm using Postgres if that matters.

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You could do this:

select p.*,
       (case when lag(stock_level) < stock_level
             then (lag(stock_level, 1) - lag(stock_level, 4) ) / 3
             else stock_level - lag(stock_level)
        end) as diff
from pings p
where sku = 'widget';

The arithmetic expression in the then clause is just another way of calculating the average you want.

Upvotes: 1

Related Questions