ShapeShiftAverage Swap Size by Affiliates (in USD) copy
Updated 2024-10-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
›
⌄
-- forked from Rayyyk / Average Swap Size by Affiliates (in USD) @ https://flipsidecrypto.xyz/Rayyyk/q/nv5kgkBDK4YH/average-swap-size-by-affiliates-in-usd
with stream_swap1 as (select case
when affiliate_address in ('t', 'T', 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') then 'THORSwap'
when affiliate_address in ('wr', 'thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8') then 'THORWallet'
when affiliate_address = 'tl' then 'TS Ledger'
when affiliate_address = 'cb' then 'Team CoinBot'
when affiliate_address = 'dx' then 'Asgardex'
when affiliate_address = 'ss' then 'ShapeShift'
when affiliate_address = 'xdf' then 'xDEFI'
when affiliate_address = 'rg' then 'Rango'
when affiliate_address = 'ej' then 'Edge Wallet'
when affiliate_address = 'ds' then 'DefiSpot'
when affiliate_address = 'lends' then 'Lends'
when affiliate_address = 'decentralfi' then 'DecentralFi'
when affiliate_address in ('ti', 'te', 'tr', 'td') then 'TrustWallet'
when affiliate_address is null then 'No Affiliate'
--else 'Other'
end as "Affiliates",
tx_id,
split(from_asset, '-')[0] as from_assets,
case
when from_assets ilike '%/%' then split(from_assets, '/')[1]
else split(from_assets, '.')[1]
end as from_asset_names,
split(to_asset, '-')[0] as to_assets,
case
when to_assets ilike '%/%' then split(to_assets, '/')[1]
else split(to_assets, '.')[1]
end as to_asset_names,
concat(from_asset_names, ' -> ', to_asset_names) as assets,
case when assets ilike '%RUNE' then 2
else 1
end as numbering,
sum(from_amount_usd) as usd_volume
from thorchain.defi.fact_swaps
QueryRunArchived: QueryRun has been archived