with price_tbl as (

SELECT date(block_hour) as price_hour,

avg(price_usd) as price

from algorand.prices_swap

where asset_id = 0 -- ALGO

GROUP by price_hour

SELECT

date(ca.block_timestamp) as date,

count(DISTINCT ca.tx_group_id) as sales,

sum(zeroifnull(p.tx_message:ca/1e6)) as fee_earned,

sum(amount) as tota_sale,

(tota_sale + fee_earned) as total_sale_with_fee,

total_sale_with_fee * pr.price as total_usd_sale,

sum(fee_earned) over (order by date asc) as fee_accumulated

from algorand.application_call_transaction ca

join algorand.payment_transaction p on p.tx_group_id = ca.tx_group_id

join price_tbl pr on pr.price_hour = date(ca.block_timestamp)

where try_base64_decode_string(ca.tx_message:txn:apaa[0]::string) = 'close_auction'

and date(ca.block_timestamp) >= '2022-01-01'

group by date(ca.block_timestamp), pr.price