with transactions as (
select
signers[0] as from_address,
min(block_timestamp) as first_tx
from solana.core.fact_transactions
where succeeded = 'TRUE'
group by from_address
),
new_users as (
select
from_address as address
from transactions
where first_tx >= '2022-04-01' -- from April
and first_tx < '2022-09-01' -- to August
),
swaps as (
select
swaps.block_timestamp::date as date,
count(swaps.tx_id) as tx_count
from solana.core.fact_swaps as swaps
inner join new_users on swaps.swapper = new_users.address
where swaps.block_timestamp >= '2022-04-01' -- from April
and swaps.block_timestamp < '2022-09-01' -- to August
and swaps.succeeded = 'TRUE'
group by date
),
transfers as (
select
trans.block_timestamp::date as date,
count(trans.tx_id) as tx_count
from solana.core.fact_transfers as trans
inner join new_users on trans.tx_from = new_users.address
where trans.block_timestamp >= '2022-04-01' -- from April