SELECT

-- On borne sur les dernières 24h

block_timestamp,

tx_hash,

gas_used,

gas_price

FROM mantle.core.fact_transactions

WHERE block_timestamp >= DATEADD(day, -1, CURRENT_TIMESTAMP)

AND block_timestamp < CURRENT_TIMESTAMP

COUNT(DISTINCT tx_hash) AS tx_count_24h,

/* Conversion Gwei MNT -> MNT : on divise par 1e9 */

SUM(gas_used * gas_price) / 1e9 AS total_mnt_fees_24h,

CASE WHEN COUNT(DISTINCT tx_hash) = 0 THEN 0

ELSE (SUM(gas_used * gas_price) / 1e9)

/ COUNT(DISTINCT tx_hash)

END AS avg_mnt_fee_per_tx_24h

FROM last_24h;