wanlincextodex copy
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 yasmin-n-d-r-h / cextodex @ https://flipsidecrypto.xyz/yasmin-n-d-r-h/q/RY1zr1CGNcon/cextodex
WITH uni_prices AS (
SELECT
date_trunc('day', hour) AS day,
avg(price) AS uni_price
FROM
ethereum.core.ethereum.price.ez_asset_metadata
WHERE
symbol = 'UNI'
GROUP BY
day
),
cex_to_dex AS (
SELECT
trunc(block_timestamp::date, 'week') AS date,
count(DISTINCT tx_hash) AS total_transfers,
sum(amount) AS uni_volume,
SUM(amount_usd) AS amount_usd
FROM
ethereum.core.ez_token_transfers AS t
JOIN ethereum.core.dim_labels AS l1 ON l1.address = t.from_address
JOIN ethereum.core.dim_labels AS l2 ON l2.address = t.to_address
WHERE
symbol ILIKE 'UNI'
AND l1.label_type ILIKE '%cex%'
AND l2.label_type ILIKE '%dex%'
GROUP BY
1
)
SELECT
cex_to_dex.date,
uni_prices.uni_price,
cex_to_dex.total_transfers,
cex_to_dex.uni_volume,
cex_to_dex.amount_usd,
Run a query to Download Data