binhachonHistoric bLUNA - LUNA ratio - less than 1.001
Updated 2021-11-17
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
›
⌄
------------------------------------- GET SWAP PRICE --------------------------------------------------------------------------
WITH LP_SWAP_TABLE AS(
SELECT BLOCK_TIMESTAMP, TX_ID, --SWAP LUNA FOR bLUNA
EVENT_ATTRIBUTES:"to"::string AS ADDRESS,
EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS LUNA_AMOUNT,
EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS BLUNA_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"0_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"0_contract_address"::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' -- INPUT LP CONTRACT HERE
UNION ALL
SELECT BLOCK_TIMESTAMP, TX_ID, -- SWAP bLUNA for LUNA
EVENT_ATTRIBUTES:"from"::string AS ADDRESS,
EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS LUNA_AMOUNT,
EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS BLUNA_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"3_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"3_contract_address"::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' -- INPUT LP CONTRACT HERE
)
------------------------------------- 30 seconds time window --------------------------------------------------------------------------
select * from (
SELECT time_slice(BLOCK_TIMESTAMP, 30, 'SECOND', 'START') as "START OF SLICE",
time_slice(BLOCK_TIMESTAMP, 30, 'SECOND', 'END') as "END OF SLICE",
SUM(BLUNA_AMOUNT) AS BLUNA_AMOUNT,
SUM(LUNA_AMOUNT) AS LUNA_AMOUNT,
SUM(BLUNA_AMOUNT)/SUM(LUNA_AMOUNT) AS RATIO
FROM LP_SWAP_TABLE
WHERE BLUNA_AMOUNT > 0.1
AND LUNA_AMOUNT > 0.1
GROUP BY "START OF SLICE", "END OF SLICE"
)
where RATIO < 1.001
ORDER BY "START OF SLICE"