theericstonelivequery test
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
›
⌄
-- use this query to find 15 min interval historical
-- dex-based pricing for any given token, provided its
-- contract address and its most popular trading pool's address
-- and then use LiveQuery to tack on the live implied price based on
-- the pool's contract state
-- start by calculating the number of swaps and the average price
-- per 15 min increment using flipside's pre-formatted dex swaps table
WITH hist_twap as (
SELECT
time_slice(block_timestamp, 15, 'MINUTE') as time,
count(distinct(tx_hash)) as n_swaps,
avg(price_per_weth) as twap_eth from (
select
block_timestamp, tx_hash,
case when token_out = lower('0xb2114e5420927932666a5c5bd1ac4e14d9ede32b') -- whatever trending coin
then amount_in / amount_out
else amount_out / amount_in end as price_per_weth
from
ethereum.core.ez_dex_swaps swaps
where (
swaps.token_in = lower('0xb2114e5420927932666a5c5bd1ac4e14d9ede32b') -- whatever trending coin
or swaps.token_out = lower('0xb2114e5420927932666a5c5bd1ac4e14d9ede32b') -- whatever trending coin
)
and block_timestamp > current_date - 2)
group by 1
),
-- next we use livequery to pull the current balances of WETH and the
-- token of interest in order to calculate the current implied price
with input_tokens AS (
SELECT '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2' AS token_address --WETH
UNION ALL
SELECT '0xb2114E5420927932666A5C5Bd1ac4e14d9EDe32B' -- whatever trending coin
Run a query to Download Data