sql-challenge.md

Given the below PostgreSQL compatible SQL

with calendar
as (
	select t.day::date,
		trunc(random() * 3 + 1) max_version_daily,
		trunc(random() * 3 + 1) max_version_hourly
	from generate_series(timestamp '2010-01-01', timestamp '2022-01-31', interval '1 day') as t(day)
	),
rates
as (
	select calendar.day,
		rates.rate_type,
		versions.num version_num,
		round((random() * 10)::numeric, 2) value
	from calendar,
		(
			values ('daily'),
				('hourly')
			) rates(rate_type),
		(
			values (1),
				(2),
				(3)
			) versions(num)
	)
select *
from calendar
join rates on calendar.day = rates.day
order by calendar.day

You need to update the query to return a simliar result set:

"day"	 "daily_val"	"hourly_val"
"2010-01-01"	8.98	4.98
"2010-01-02"	1.32	3.00
"2010-01-03"	5.69	6.40
"2010-01-04"	7.67	5.46
"2010-01-05"	2.16	1.73
"2010-01-06"	2.20	3.50
"2010-01-07"	3.72	4.66
"2010-01-08"	3.45	4.52
"2010-01-09"	1.57	5.96
"2010-01-10"	6.47	4.15

NB# There are some rules...

• The calendar max_version_daily must be >= to the rates version_num where the rate_type is daily
• The calendar max_version_hourly must be >= the the rates version_num where the rate_type is hourly
• For each calendar day we want the latest rate value for hourly and daily using highest version_num per day, rate_type

Working setup here to play with https://www.db-fiddle.com/f/ryptnGQjTYGisEgPp9TJmB/2