jackguyETH Derivatives
Updated 2022-09-11
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
›
⌄
with tab1 as (
SELECT
date_trunc('day', block_timestamp) as day1,
avg(amount_out_usd/ amount_out) as price
from ethereum.core.ez_dex_swaps
WHERE token_out in (lower('0xFe2e637202056d30016725477c5da089Ab0A043A'), lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb'), lower('0xae78736Cd615f374D3085123A210448E74Fc6393'), lower('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84'))
GROUP BY 1--,2
)
SELECT
date_trunc('week', block_timestamp) as week,
CASE
WHEN token_out LIKE lower('0xFe2e637202056d30016725477c5da089Ab0A043A') THEN 'Stakewise ETH'
WHEN token_out LIKE lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb') THEN 'stkr ETH'
WHEN token_out LIKE lower('0xae78736Cd615f374D3085123A210448E74Fc6393') THEN 'Rocket pool ETH'
WHEN token_out LIKE lower('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84') THEN 'Lido ETH' END as token,
avg(amount_out_usd/ amount_out) as price,
count(DISTINCT tx_hash) as swaps,
sum(CASE WHEN token_out LIKE lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb') THEN (amount_out * price)/power(10,18) ELSE amount_out * price END) as volume_usd,
avg(CASE WHEN token_out LIKE lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb') THEN (amount_out * price)/power(10,18) ELSE amount_out * price END) as avg_swap_size
from ethereum.core.ez_dex_swaps
LEFT outer JOIN tab1
on date_trunc('week', block_timestamp) = day1
WHERE token_out in (lower('0xFe2e637202056d30016725477c5da089Ab0A043A'), lower('0xE95A203B1a91a908F9B9CE46459d101078c2c3cb'), lower('0xae78736Cd615f374D3085123A210448E74Fc6393'), lower('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84'))
GROUP BY 1,2
Run a query to Download Data