0

I have a cost_table with columns in BigQuery:

country start_date_of_campaign end_date_of_campaign spend currency
DE 2022-10-05 2022-10-15 10 EUR
DK 2022-09-07 2022-09-23 20 DKK

After a cross join with date_dimensions table I have a cost_table_exploded on sort of daily granularity. All the dates between start_date and end_date of campaign get a new row. The column spend is converetd into total_spend (sum(spend)). Result:

country start_date_of_campaign end_date_of_campaign total_spend currency date
DE 2022-10-05 2022-10-07 10 EUR 2022-10-05
DE 2022-10-05 2022-10-07 10 EUR 2022-10-06
DE 2022-10-05 2022-10-07 10 EUR 2022-10-07
DK 2022-09-07 2022-09-23 20 DKK 2022-09-23

I need to enrich the cost_table_exploded with spend in euro.

I have a currency dimensions table that is on monthyl granularity with columns:

currency_name valid_from_date to_euro_conversion
DKK 2022-10-01 0,13460
DKK 2022-09-01 0,13450
DKK 2022-08-01 0,13445
DKK 2022-07-01 0,13455
EUR 2022-10-01 1
EUR 2022-09-01 1

How do I join table on monthly granularity to table on daily granularity?

Roza Ada
  • 1
  • 1

1 Answers1

1

I'm just going to focus on your real ask here:

How do I join table on monthly granularity to table on daily granularity?

In GBQ, when you join the ON condition is super powerful where you can apply all sorts of transforms on fields. So at the end if you need month to month or year to year or something like that with different units of data from different table you can bring it together into same unit when you are doing ON :

Something like the sample below:

with day_table1 as 
(select 1 as id, current_date() as today) ,

day_table2 as 
(select 2 as id, extract(MONTH FROM current_date()) as month, extract(YEAR FROM current_date()) as year)

select * from day_table1 as a 
join day_table2 as b on 
extract(MONTH FROM a.today) = b.month and 
extract(YEAR FROM a.today) = b.year

I will suggest you bring in year into the mix as well to avoid issues with month when year values become more than a year.

Pratik Patil
  • 612
  • 1
  • 5
  • 18