select date(BLOCK_HOUR) as day, avg(PRICE_USD) as price

from flipside_prod_db.algorand.prices_swap

where ASSET_NAME='ALGO'

group by day

),

algo_transaction as (

select t.BLOCK_TIMESTAMP::DATE as day,TX_ID,FEE,price

from flipside_prod_db.algorand.transactions as t JOIN algo_price

on t.BLOCK_TIMESTAMP::DATE=algo_price.day

where t.BLOCK_TIMESTAMP::DATE>='2022-01-01'

)

,

day_total_cost as

(

select day ,sum(FEE * price) as "Daily Cost of Transaction Fees (USD)", count(distinct TX_ID) as "Daily Transaction Count"

from algo_transaction

group by day

)

select day ,"Daily Cost of Transaction Fees (USD)", "Daily Transaction Count" ,

sum("Daily Cost of Transaction Fees (USD)") over (order by day asc rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Total Accumulated Cost of Transaction Fees (USD)"

from day_total_cost

order by day