SELECT DISTINCT address

FROM kaia.core.dim_contracts

WHERE address IS NOT NULL

),

latest_kaia_price AS (

SELECT PRICE

FROM kaia.price.ez_prices_hourly

WHERE symbol = 'KAIA'

AND blockchain = 'kaia'

AND hour = (SELECT MAX(hour) FROM kaia.price.ez_prices_hourly WHERE symbol = 'KAIA' AND blockchain = 'kaia')

ROW_NUMBER() OVER(ORDER BY SUM(tx.tx_fee ) DESC) AS gas_rank,

CAST(tx.to_address AS VARCHAR) AS contract,

'https://kaiascan.io/address/' || CAST(tx.to_address AS VARCHAR) AS contract_linked,

SUM(tx.tx_fee ) AS gas_spend,

SUM(tx.tx_fee * lp.PRICE) AS gas_spend_usd,

COUNT(*) AS tx_cnt,

COUNT(DISTINCT tx.from_address) AS users

FROM kaia.core.fact_transactions tx

INNER JOIN smart_contracts_list b ON tx.to_address = b.address

CROSS JOIN latest_kaia_price lp

WHERE tx.block_timestamp > CURRENT_DATE - INTERVAL '30 day'

AND tx.tx_fee > 0

GROUP BY 2, 3, lp.PRICE

ORDER BY 1

LIMIT 1000;