sebateau22-AHAffiliate_Weekly_Revenue
Updated 2024-12-29
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 Daily Volumes @ https://flipsidecrypto.xyz/studio/queries/5d849804-ebbb-4983-8c33-12523e13c899
-- Inspiration from SpottWiggum
-- 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_fee_basis_points,
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(week,block_timestamp) AS week,
from_address,
from_amount_usd,
affiliate_address,
affiliate_fee_basis_points,
CASE
WHEN affiliate_address IN ('t', 'T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') THEN 'Thorswap'
QueryRunArchived: QueryRun has been archived