theericstoneSwap Counts by Hour by Pool
Updated 2023-03-27
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 stats1 as (
select
pool_name,
date_trunc('hour',block_timestamp) as hour,
COUNT( tx_id) as total_swaps
--FLOOR( SUM (ABS (amount1_usd))) as total_amount_swapped_usd
from uniswapv3.swaps
WHERE block_timestamp > CURRENT_DATE - interval '7 days'
GROUP BY 2,1
)
select
CASE
WHEN total_swaps < 10
then 'other'
else pool_name
end as pool_filtered,
hour,
sum(total_swaps) as total_swaps
--total_amount_swapped_usd
from stats1
WHERE
--total_amount_swapped_usd > 0 AND
total_swaps > 0
GROUP BY 1,2
ORDER BY 3 DESC
Run a query to Download Data