messariSwap count copy
Updated 2025-01-06
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
›
⌄
-- forked from pietrekt / Swap count @ https://flipsidecrypto.xyz/pietrekt/q/2P8FSYdVSYlh/swap-count
WITH unique_swappers AS (SELECT day, sum(unique_swapper_count) AS unique_swapper_count FROM thorchain.defi.fact_daily_pool_stats GROUP BY day),
swap_transactions AS (SELECT max(block_timestamp) as block_timestamp, tx_id, max(from_amount_usd) as volume from thorchain.defi.fact_swaps group by tx_id),
swap_count AS(SELECT to_date(block_timestamp) as day, COUNT(*) OVER(PARTITION BY day) AS swap_count from swap_transactions),
grouped AS (SELECT day, avg(swap_count) as swap_count from swap_count group by day),
joined AS (SELECT a.day, a.swap_count, unique_swapper_count FROM grouped AS a LEFT JOIN unique_swappers AS b on a.day = b.day)
SELECT *,
avg(unique_swapper_count) OVER(ORDER BY day
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW )
as unique_swapper_count_ma_30d,
SUM(swap_count) OVER (ORDER BY day) AS swap_count_cumulative
FROM joined WHERE day is not null ORDER BY day DESC
-- WITH swaps AS (SELECT day, SUM(swap_count) as swap_count, SUM(UNIQUE_SWAPPER_COUNT) as unique_swapper_count, ROW_NUMBER() OVER (ORDER BY day) as rownum FROM thorchain.defi.fact_daily_pool_stats Group BY day),
-- culmulative AS (SELECT day, (SELECT SUM(swap_count) FROM swaps as b WHERE b.rownum <= a.rownum) as swap_count_cumulative,
-- swap_count, unique_swapper_count
-- FROM swaps as a)
-- SELECT * FROM culmulative WHERE day is not null ORDER BY day DESC
QueryRunArchived: QueryRun has been archived