WITH priceTb as (

HOUR::date as p_date,

avg(PRICE) as avg_PRICE,

FROM near.price.ez_prices_hourly

WHERE SYMBOL = 'WNEAR'

GROUP by 1

)

, main as (

SELECT DISTINCT

block_timestamp,

tx_hash,

TX_SIGNER as FROM_ADDRESS,

(TRANSACTION_FEE/pow(10,24)) as TX_FEE

FROM near.core.fact_transactions

WHERE year(block_timestamp::date) = '2024'

AND block_timestamp::date < date_trunc('week', current_date)

SELECT

date_trunc('week',block_timestamp) as date,

sum(TX_FEE) as "Gas Fee [$NEAR]",

sum("Gas Fee [$NEAR]") over (ORDER by date) as cum_fee_near,

sum(TX_FEE*avg_PRICE) as "Gas Fee [$USD]",

sum("Gas Fee [$USD]") over (ORDER by date) as cum_fee_usd

FROM main

JOIN priceTb on block_timestamp::date = p_date

GROUP BY 1

ORDER BY 1 DESC