SELECT

date_trunc('hour', block_timestamp) as hour,

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

hour,

SUM(deposit) as usdc_deposited,

SUM(withdrawal)*(-1) as usdc_withdrawn,

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

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

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

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

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

AVG(CASE WHEN withdrawal > 0 THEN withdrawal END)*(-1) as avg_withdrawal_size,

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

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

SUM(deposit) - SUM(withdrawal) as net_flow

FROM transfers

GROUP BY 1

ORDER BY 1;