binhachon80. [Elite] Historic bAssets Price Movements - BETH-WETH - Sort by BETH price
Updated 2021-10-18
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 UST_AMOUNT,
EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS BETH_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"0_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"0_contract_address"::string = 'terra1c0afrdc5253tkp5wt7rxhuj42xwyf2lcre0s7c' -- 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 UST_AMOUNT,
EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS BETH_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"3_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"3_contract_address"::string = 'terra1c0afrdc5253tkp5wt7rxhuj42xwyf2lcre0s7c' -- INPUT LP CONTRACT HERE
),
ETH_UNISWAP_PRICE AS(
SELECT BLOCK_TIMESTAMP,
ABS(AMOUNT0_ADJUSTED) AS WETH_AMOUNT,
ABS(AMOUNT1_ADJUSTED)AS USDT_AMOUNT
FROM uniswapv3.swaps
WHERE token0_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
AND token1_address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
UNION ALL
SELECT BLOCK_TIMESTAMP,
ABS(AMOUNT1_ADJUSTED) AS WETH_AMOUNT,
ABS(AMOUNT0_ADJUSTED) AS USDT_AMOUNT
FROM uniswapv3.swaps
WHERE token1_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
AND token0_address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
),
------------------------------------- 30 seconds time window --------------------------------------------------------------------------
Run a query to Download Data