tables daily granularity left join monthly granularity

Question

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?

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