sol_flipsiders as (

select

distinct

--trunc(block_timestamp,'day') as date,

tx_to as flipsiders,

sum(amount) as sol_amount

from solana.core.fact_transfers

where tx_from='4GsmCJ1c69BD7iX2BqPYB4Jy4a35gG8mjpuM7dPEqijG' and mint ='So11111111111111111111111111111111111111112'

and block_timestamp>='2022-01-01'

group by 1 order by 2 desc

),

post_transfers as (

distinct tx_from as flipsiders,

tx_to as receiver_wallet,

count(distinct tx_id) as n_transfers,

sum(amount) as sol_amount,

max(block_timestamp) as last_transfer_time

from solana.core.fact_transfers

where tx_from in (select flipsiders from sol_flipsiders)

group by 1,2 order by 2,1,3 desc

),

receivers as (

select

distinct receiver_wallet,

count(distinct flipsiders) as n_flipsiders,

avg(n_transfers) as avg_transfer_per_flipsider,

median(sol_amount) as median_sol_amount,

sum(sol_amount) as total_sol_amount_received,

max(last_transfer_time) as last_transfer_time

from post_transfers

group by 1

),