binhachon80. [Elite] Historic bAssets Price Movements - BETH-WETH - Sort by BETH price
    Updated 2021-10-18
    ------------------------------------- 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