mlhUntitled Query
Updated 2022-11-29Copy Reference Fork
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 tb1 as (select
distinct recipient as user1,
sum(amount) as total_recieved
from flow.core.ez_token_transfers
where token_contract = 'A.1654653399040a61.FlowToken'
and tx_succeeded = 'TRUE'
group by 1)
select
count(distinct tx_id) as swaps,
count(distinct TRADER) as swappers,
sum(TOKEN_IN_AMOUNT*a.token_price) as USD_volume
from flow.core.ez_swaps s left join (select
date_trunc('day',TIMESTAMP) as day,
TOKEN_CONTRACT,
Symbol,
avg(price_usd) as token_price,
median(price_usd) as token_price1
from flow.core.fact_prices
group by 1,2,3
) a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day
where TRADER in (select users from (select
distinct user1 as users,
total_recieved - total_sent as net_flow
from tb1 s left join (select
distinct sender as user2,
sum(amount) as total_sent
from flow.core.ez_token_transfers s left join tb1 b on s.sender=b.user1
where token_contract = 'A.1654653399040a61.FlowToken'
and tx_succeeded = 'TRUE'
and sender in (select user1 from tb1)
group by 1) b on s.user1 = b.user2
left join flow.core.dim_contract_labels c on s.user1 = c.account_address
where total_recieved>total_sent
and CONTRACT_NAME is null
having net_flow >= 10000
Run a query to Download Data