prices as (

trunc(RECORDED_HOUR,'d') as date,

median(PRICE) as avg_price

FROM osmosis.price.ez_prices

WHERE SYMBOL = 'AXL'

GROUP by 1

)

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_ID) as "Transaction Count",

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

count(distinct TX_FROM) 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",

(sum(((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(GAS_WANTED)) as "Gas Limit",

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

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

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