WITH t1 AS (

SELECT

block_timestamp,

tx_hash,

tx_receiver AS contract_address,

tx_signer AS sender,

transaction_fee / POW(10,24) AS tx_fee,

tx:receipt[0].outcome.gas_burnt::NUMBER / POW(10,16) AS gas_burned,

(tx:receipt[0].outcome.gas_burnt::NUMBER / POW(10,16)) * 0.3 AS dev_revenue,

(transaction_fee / POW(10,24)) * 0.3 AS revenue_fees

FROM near.core.fact_transactions

WHERE block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

),

nearprice AS (

SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price

FROM near.price.ez_prices_hourly

WHERE symbol = 'NEAR'

GROUP BY day

SELECT

t2.label_type,

COUNT(t1.tx_hash) AS n_tx,

SUM(t1.tx_fee) AS "Total Fee (NEAR)",

SUM(t1.gas_burned) AS "Burnt Fee (NEAR)",

SUM(t1.dev_revenue) AS "Revenue (NEAR)",

-- Calculating USD values by multiplying with NEAR price

SUM(t1.tx_fee * np.price) AS "Total Fee (USD)",

SUM(t1.gas_burned * np.price) AS "Burnt Fee (USD)",

SUM(t1.dev_revenue * np.price) AS "Revenue (USD)"