prices as (

date(HOUR) as date,

symbol,

avg(price) as avg_price

from near.price.ez_prices_hourly

where symbol = 'NEAR'

AND BLOCKCHAIN = 'near protocol'

AND date > '2023-01-01'

group by 1,2

)

select

date_trunc('quarter', block_timestamp) as "Date",

case

when year(block_timestamp) = '2023' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 23 )

when year(block_timestamp) = '2024' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 24 )

end as "Quarter",

CASE when year(block_timestamp) = '2023' then 'y.2023' else 'y.2024' end as year,

-- year(block_timestamp)::string as year,

count(distinct tx_hash) as "Transaction Count",

round("Transaction Count" / count(distinct block_timestamp::date)) as "Average Daily TXs",

count(distinct tx_signer) as "User Count",

round("User Count" / count(distinct block_timestamp::date)) as "Average Daily Users",

round((("Transaction Count" / lag("Transaction Count") over (order by "Date")) - 1) * 100) AS "Change in TXs in Percent",

round((("User Count" / lag("User Count") over (order by "Date")) - 1) * 100) AS "Change in Users in Percent",

sum("Transaction Count") over (order by "Date") AS "Total Transaction",

round(sum(((TRANSACTION_FEE) / pow(10, 24)) * avg_price)) as "Fees",

round((("Fees" / lag("Fees") over (order by "Date")) - 1) * 100) AS "Change in Fees in Percent",

(sum(ATTACHED_GAS)) as "Gas Limit",

(sum(gas_used)) as "Gas Used",

"Gas Used" / "Gas Limit" * 100 as "Gas Filled",