jackguyWho's Got The Flow? 4
Updated 2022-11-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
28
29
30
31
32
33
34
35
36
›
⌄
with tab1 as (
SELECT
user1 as wallet,
sum(in_volume - out_volume) as volume
FROM (
SELECT
sender as user1,
sum(amount) as out_volume
FROM flow.core.ez_token_transfers
WHERE token_contract LIKE 'A.1654653399040a61.FlowToken'
GROUP BY 1
) as a JOIN (
SELECT
RECIPIENT as user2,
sum(amount) as in_volume
FROM flow.core.ez_token_transfers
WHERE token_contract LIKE 'A.1654653399040a61.FlowToken'
GROUP BY 1
) as b ON user1 = user2
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50
), tab2 as (
SELECT
date_trunc('day', block_timestamp) as day,
--CASE WHEN recipient in (SELECT wallet from tab1) THEN wallet END
CASE WHEN recipient in (SELECT wallet from tab1) THEN recipient ELSE sender END as user2,
sum(CASE WHEN recipient in (SELECT wallet from tab1) THEN amount END) as in_volume,
sum(CASE WHEN sender in (SELECT wallet from tab1) THEN amount END) as out_volume
FROM flow.core.ez_token_transfers
WHERE token_contract LIKE 'A.1654653399040a61.FlowToken'
AND (recipient in (SELECT wallet from tab1)
or sender in (SELECT wallet from tab1))
GROUP BY 1,2
Run a query to Download Data