Metiocrealgo price vs Yieldly daily stakers
Updated 2022-05-23
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
›
⌄
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