SELECT

tx_to,

tx_from,

amount,

CASE WHEN tx_to = '6PpD39XuSbLNrVPqCVf7TkXsHqJBwZQpJAfSnKGgGurV' THEN amount ELSE 0 END as deposit,

CASE WHEN tx_from = '6PpD39XuSbLNrVPqCVf7TkXsHqJBwZQpJAfSnKGgGurV' THEN amount ELSE 0 END as withdrawal

FROM solana.core.fact_transfers

WHERE (tx_to = '6PpD39XuSbLNrVPqCVf7TkXsHqJBwZQpJAfSnKGgGurV' OR tx_from = '6PpD39XuSbLNrVPqCVf7TkXsHqJBwZQpJAfSnKGgGurV')

AND mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' -- USDC address

AND block_timestamp <= cast('2024-07-18 14:00' as timestamp)

SELECT

SUM(deposit) as total_usdc_deposited,

SUM(withdrawal) as total_usdc_withdrawn,

COUNT(CASE WHEN deposit > 0 THEN 1 END) as total_num_deposits,

COUNT(CASE WHEN withdrawal > 0 THEN 1 END) as total_num_withdrawals,

COUNT(DISTINCT CASE WHEN deposit > 0 THEN tx_from END) as total_depositors,

COUNT(DISTINCT CASE WHEN withdrawal > 0 THEN tx_to END) as total_withdrawers,

AVG(CASE WHEN deposit > 0 THEN deposit END) as overall_avg_deposit_size,

AVG(CASE WHEN withdrawal > 0 THEN withdrawal END) as overall_avg_withdrawal_size,

SUM(deposit) / NULLIF(COUNT(DISTINCT CASE WHEN deposit > 0 THEN tx_from END), 0) as avg_total_deposit_per_user,

SUM(withdrawal) / NULLIF(COUNT(DISTINCT CASE WHEN withdrawal > 0 THEN tx_to END), 0) as avg_total_withdrawal_per_user,

SUM(deposit) - SUM(withdrawal) as total_net_flow

FROM transfers;