price queries

Centralized exchange price data

1
Select
2
minute,
3
price,
4
symbol
5
from prices.usd
6
7
WHERE symbol = '{{1. Symbol}}'
8
9
and minute > now() - interval '1hour'
Copied!

Decentralized exchange price data

1
Select
2
hour,
3
median_price,
4
d."contract_address"
5
from dex."view_token_prices" d
6
left join erc20.tokens e on d.contract_address = e.contract_address
7
WHERE symbol = '{{1. Symbol}}'
8
9
and hour > now() - interval '1000hour'
Copied!
This Query depends on the erc20.tokens table and can have gaps when there are no trades that been committed in that hour.
For a better but more complicated to navigate version use this:
1
with dex_trades AS (
2
SELECT
3
token_a_address as contract_address,
4
usd_amount/token_a_amount as price,
5
block_time
6
FROM dex.trades
7
WHERE 1=1
8
AND usd_amount > 0
9
AND category = 'DEX'
10
AND token_a_amount > 0
11
AND token_a_address = CONCAT('\x', substring('{{1. token_address}}' from 3))::bytea
12
13
UNION ALL
14
15
SELECT
16
token_b_address as contract_address,
17
usd_amount/token_b_amount as price,
18
block_time
19
FROM dex.trades
20
WHERE 1=1
21
AND usd_amount > 0
22
AND category = 'DEX'
23
AND token_b_amount > 0
24
AND token_b_address = CONCAT('\x', substring('{{1. token_address}}' from 3))::bytea
25
26
),
27
28
29
rawdata as (
30
31
SELECT
32
date_trunc('hour', block_time) as hour,
33
d.contract_address,
34
e.symbol as asset,
35
(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)) AS price,
36
count(1) AS sample_size
37
FROM dex_trades d
38
left join erc20.tokens e on e.contract_address = d.contract_address
39
GROUP BY 1, 2, 3
40
41
)
42
43
,leaddata as
44
(
45
SELECT
46
hour,
47
contract_address,
48
asset,
49
price,
50
sample_size,
51
lead(hour, 1, now() ) OVER (PARTITION BY contract_address ORDER BY hour asc) AS next_hour
52
from rawdata
53
where sample_size > 3
54
)
55
56
,hours AS
57
(
58
SELECT
59
generate_series('2020-01-01'::TIMESTAMP, date_trunc('hour', NOW()), '1 hour') AS hour
60
)
61
62
63
64
SELECT
65
d.hour as hour,
66
contract_address,
67
asset,
68
price,
69
sample_size
70
from leaddata b
71
INNER JOIN hours d ON b.hour <= d.hour
72
AND d.hour < b.next_hour -- Yields an observation for every hour after the first transfer until the next hour with transfer
Copied!
This will return the price by hour according to dex trading data and fill in gaps where there have been no trades commited. You can adjust the sample size of trades required to carry the price data forward in line 53. This can help ammend edge cases where arbritrage bots get weird prices on dexes. If you have issues with this, please reach out to us on Discord.
Last modified 3mo ago