I have the following table:
date | ratio | revenue
---------|-------|-----------
03-30-18 | 1.2 | 918264
03-31-18 | 0.94 | 981247
04-01-18 | 1.1 | 957353
04-02-18 | 0.99 | 926274
04-03-18 | 1.05 |
04-04-18 | 0.97 |
04-05-18 | 1.23 |
As you can see, 04-03-18 and beyond haven't happened yet so there is no revenue input for those days. But I have a ratio for those future days. I want to use the AVG revenue of the last 4 days that I do have and multiply it by the ratio to make future revenue predictions.
In result, I wish to have the following table:
date | ratio | revenue
---------|-------|-----------
03-30-18 | 1.2 | 918264
03-31-18 | 0.94 | 981247
04-01-18 | 1.1 | 957353
04-02-18 | 0.99 | 926274
04-03-18 | 1.05 | 993073.73
04-04-18 | 0.97 | 917410.97
04-05-18 | 1.23 | 1163314.94
I don't see a need for window functions, so I would phrase this as:
select t.date, t.ratio,
coalesce(t.revenue, a.avg4 * ratio) as revenue
from t cross join
(select avg(revenue) as avg4
from (select t.*
from t
where t.revenue is not null
order by date desc
limit 4
) t
) a
order by date;