theericstoneTotal swaps by pool in Uniswap v3
Updated 2023-03-10
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
›
⌄
-- This query is for the Daily swap based on the group of pool, including the
WITH prices AS (
SELECT * FROM ethereum.token_prices_hourly
),
swap_raw AS (
SELECT
date_trunc('hour', swaps.block_timestamp) AS block_hour,
pool_name,
token0_symbol,
token1_symbol,
amount0_adjusted,
amount1_adjusted,
-- CASE WHEN amount0_adjusted < 0 THEN 'out' ELSE 'in' END AS token0_direction,
-- CASE WHEN amount1_adjusted < 0 THEN 'out' ELSE 'in' END AS token1_direction,
CASE WHEN amount0_adjusted < 0 THEN amount0_adjusted ELSE amount1_adjusted END AS swap_amount_out,
CASE WHEN amount0_adjusted >= 0 THEN amount0_adjusted ELSE amount1_adjusted END AS swap_amount_in,
CASE WHEN amount0_adjusted < 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_out,
CASE WHEN amount0_adjusted >= 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_in
FROM uniswapv3.swaps swaps
LEFT JOIN prices p0
ON p0.hour = date_trunc('hour', swaps.block_timestamp) AND p0.token_address = swaps.token0_address
LEFT JOIN prices p1
ON p1.hour = date_trunc('hour', swaps.block_timestamp) AND p1.token_address = swaps.token1_address
where swaps.block_timestamp > getdate() - interval '7 days'
)
SELECT * from (
SELECT
pool_name,
token0_symbol,
token1_symbol,
SUM(swap_amount_in) AS swap_amount_in,
SUM(swap_amount_out) AS swap_amount_out,
SUM(swap_usd_amount_in) AS swap_usd_amount_in,
SUM(swap_usd_amount_out) AS swap_usd_amount_out
FROM swap_raw
Run a query to Download Data