purifKodiak pricing and vol
Updated 2024-09-14
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
›
⌄
with tokens(name, address, decimals) as (
SELECT name , address, decimals
FROM (
VALUES
('WBERA','0x7507c1dc16935b82698e4c63f2746a2fcf994df8',1e18),
('BERA','0x0000000000000000000000000000000000000000',1e18),
('USDC','0xd6d83af58a19cd14ef3cf6fe848c9a4d21e5727c',1e6),
('HONEY','0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03',1e18),
('WBTC','0x286F1C3f0323dB9c91D1E8f45c8DF2d065AB5fae',1e8)
) AS X(name ,address,decimals)
),
pools(pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals) as (
select pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals
from (
VALUES
('0x8a960A6e5f224D0a88BaD10463bDAD161b68C144', '0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03','HONEY',1e18,'0x7507c1dc16935b82698e4c63f2746a2fcf994df8','WBERA',1e18),
('0x246c12d7f176b93e32015015dab8329977de981b', '0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03','HONEY',1e18,'0xf5afcf50006944d17226978e594d4d25f4f92b40','NECT',1e18),
('0x64f18443596880df5237411591afe7ae69f9e9b9', '0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03','HONEY',1e18,'0x64f18443596880df5237411591afe7ae69f9e9b9','USDC',1e6),
('0x4302b57ba521cf2137e8af85990ab1ca2e241126', '0x4302b57ba521cf2137e8af85990ab1ca2e241126','IBGT',1e18,'0x7507c1dc16935b82698e4c63f2746a2fcf994df8','WBERA',1e18)
) as X(pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals)
)
,hourly_price as (
select hour, CASE
WHEN t0_name = 'HONEY' or t0_name='WBERA' THEN t1_name
ELSE t0_name
END as token,
CASE
WHEN t0_name = 'HONEY' or t0_name='WBERA' THEN t0_name
ELSE t1_name
END as quote_token,
avg(price) as price,
from (
select date_trunc('hour',block_timestamp) as hour, t0_name, t1_name,
CASE
WHEN t0_name = 'HONEY' or t0_name='WBERA' THEN amount0/amount1
QueryRunArchived: QueryRun has been archived