binhachonMind Your Ps and Ys - yLUNA ratio
Updated 2022-03-30
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
31
32
33
34
35
36
›
⌄
with prism_transaction as (
select
block_timestamp,
msg_value:coins[0]:amount::float as raw_LUNA_amount,
msg_value:execute_msg:execute_swap_operations:minimum_receive::float as raw_yLUNA_amount,
raw_LUNA_amount/raw_yLUNA_amount as ratio
from terra.msgs
where 1 = 1 --tx_id = '6F05209C823E68C7031F2567A96C6BAC59F5A36B943D1E78675EE5A53CF15BC3'
and msg_value:coins[0]:denom::string = 'uluna'
and msg_value:execute_msg:execute_swap_operations:operations[1]:prism_swap:ask_asset_info:cw20::string = 'terra17wkadg0tah554r35x6wvff0y5s7ve8npcjfuhz'
and tx_status = 'SUCCEEDED'
),
ratio as (
select
block_timestamp::date as time,
avg(ratio) as ratio
from prism_transaction
group by time
),
LUNA_price as (
select
block_timestamp::date as time,
avg(price_usd) as avg_price
from terra.oracle_prices
where symbol = 'LUNA'
group by time
)
select
LUNA_price.*,
ratio,
1/ratio
from LUNA_price
inner join ratio on (ratio.time = LUNA_price.time)
Run a query to Download Data