TX_HASH,

BLOCK_TIMESTAMP,

TX_RECEIVER,

TX_SIGNER,

GAS_USED,

transaction_fee / POW(10, 24) as TRANSACTION_FEE

FROM near.core.fact_transactions

where TX_RECEIVER like 'aurora'

and TX_STATUS like 'Success'

),

Near_token_price as

TIMESTAMP::date as time,

SYMBOL,

avg(PRICE_USD) as USD_price

FROM near.core.fact_prices

where SYMBOL = 'wNEAR'

GROUP BY 1,2

SELECT

date_trunc('week', BLOCK_TIMESTAMP) as Daily,

COUNT(distinct TX_HASH) as Number_of_Transactions,

SUM(Number_of_Transactions) OVER (ORDER BY Daily ASC) as Cumulative_Transactions,

COUNT(distinct (TX_SIGNER)) as Number_of_Users,

SUM(Number_of_Users) OVER (ORDER BY Daily ASC) as Cumulative_Users,

AVG(TRANSACTION_FEE) as Average_TX_Fee_Token,

AVG(TRANSACTION_FEE * USD_price) Average_TX_Fee_USD,

(Number_of_Transactions / Number_of_Users) as Average_TX_Per_User

FROM Aurora_Transactions a