Spot-WiggumAffiliates/Non-Affiliate Market Share
Updated 2024-11-17
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 Affiliates Monthly Volumes @ https://flipsidecrypto.xyz/studio/queries/e42529aa-279f-4270-a45a-f4738dae4b0f
-- forked from SwapKit Affiliates Daily Volumes @ https://flipsidecrypto.xyz/studio/queries/68248f53-fea9-40d9-a15a-d5a6efcd4ef4
WITH attempted_txs AS (
SELECT
DISTINCT tx_id,
block_timestamp,
from_address,
from_amount_usd,
affiliate_address
FROM
thorchain.defi.fact_swaps
WHERE
block_timestamp >= '2024-01-01'
),
successful_txs AS (
SELECT *
FROM attempted_txs AS a
WHERE NOT EXISTS (
SELECT 1
FROM thorchain.defi.fact_refund_events AS r
WHERE r.tx_id = a.tx_id
)
),
affiliate_swaps AS (
SELECT
DISTINCT tx_id,
DATE_TRUNC(month,block_timestamp) AS month,
from_address,
from_amount_usd,
affiliate_address,
CASE
WHEN affiliate_address IN ('t', 'T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor17suv0n437snv68axkx64whutkrvefv7pzq7xep','ti', 'te','tr', 'td','dx','thor1ap5vn4svwkpch2c9jm7hlpr2pj47e62xwpcvtw','wr','thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8','ds','rg','ss','lends','bp','dcf','tl','ej','lifi','okw','sy','oky','xdf','zengo','cb','pl','bqx','ELD','cakewallet','kf','ferz','runifier','zcx-com', '0xbami','cs','moca','va','vi','v0', 'll', 'tb','is' ) THEN 'Affiliate'
ELSE 'Unknown/ No Affiliate' -- edit this one day to filter by affiliate address not null
END AS is_affiliate
Auto-refreshes every 12 hours
QueryRunArchived: QueryRun has been archived