from solana.transactions

where block_timestamp >= '2022-01-01'

union

select date_trunc('day', block_timestamp) as dt, tx_to_address as active_address

from solana.transactions

where block_timestamp >= '2022-01-01'),

all_tx as (select date_trunc('day', block_timestamp) as dt, count(distinct tx_id) as total_tx

from solana.transactions

where block_timestamp >= '2022-01-01'

group by 1),

successful as ((select date_trunc('day', block_timestamp) as dt, count(distinct tx_id) as successful_tx

from solana.transactions

where succeeded = 'TRUE' and block_timestamp >= '2022-01-01'

group by 1))

select a.dt, count(distinct a.active_address) as active_wallets, b.total_tx, c.successful_tx, (c.successful_tx/b.total_tx) as tx_success_rate

from from_wallet a

left join all_tx b on a.dt = b.dt

left join successful c on a.dt = c.dt

group by 1,3,4