select mindate::date as day,

count (distinct tx_signer) as New_Users

from (select tx_signer, min (block_timestamp) as mindate from near.core.fact_transactions group by 1)

group by 1),

flow1 as (

select mindate::date as day,

count (distinct proposer) as New_Users

from (select proposer, min(block_timestamp) as mindate from flow.core.fact_transactions group by 1)

group by 1),

solana1 as (

select mindate::date as day,

count (distinct user1) as New_Users

from (select signers[0] as user1, min(block_timestamp) as mindate from solana.core.fact_transactions group by 1)

group by 1)

select 'NEAR' as chain,

date_trunc (day,block_timestamp) as date,

New_Users,

count (distinct tx_hash) as TX_Count,

count (distinct tx_signer) as Users_Count

from near.core.fact_transactions t1 join near1 t2 on t1.block_timestamp::date = t2.day

where tx_status ilike 'success'

and block_timestamp >= '2022-11-07' and block_timestamp < '2022-11-15'

group by 1,2,3

union ALL

select 'FLOW' as chain,

date_trunc (day,block_timestamp) as date,

New_Users,

count (distinct tx_id) as TX_Count,

count (distinct proposer) as Users_Count

from flow.core.fact_transactions t1 join flow1 t2 on t1.block_timestamp::date = t2.day