SELECT
Date_trunc(day, block_timestamp) AS Day
,COUNT(TX_HASH) "Number Of Transactions"
,SUM(gas_used * gas_price)/1e9 as "Daily Gas Fees"
, SUM(SUM(tx_fee)) over (order by Date_trunc(day, block_timestamp) ) as "Total Gas Fees"
,AVG(gas_used * gas_price)/1e9 as "Average Gas Fee $BNB"
FROM
bsc.core.fact_transactions
WHERE
TX_HASH IN ( SELECT
DISTINCT TX_HASH
FROM
bsc.defi.ez_dex_swaps
WHERE
BLOCK_TIMESTAMP >= '2024-01-01'
AND
PLATFORM LIKE '%pancake%'
)
GROUP BY
1