with
date_cte as (
select
distinct
BLOCK_TIMESTAMP::date date
, case
when date < '2022-09-15' then '1. before the merge'
else '2. after the merge'
end period
from ethereum.core.fact_blocks
where 1=1
and BLOCK_TIMESTAMP::date >= '2022-01-01'
and BLOCK_TIMESTAMP::date <= '2022-10-20'
)
, actual_cte as (
select
period
, avg(energy_consumption) energy_consumption
from (
select
date
, period
, case
when date < '2022-09-15' then 112/365
else 0.01/365
end energy_consumption
from date_cte
)
group by 1
)
, merge_cte as (
select
datediff(day, '2022-09-15', '2022-12-31') * energy_consumption energy_consumption -- after merge numbers
from actual_cte
where 1=1
and period = '2. after the merge'