complicated.sql

with days as (
	select '2019-12-31'::date as date_day union all
	select '2020-01-01'::date as date_day union all
	select '2020-01-02'::date as date_day union all
	select '2020-01-03'::date as date_day union all
	select '2020-01-04'::date as date_day union all
	select '2020-01-05'::date as date_day
),
events as (
	select 1 as user_id, 'drew' as name, '2020-01-01'::date as event_time union all
	select 1 as user_id, 'draw' as name, '2020-01-02'::date as event_time union all
	select 1 as user_id, 'Drew' as name, '2020-01-03'::date as event_time
),
events_windowed as (
	select *,
		lead(event_time) over (partition by user_id order by event_time) as next_event_time
	from events
)

select
	user_id,
	name,
	event_time as valid_from,
	coalesce(next_event_time, '2099-12-31'::date) as valid_to

from events_windowed
join days on days.date_day >= events_windowed.event_time 
 and (
 	days.date_day < events_windowed.next_event_time or (
 		events_windowed.event_time = days.date_day and
 		events_windowed.next_event_time is null
 	)
 )
 
/*
1	drew	2020-01-01	2020-01-02
1	draw	2020-01-02	2020-01-03
1	Drew	2020-01-03	2099-12-31
*/

simple.sql

-- Create a simple "date spine" for example purposes
-- see: https://github.com/fishtown-analytics/dbt-utils#date_spine-source

with days as (
	select '2019-12-31'::date as date_day union all
	select '2020-01-01'::date as date_day union all
	select '2020-01-02'::date as date_day union all
	select '2020-01-03'::date as date_day union all
	select '2020-01-04'::date as date_day union all
	select '2020-01-05'::date as date_day
),

-- mock out some example events
events as (
	select 1 as user_id, 'drew' as name, '2020-01-01'::date as event_time union all
	select 1 as user_id, 'draw' as name, '2020-01-02'::date as event_time union all
	select 1 as user_id, 'Drew' as name, '2020-01-03'::date as event_time
),

-- calculate the _next_ event time, to determine the "valid_to" date for a record
events_windowed as (
	select *,
		lead(event_time) over (partition by user_id order by event_time) as next_event_time
	from events
)

-- use an inequality join to 
select
	user_id,
	name,
	event_time as valid_from,
	coalesce(next_event_time, '2099-12-31'::date) as valid_to

from events_windowed
join days on days.date_day >= events_windowed.event_time
 and (days.date_day < events_windowed.next_event_time or events_windowed.next_event_time is null)
;

/*
1	drew	2020-01-01	2020-01-02
1	draw	2020-01-02	2020-01-03
1	Drew	2020-01-03	2099-12-31
1	Drew	2020-01-03	2099-12-31
1	Drew	2020-01-03	2099-12-31
*/