Kruys-CollinsArbitrum Vault Tokenn Prices copy
Updated 2024-11-09
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
›
⌄
-- forked from Arbitrum Vault Tokenn Prices @ https://flipsidecrypto.xyz/studio/queries/f630d773-2e16-4c07-bec4-cedfc423b284
WITH token_list AS (
SELECT column1 as t_address, LOWER(column2) as symbol
FROM VALUES
('0xd4d42f0b6def4ce0383636770ef773390d85c61a', 'sushi'),
('0x82af49447d8a07e3bd95bd0d56f35241523fbab1', 'weth'),
('0x3e6648c5a70a150a88bce65f4ad4d506fe15d2af', 'spell'),
('0xfea7a6a0b346362bf88a9e4a88416b77a57d6c2a', 'mim'),
('0x8d9ba570d6cb60c7e3e0f31343efe75ab8e65fb1', 'gohm'),
('0xeb4c2781e4eba804ce9a9803c67d0893436bb27d', 'renbtc'),
('0xaf88d065e77c8cc2239327c5edb3a432268e5831', 'usdc'),
('0xb1f1ee126e9c96231cc3d3fad7c08b4cf873b1f1', 'bifi'),
('0x7f90122bf0700f9e7e1f688fe926940e8839f353', '2crv'),
('0x539bde0d7dbd336b79148aa742883198bbf60342', 'magic'),
('0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f', 'wbtc'),
('0xf97f4df75117a78c1a5a0dbb814af92458539fb4', 'link'),
('0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9', 'usdt'),
('0x82af49447d8a07e3bd95bd0d56f35241523fbab1', 'eth'),
('0xB6a0ad0f714352830467725e619ea23E2C488f37', 'slp')
),
daily_prices AS (
-- Arbitrum swaps
SELECT
token_in as token_address,
symbol_in as symbol,
DATE(block_timestamp) as price_date,
AVG(CASE
WHEN amount_in > 0 AND amount_in_usd IS NOT NULL THEN amount_in_usd/amount_in
WHEN amount_in > 0 AND amount_out_usd IS NOT NULL THEN amount_out_usd/amount_out * (amount_out/amount_in)
ELSE NULL
END) as avg_price_from_in,
'Arbitrum' as chain
FROM arbitrum.defi.ez_dex_swaps
WHERE block_timestamp >= DATEADD(day, -1, CURRENT_TIMESTAMP())
AND (amount_in_usd IS NOT NULL OR amount_out_usd IS NOT NULL)
QueryRunArchived: QueryRun has been archived