SELECT

date_trunc('hour', block_timestamp) AS hour,

CASE

WHEN swap_from_mint = '6p6xgHyF7AeE6TZkSmFsko444wqoP15icUSqi2jfGiPN' THEN 'selling'

WHEN swap_to_mint = '6p6xgHyF7AeE6TZkSmFsko444wqoP15icUSqi2jfGiPN' THEN 'buying'

ELSE 'other'

END AS action_type,

COUNT(DISTINCT tx_id) AS hourly_swaps,

COUNT(DISTINCT swapper) AS hourly_swappers,

SUM(swap_from_amount_usd) AS hourly_volume

FROM

solana.defi.ez_dex_swaps

WHERE

swap_from_mint = '6p6xgHyF7AeE6TZkSmFsko444wqoP15icUSqi2jfGiPN'

OR swap_to_mint = '6p6xgHyF7AeE6TZkSmFsko444wqoP15icUSqi2jfGiPN'

GROUP BY

date_trunc('hour', block_timestamp),

action_type

SELECT

hour,

action_type,

SUM(hourly_swaps) AS swaps,

SUM(hourly_volume) AS volume,

SUM(hourly_swappers) AS swappers

FROM

hourly_data

WHERE

action_type IN ('buying', 'selling')

GROUP BY

hour, action_type

ORDER BY

hour, action_type;