SELECT

tx_hash,

tx_fee

FROM kaia.core.fact_transactions

),

klay_price AS (

SELECT

price AS klay_to_usd

FROM kaia.price.ez_prices_hourly

WHERE symbol = 'KLAY'

ORDER BY hour DESC

LIMIT 1

),

platform_tx_fees AS (

SELECT

s.platform,

SUM(f.tx_fee * p.klay_to_usd) AS total_tx_fee_usd,

AVG(f.tx_fee * p.klay_to_usd) AS avg_tx_fee_usd

FROM kaia.defi.ez_dex_swaps s

JOIN transaction_fees f ON s.tx_hash = f.tx_hash

JOIN klay_price p ON TRUE

GROUP BY s.platform

platform,

total_tx_fee_usd,

avg_tx_fee_usd,

RANK() OVER (ORDER BY avg_tx_fee_usd ASC) AS fee_rank

FROM platform_tx_fees

ORDER BY fee_rank;