Date_trunc(hour, a.block_timestamp) AS Hour,

a.tx_hash,

CASE

when a.token_in_contract = 'blackdragon.tkn.near' then 'IN'

when a.token_out_contract = 'blackdragon.tkn.near' then 'OUT'

else 'error' END AS AssetFlow,

b.transaction_fee / pow(10,24) AS tx_fee_near,

SUM(a.amount_in * (b.transaction_fee / pow(10,24)))AS tx_fee_usd,

COUNT(DISTINCT trader) AS Users

FROM near.defi.ez_dex_swaps a

JOIN near.core.fact_transactions b

ON a.tx_hash = b.tx_hash

WHERE (token_in_contract = 'blackdragon.tkn.near'

OR token_out_contract = 'blackdragon.tkn.near' )

AND tx_succeeded ='true'

AND a.block_timestamp::DATE >= current_date - interval '30 days'

AND b.block_timestamp::DATE >= current_date - interval '30 days'

GROUP BY 1,2,3,4

ORDER BY tx_fee_usd DESC