WITH AVAX_Tx_Tab AS
(
SELECT
a.BLOCK_TIMESTAMP,
a.GAS_PRICE,
a.GAS_USED,
a.TX_FEE,
a.FROM_ADDRESS,
a.TX_HASH,
b.CONTRACT_NAME,
b.CONTRACT_ADDRESS,
b.EVENT_NAME,
c.NAME,
c.SYMBOL
FROM
avalanche.core.fact_transactions a JOIN avalanche.core.fact_event_logs b ON a.TX_HASH = b.TX_HASH
JOIN avalanche.core.dim_contracts c ON b.contract_address = c.address
),
avax_Price AS
(
SELECT
HOUR,
PRICE
FROM
ethereum.core.fact_hourly_token_prices
WHERE
TOKEN_ADDRESS = lower('0x85f138bfEE4ef8e540890CFb48F620571d67Eda3')
)
SELECT
NAME,
SUM(GAS_USED) as overall_gas_used
FROM
AVAX_Tx_Tab