date(RECORDED_AT) AS days ,

avg(PRICE) ax_price

from

osmosis.price.dim_prices

where SYMBOL = 'AXL' and PROVIDER = 'coin market cap'

and days >= current_date - interval ' 1 year '

group by 1)

select

-- monthname(BLOCK_TIMESTAMP) AS Month,

date_trunc('week', BLOCK_TIMESTAMP)::date AS date,

count(*) AS TX,

sum(case when TX_SUCCEEDED!='TRUE' then 0 else 1 end) AS "Success TX",

("Success TX"/TX)*100 AS "Success %",

100-"Success %" as failure_rate ,

round ( 100*("Success %"-lag("Success %",1)over(order by date))/lag("Success %",1)over(order by date),2) as "Success rate change %",

sum(fee*ax_price/1e6) as total_fee_usd,

round ( 100*(total_fee_usd-lag(total_fee_usd,1)over(order by date))/lag(total_fee_usd,1)over(order by date),2) as "total_fee_usd change %",

avg(fee*ax_price/1e6) as avg_fee_usd,

round ( 100*(avg_fee_usd-lag(avg_fee_usd,1)over(order by date))/lag(avg_fee_usd,1)over(order by date),2) as "avg_fee_usd change %",

avg(avg_fee_usd)over(order by date rows between 100 preceding and current row) as "Mov avg 100",

avg(avg_fee_usd)over(order by date rows between 7 preceding and current row) as "Mov avg 7"

from axelar.core.fact_transactions , axl_price

where BLOCK_TIMESTAMP::date between current_date - interval ' 1 year ' and current_date -1

and BLOCK_TIMESTAMP::date = days

group by 1

order by 1 desc