DAY | DAILY_UNIQUE_SWAPPER_COUNT | UNIQUE_SWAPPER_COUNT_30D_MA | WEEKLY_UNIQUE_SWAPPER_COUNT | MONTHLY_UNIQUE_SWAPPER_COUNT | |
|---|---|---|---|---|---|
1 | 2025-05-29 00:00:00.000 | 618 | 1291.133 | ||
2 | 2025-05-28 00:00:00.000 | 1135 | 1298.2 | ||
3 | 2025-05-27 00:00:00.000 | 1253 | 1288.333 | ||
4 | 2025-05-26 00:00:00.000 | 967 | 1274.266 | 3585 | |
5 | 2025-05-25 00:00:00.000 | 936 | 1269.466 | ||
6 | 2025-05-24 00:00:00.000 | 917 | 1277.8 | ||
7 | 2025-05-23 00:00:00.000 | 1408 | 1289.133 | ||
8 | 2025-05-22 00:00:00.000 | 1889 | 1289.6 | ||
9 | 2025-05-21 00:00:00.000 | 1382 | 1275.633 | ||
10 | 2025-05-20 00:00:00.000 | 1130 | 1267.466 | ||
11 | 2025-05-19 00:00:00.000 | 1125 | 1255.133 | 7555 | |
12 | 2025-05-18 00:00:00.000 | 994 | 1246.5 | ||
13 | 2025-05-17 00:00:00.000 | 920 | 1246.6 | ||
14 | 2025-05-16 00:00:00.000 | 1204 | 1249.733 | ||
15 | 2025-05-15 00:00:00.000 | 1370 | 1244.733 | ||
16 | 2025-05-14 00:00:00.000 | 1627 | 1237.066 | ||
17 | 2025-05-13 00:00:00.000 | 1598 | 1226.233 | ||
18 | 2025-05-12 00:00:00.000 | 1838 | 1211.266 | 8164 | |
19 | 2025-05-11 00:00:00.000 | 1708 | 1187.4 | ||
20 | 2025-05-10 00:00:00.000 | 1655 | 1167.5 |
messari2024-07-24 11:31 AM
Updated 2025-04-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
›
⌄
with daily_uniq_swappers AS (SELECT to_date(block_timestamp) as day, COUNT(DISTINCT from_address) as daily_unique_swapper_count
FROM thorchain.defi.fact_swaps_events WHERE day IS NOT NULL group by day),
monthly_uniq_swappers AS(SELECT trunc(block_timestamp, 'month') as month,
COUNT(DISTINCT from_address) as monthly_unique_swapper_count FROM thorchain.defi.fact_swaps_events
WHERE month IS NOT NULL
group by month),
weekly_uniq_swappers AS(SELECT trunc(block_timestamp, 'week') as week,
COUNT(DISTINCT from_address) as weekly_unique_swapper_count FROM thorchain.defi.fact_swaps_events
WHERE week IS NOT NULL
group by week)
select day, daily_unique_swapper_count,
avg(daily_unique_swapper_count) OVER(ORDER BY day
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
as unique_swapper_count_30d_ma,
weekly_unique_swapper_count, monthly_unique_swapper_count
from daily_uniq_swappers AS a
LEFT JOIN weekly_uniq_swappers AS b ON a.day = b.week
LEFT JOIN monthly_uniq_swappers AS c ON a.day = c.month
ORDER BY day DESC
Last run: about 19 hours ago
...
1437
71KB
3s