date_trunc('day',BLOCK_HOUR) as dt0,

avg(PRICE_USD) as usd

from algorand.prices_swap

where ASSET_ID='0'

group by 1),

t1 as(select

tx_group_id

from algorand.payment_transaction

where RECEIVER='RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE'

select

date_trunc('day',block_timestamp) as dt,

count(*) as number_sale,

sum(AMOUNT) as total_algo,

p.usd as ALGO_usd,

total_algo*ALGO_usd as total_usd

from algorand.payment_transaction inner join price p on date_trunc('day',block_timestamp) = p.dt0

where dt>='2022-01-01'

and tx_group_id in (select tx_group_id from t1)

and RECEIVER!='RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE'

group by 1,4