SELECT date_trunc('week', block_timestamp) as "Date",

COUNT(tx_hash)/(7 * 24 * 60 * 60) as "Avg Blocktime Txn",

SUM(transaction_fee)/pow(10, 24) as "Total Fees",

AVG(transaction_fee)/pow(10, 24) as "Avg Fee",

COUNT(tx_hash) as "Total Txn",

COUNT(DISTINCT tx_signer) as "Unique Users"

FROM near.core.fact_transactions

GROUP BY 1 ORDER BY 6 DESC

),

users_transaction as (

SELECT tx_signer as account, block_timestamp

FROM near.core.fact_transactions

),

new_users_date as (

SELECT account,

MIN(block_timestamp) as min_block_timestamp

FROM users_transaction

GROUP BY account

ORDER BY 2

),

new_users_weekly as (

SELECT date_trunc('week', min_block_timestamp) as block_date,

COUNT(DISTINCT account) as new_users_count

FROM new_users_date

GROUP BY 1 ORDER BY 1

SELECT

s.*,

nu.new_users_count as "New Users"

FROM summary s LEFT JOIN new_users_weekly nu ON s."Date" = nu.block_date

ORDER BY s."Date" DESC