angealPSI Block Prices
Updated 2022-05-26
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 psi_ust_trades AS (select
event_type,
block_timestamp,
tx_id,
event_attributes:offer_asset::string as from_asset,
event_attributes:ask_asset::string as to_asset,
event_attributes:offer_amount - event_attributes:commission_amount as amount_sent,
event_attributes:return_amount as amount_received
from
terra.msg_events
where
tx_status = 'SUCCEEDED'
and event_attributes:offer_asset is not null
AND
((from_asset = 'terra12897djskt9rge8dtmm86w654g7kzckkd698608' AND to_asset = 'uusd')
OR
(to_asset = 'terra12897djskt9rge8dtmm86w654g7kzckkd698608' AND from_asset = 'uusd'))
and block_timestamp >= '2021-10-01'),
block_prices AS (
SELECT
block_timestamp,
CASE
WHEN from_asset != 'uusd'
THEN amount_received / amount_sent
ELSE amount_sent / amount_received
END AS psi_price
FROM psi_ust_trades
)
SELECT
date_trunc('hour', block_timestamp) AS trade_hour,
AVG(psi_price) AS avg_psi_price
FROM block_prices
GROUP BY trade_hour
Run a query to Download Data