select

block_hour::date as days,

avg(price_usd) as ALGO_price

from algorand.prices_swap

where asset_id = 0

and block_hour::date >= '2022-01-01'

group by days

order by days

),

yieldly_staking_pool as (

select

block_timestamp::date as days,

sum(asset_amount/1e6) as YLDY_staked_amount,

count (distinct sender) as number_of_stakers

from algorand.application_call_transaction

left join algorand.asset_transfer_transaction using(tx_group_id)

where block_timestamp::date >= '2022-01-01'

and block_timestamp::date < '2022-05-24'

and try_base64_decode_string(tx_message:txn:apaa[0]::string) in ('S','stake')

and app_id in (710518651, 717256390, 620625200, 625053603, 624919018,620458102,

618390867, 617707129, 604434381, 609492331, 604411076,604392265,

604373501, 604219363, 593324268, 596950925, 593289960, 593270704,

593126242, 591414576, 583357499, 511597182, 447336112, 424101057,

393388133, 385089192, 233725850)

group by days

order by days

ALGO_daily_price.days,

ALGO_daily_price.ALGO_price,

yieldly_staking_pool.YLDY_staked_amount,

yieldly_staking_pool.number_of_stakers

from ALGO_daily_price

left join yieldly_staking_pool