I am not sure if this makes any sense to want to do. I come from a programmer background and for me this should make sense to do. I have basic SQL experience so i know in basis what i am doing, but data modeling for analytics is mostly new and foreign to me.
I have the following tables:
- Campaign: id, name, etc.
- Daily_Spending: id, campaign_id, date, spending
- Daily_Revenue: id, campaign_id, date, revenue
I now want to join these tables many_to_one on campaign_id to effectively create a result derived table that looks like:
Daily_Campaign_Data: campaign_id, date, spending, revenue
Where spending and revenue are effectively NULL or 0 if they did not exist for a specific date.
First of all: Does it make sense to do this just to get a neat table which basically contains all the dates that there was spending and/or revenue going on.
Second: What would be the way to go about this? Since i don't seem to find anything about merging those date fields into one if they co-exist like described.
And Third: Would there be alternatives that make more sense in the database world?
You need a list of available dates. Then use a cross join to get the combinations of campaigns and dates and finally left joins to bring in the data you want.
Assuming campaign/dates are not duplicated in the spending and revenue tables:
select d.date, c.*,
ds.spending, dr.revenue
from campaign c cross join
(select date from daily_spending
union -- on purpose to remove duplicates
select date from daily_revenue
) d left join
daily_spending ds
on ds.campaign_id = c.id and ds.date = d.date left join
daily_revenue dr
on dr.campaign_id = c.id and dr.date = d.date;