dates as (

select

date::date as halving_date,

year as halving_year,

number as halving_number,

label as halving_label

from (

values

('2012-11-28', '2012', '1st Halving', '2012 Halving'),

('2016-07-09', '2016', '2nd Halving', '2016 Halving'),

('2020-05-11', '2020', '3rd Halving', '2020 Halving'),

('2024-04-20', '2024', '4th Halving', '2024 Halving')

) t(date, year, number, label)

),

prices as (

select

hour::date as date,

avg(price) as price

from

bitcoin.price.ez_prices_hourly

group by

date

),

miners as (

select

transactions.block_timestamp,

transactions.block_number,

transactions.outputs[0]:scriptPubKey:address as miner,

rewards.total_reward,

rewards.total_reward * prices.price as total_reward_usd,

rewards.block_reward,

rewards.fees,