SalehUser Distribution Based on Swap Volume
Updated 2024-10-08
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 lst_all as (
select
block_timestamp::date as date
,tx_id
,from_address as wallet
,split(pool_name,'-')[0] as Pool
,iff( FROM_AMOUNT_USD>=TO_AMOUNT_USD,FROM_AMOUNT_USD,TO_AMOUNT_USD) as amount_usd
from thorchain.defi.fact_swaps
-- where block_timestamp::date>='2024-01-01'
where AFFILIATE_ADDRESS in('T','t','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk')
and _TX_TYPE='swap'
)
,lst_all_raw as (
SELECT
wallet
,sum(amount_usd) as "Amount USD"
FROM lst_all
group by 1
)
select
case
when ("Amount USD"!=0) and "Amount USD"<=10 then '[less than $10]'
when "Amount USD">=11 and "Amount USD"<=100 then '[$11-$100]'
when "Amount USD">=101 and "Amount USD"<=500 then '[$101-$500]'
when "Amount USD">=501 and "Amount USD"<=1000 then '[$501-$1,000]'
when "Amount USD">=1001 and "Amount USD"<=5000 then '[$1,001-$5,000]'
when "Amount USD">=5001 and "Amount USD"<=10000 then '[$5,001-$10,000]'
when "Amount USD">=10001 then '[More than $10,000]'
else 'Other'
end as "Volume($)-Based Distribution"
,count(DISTINCT wallet ) as Traders
from lst_all_raw
group by 1
order by Traders desc
QueryRunArchived: QueryRun has been archived