Reputation: 15
I have a dataset in Google Big Query which I need to manipulate in order to use it in Data Studio. It contains media spends, which are entered every wednesday, but need to be spread out to the previous week (wed-wed) so e.g. there is a spend of €7K on the 15th. That means that on the week before 8th up untill the 14th; the spend was €1K every day. The amount differ every week but are always entered on the wednesday..:
I used to do this in Google sheets where I used sumif looking at the weekday, and based on that weekday lookup the value of the date + X (so e.g. for a tuesday that would be date +1) and divide this amount by 7.
Now I would like to do the same, but in SQL and I am struggling to make it work. Is there an easy solution or do I need to build an alike formula using e.g. CASE WHEN based on weekday?
Thanks a lot for your help!
Upvotes: 1
Views: 569
Reputation: 2365
I prepared this query assuming data is always entered on the same week day (Wednesday, in your case), and you want to distribute it to previous 7 days.
WITH
data AS (
SELECT CAST('2020-04-08' as date) as Date, 5000 as Spend union all
SELECT CAST('2020-04-15' as date) as Date, 7000 as Spend
)
select DATE_SUB(Date, interval n_days day) as Date, Spend/7 as spend
from data
join unnest(generate_array(1, 7)) as n_days
So, input is pretty simple, just Wednesdays.
Upvotes: 0
Reputation: 1271241
You can write a query to do this. If you know that you have data for each week, you just want the most recent non-NULL value and to divide that by 7:
select t.*,
last_value(spend ignore nulls) over (order by date) / 7
from t;
If you might have dates that are missed or null values for the entire week, then the above won't work. A more general solution identifies the week and partitions by that:
select t.*,
max(spend) over (partition by date_trunc(date, week(Wednesday))) / 7
from t;
Upvotes: 1
Reputation: 42854
It seems that you need something like
UPDATE sourcetable t1
SET t1.Spend = t3.Spend
FROM ( SELECT t2.Date,
MAX(t2.Spend) OVER (ORDER BY t2.Date
ROWS BETWEEN 1 FOLLOWING
AND 7 FOLLOWING) Spend
FROM sourcetable t2 ) t3
WHERE t1.Date = t3.Date;
Upvotes: 0