DAY | DAILY_UNIQUE_SWAPPER_COUNT | UNIQUE_SWAPPER_COUNT_30D_MA | WEEKLY_UNIQUE_SWAPPER_COUNT | MONTHLY_UNIQUE_SWAPPER_COUNT | |
|---|---|---|---|---|---|
1 | 2025-06-13 00:00:00.000 | 710 | 1032.8 | ||
2 | 2025-06-12 00:00:00.000 | 944 | 1063.366 | ||
3 | 2025-06-11 00:00:00.000 | 1083 | 1085.166 | ||
4 | 2025-06-10 00:00:00.000 | 1046 | 1110.333 | ||
5 | 2025-06-09 00:00:00.000 | 858 | 1132.4 | 4084 | |
6 | 2025-06-08 00:00:00.000 | 586 | 1158.966 | ||
7 | 2025-06-07 00:00:00.000 | 668 | 1191.6 | ||
8 | 2025-06-06 00:00:00.000 | 845 | 1240.466 | ||
9 | 2025-06-05 00:00:00.000 | 1206 | 1277.266 | ||
10 | 2025-06-04 00:00:00.000 | 982 | 1310.6 | ||
11 | 2025-06-03 00:00:00.000 | 787 | 1320.6 | ||
12 | 2025-06-02 00:00:00.000 | 821 | 1324.6 | 5041 | |
13 | 2025-06-01 00:00:00.000 | 738 | 1323.033 | 9218 | |
14 | 2025-05-31 00:00:00.000 | 850 | 1327.033 | ||
15 | 2025-05-30 00:00:00.000 | 1135 | 1318.133 | ||
16 | 2025-05-29 00:00:00.000 | 1095 | 1307.033 | ||
17 | 2025-05-28 00:00:00.000 | 1135 | 1298.2 | ||
18 | 2025-05-27 00:00:00.000 | 1253 | 1288.333 | ||
19 | 2025-05-26 00:00:00.000 | 967 | 1274.266 | 6094 | |
20 | 2025-05-25 00:00:00.000 | 936 | 1269.466 |
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 17 hours ago
...
1452
71KB
3s