crypto_gostmain-coffee
Updated 2024-11-09
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 vol as (SELECT
-- BLOCK_TIMESTAMP::date as day,
-- PLATFORM,
-- TX_HASH,
-- SUM (AMOUNT_IN_USD + AMOUNT_OUT_USD) as volume
-- FROM ethereum.defi.ez_dex_swaps
-- QUALIFY
-- ROW_NUMBER() OVER (PARTITION BY volume ORDER BY day ASC) = 1
-- GROUP BY block_timestamp::date, tx_hash, platform
-- LIMIT 10;
-- )
-- select volume
WITH vol AS (
SELECT
BLOCK_TIMESTAMP::date AS day,
PLATFORM,
TX_HASH,
SUM(AMOUNT_IN_USD + AMOUNT_OUT_USD) AS volume,
ROW_NUMBER() OVER (PARTITION BY day, PLATFORM ORDER BY SUM(AMOUNT_IN_USD + AMOUNT_OUT_USD) DESC) AS rn
FROM ethereum.defi.ez_dex_swaps
WHERE BLOCK_TIMESTAMP >= DATEADD(month, -6, CURRENT_DATE) -- past 6 months
GROUP BY BLOCK_TIMESTAMP::date, PLATFORM, TX_HASH
)
SELECT
day,
PLATFORM,
TX_HASH,
volume
FROM vol
WHERE rn = 1
LIMIT 10;
QueryRunArchived: QueryRun has been archived