yasmin-n-d-r-hswap and swaer
Updated 2023-05-15
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
›
⌄
WITH price AS (
SELECT
date_trunc('day', RECORDED_HOUR) AS hour,
avg(close) AS usd_price
FROM
flow.core.fact_hourly_prices
WHERE
TOKEN = 'Flow'
GROUP BY
1
)
SELECT
date_trunc('week', ez.block_timestamp) AS date,
count(DISTINCT ez.tx_id) AS swaps,
count(DISTINCT ez.trader) AS swappers,
sum(ez.TOKEN_IN_AMOUNT * p.usd_price) AS volume_IN,
AVG(ez.TOKEN_IN_AMOUNT * p.usd_price) AS Avg_volume,
SUM(count(DISTINCT ez.tx_id)) OVER (
ORDER BY date_trunc('week', ez.block_timestamp)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cum_swaps,
SUM(count(DISTINCT ez.trader)) OVER (
ORDER BY date_trunc('week', ez.block_timestamp)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cum_swappers,
SUM(sum(ez.TOKEN_IN_AMOUNT * p.usd_price)) OVER (
ORDER BY date_trunc('week', ez.block_timestamp)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cum_volume_IN
FROM
flow.core.ez_swaps AS ez
JOIN flow.core.fact_transactions AS ft ON ez.tx_id = ft.tx_id
JOIN price AS p ON date_trunc('day', ez.block_timestamp) = p.hour
WHERE
ft.PROPOSER = '0x55ad22f01ef568a1'
AND ft.PAYER = '0x55ad22f01ef568a1'
Run a query to Download Data