DATE | AFFILIATE_ADDRESS | N_TXNS | CUM_TXNS_BY_AFFILIATE | N_USERS | TOTAL_USERS_ALL_TIME | VOLUME | CUM_VOLUME_BY_AFFILIATE | AVG_VOLUME | MEDIAN_VOLUME | |
|---|---|---|---|---|---|---|---|---|---|---|
1 | 2025-02-17 00:00:00.000 | v0 | 2 | 68 | 1 | 475 | 644.551522219 | 487487.248485308 | 322.27576111 | 322.27576111 |
2 | 2025-02-17 00:00:00.000 | va | 8 | 523 | 3 | 475 | 73963.39342062 | 5837237.6035657 | 2958.535736825 | 1510.474926087 |
3 | 2025-02-17 00:00:00.000 | vi | 26 | 698 | 15 | 475 | 622209.907848061 | 23745728.1545753 | 2440.038854306 | 2541.605390377 |
4 | 2025-02-10 00:00:00.000 | v0 | 2 | 66 | 1 | 475 | 41995.284670879 | 486842.696963088 | 5249.41058386 | 5459.202309395 |
5 | 2025-02-10 00:00:00.000 | va | 56 | 515 | 8 | 475 | 250964.79422034 | 5763274.21014508 | 4047.819261618 | 2480.628086544 |
6 | 2025-02-10 00:00:00.000 | vi | 18 | 672 | 10 | 475 | 286008.076106782 | 23123518.2467272 | 3213.57388884 | 3575.168307709 |
7 | 2025-02-03 00:00:00.000 | va | 16 | 459 | 10 | 475 | 585354.479408572 | 5512309.41592474 | 2046.693983946 | 1595.72909225 |
8 | 2025-02-03 00:00:00.000 | vi | 76 | 654 | 44 | 475 | 3412072.57585894 | 22837510.1706204 | 2435.455086266 | 2505.964928968 |
9 | 2025-01-27 00:00:00.000 | v0 | 5 | 64 | 5 | 475 | 43402.374005087 | 444847.412292209 | 3945.670364099 | 944.088691008 |
10 | 2025-01-27 00:00:00.000 | va | 170 | 443 | 52 | 475 | 1838030.05505122 | 4926954.93651616 | 3058.286281283 | 350.139403209 |
11 | 2025-01-27 00:00:00.000 | vi | 170 | 578 | 117 | 475 | 6438263.40643288 | 19425437.5947615 | 686.016345917 | 47.809970507 |
12 | 2025-01-20 00:00:00.000 | v0 | 3 | 59 | 2 | 475 | 8326.383571207 | 401445.038287122 | 2775.461190402 | 1052.713127304 |
13 | 2025-01-20 00:00:00.000 | va | 39 | 273 | 27 | 475 | 659785.860826178 | 3088924.88146495 | 1502.929067941 | 583.804877815 |
14 | 2025-01-20 00:00:00.000 | vi | 91 | 408 | 61 | 475 | 2597071.96087999 | 12987174.1883286 | 2019.496081555 | 119.659101893 |
15 | 2025-01-13 00:00:00.000 | va | 8 | 234 | 7 | 475 | 694996.597965288 | 2429139.02063877 | 17374.914949132 | 7496.938489407 |
16 | 2025-01-13 00:00:00.000 | vi | 29 | 317 | 24 | 475 | 860845.338409975 | 10390102.2274486 | 6238.009698623 | 4739.798231522 |
17 | 2025-01-06 00:00:00.000 | va | 21 | 226 | 15 | 475 | 201759.388407863 | 1734142.42267348 | 2690.125178772 | 247.364498518 |
18 | 2025-01-06 00:00:00.000 | vi | 32 | 288 | 27 | 475 | 1240412.53270456 | 9529256.88903865 | 8735.299526088 | 6298.738341693 |
19 | 2024-12-30 00:00:00.000 | va | 7 | 205 | 8 | 475 | 70459.269255416 | 1532383.03426562 | 4144.662897377 | 3359.471848392 |
20 | 2024-12-30 00:00:00.000 | vi | 16 | 256 | 9 | 475 | 544488.256514681 | 8288844.35633409 | 7357.949412361 | 8555.531605028 |
paaaoThorchain-VULT-Swaps
Updated 2025-02-24
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
›
⌄
WITH affiliate_swaps AS (
SELECT *
FROM thorchain.defi.fact_swaps
WHERE affiliate_address IN ('va','vi','v0')
AND block_timestamp::date >= '2024-04-12'
),
total_users AS (
SELECT COUNT(DISTINCT FROM_ADDRESS) as total_users
FROM affiliate_swaps
)
SELECT
date_trunc('{{granularity}}', BLOCK_TIMESTAMP) as date,
affiliate_address,
count(distinct TX_ID) as n_txns,
sum(n_txns) over (PARTITION BY affiliate_address order by date) as cum_txns_by_affiliate,
count(DISTINCT FROM_ADDRESS) as n_users,
(SELECT total_users FROM total_users) as total_users_all_time,
sum(coalesce(FROM_AMOUNT_USD, TO_AMOUNT_USD)) as volume,
sum(volume) over (PARTITION BY affiliate_address order by date) as cum_volume_by_affiliate,
avg(FROM_AMOUNT_USD) as avg_volume,
median(FROM_AMOUNT_USD) as median_volume
FROM affiliate_swaps
GROUP BY 1, 2
ORDER BY date DESC, affiliate_address
Last run: about 2 months ago
82
8KB
2s