select from_address, substr(date_trunc('year',block_timestamp),0,4) as year, count(distinct from_address) as "yes" from ethereum.core.fact_transactions

group by 1,2

),

pivoted_table as

SELECT *

FROM (

SELECT from_address, year, COUNT("yes") as yes_count

FROM distinct_interaction_years

GROUP BY from_address, year

PIVOT (

MAX(yes_count) FOR year IN (2020,2021,2022, 2023)

select 'ethereum' as chain, '2020' as year, count(from_address) as addresses from pivoted_table

where 1=1

and "2020"='1'

union

select 'ethereum' as chain, '2021' as year, count(from_address) as addresses from pivoted_table

where 1=1

and "2021"='1'

union

select 'ethereum' as chain, '2022' as year, count(from_address) as addresses from pivoted_table

where 1=1

and "2022"='1'

union

select 'ethereum' as chain, '2023' as year, count(from_address) as addresses from pivoted_table

where 1=1

and "2023"='1'

union

select 'ethereum' as chain, '2020 and 2021' as year, count(from_address) as addresses from pivoted_table

where 1=1

and "2020"='1' and "2021"='1'

union