Metiocrealgo price vs Yieldly daily stakers
    Updated 2022-05-23
    with apps as (
    select * from flipside_prod_db.algorand.app
    where app_id in (233725850, 385089192, 393388133, 424101057, 447336112, 511597182, 583357499, 591414576, 593126242, 593270704, 593289960,
    596950925, 593324268, 604219363, 604373501, 604392265,604411076, 609492331, 604434381, 617707129, 618390867, 620458102, 624919018, 625053603,
    620625200, 717256390, 710518651)
    ),
    algo_price as (
    SELECT block_hour::date as date,
    avg(price_usd) as price
    FROM flipside_prod_db.algorand.prices_swap
    where asset_id=0 and date>='2022-01-01'
    group by 1
    )

    SELECT block_timestamp::date as date,
    count (distinct a.SENDER) as users_count,
    avg(asset_amount)/1e6 as average_yieldly_staked,
    avg(price) as price
    FROM
    flipside_prod_db.algorand.application_call_transaction a
    left join algorand.asset_transfer_transaction using(tx_group_id)
    inner join algo_price on a.block_timestamp::date =algo_price.date
    WHERE
    app_id in (select app_id from apps)
    AND
    block_timestamp::date >= '2022-01-01'
    and try_base64_decode_string(tx_message:txn:apaa[0]::string) in ('S','stake')
    and asset_amount >0
    group by 1
    Run a query to Download Data