-- Near New Users

select year(BLOCK_TIMESTAMP)||'-Q'||CEIL(month(BLOCK_TIMESTAMP)/3) as "Date", 'Near' as "Chain", count(*) as "New Wallets"

from (select user,

min(BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

from (

select ifnull(tx:actions[0]:Delegate:delegate_action:sender_id::string, TX_SIGNER) as user, BLOCK_TIMESTAMP

from near.core.fact_transactions where TX_SUCCEEDED=true

union all

select ifnull(tx:actions[0]:Delegate:delegate_action:receiver_id::string, TX_RECEIVER), BLOCK_TIMESTAMP

from near.core.fact_transactions where TX_SUCCEEDED=true)

group by 1)

group by 1

union all

--Polygon New Users

select year(BLOCK_TIMESTAMP)||'-Q'||CEIL(month(BLOCK_TIMESTAMP)/3) as "Date", 'Polygon' as "Chain", count(*) as "New Wallets"

from (select

FROM_ADDRESS,

min(BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

from (

select FROM_ADDRESS, BLOCK_TIMESTAMP from polygon.core.fact_transactions where STATUS='SUCCESS'

union all

select TO_ADDRESS, BLOCK_TIMESTAMP from polygon.core.fact_transactions where STATUS='SUCCESS')

group by 1)

group by 1

union all

-- Optimism New Users

select year(BLOCK_TIMESTAMP)||'-Q'||CEIL(month(BLOCK_TIMESTAMP)/3) as "Date", 'Optimism' as "Chain", count(*) as "New Wallets"

from (select

FROM_ADDRESS,

min(BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

from (

select FROM_ADDRESS, BLOCK_TIMESTAMP from optimism.core.fact_transactions where STATUS='SUCCESS'

union all

select TO_ADDRESS, BLOCK_TIMESTAMP from optimism.core.fact_transactions where STATUS='SUCCESS')

group by 1)