CODEWITHSUNDEEP
sqlgoogle-bigquery
Kim Baas
Kim Baas

Reputation: 15

Google Big Query - SQL Dynamically distribute value across (week)days

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..:

Example as-is

Example to-be

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

Answers (3)

Sabri Karagönen
Sabri Karagönen

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.

Output looks like this: Query Output

Upvotes: 0

Gordon Linoff
Gordon Linoff

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

Akina
Akina

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

Related Questions