trexolLiquidity Pool Creation Trends
Updated 2024-09-11Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
›
⌄
WITH monthly_pools AS (
SELECT
DATE_TRUNC('month', creation_time) AS creation_month,
platform,
COUNT(DISTINCT pool_address) AS new_pools,
ARRAY_AGG(DISTINCT pool_name) AS pool_names
FROM kaia.defi.dim_dex_liquidity_pools
WHERE creation_time >= '2024-01-01'
GROUP BY DATE_TRUNC('month', creation_time), platform
)
SELECT
creation_month,
platform,
new_pools,
pool_names,
SUM(new_pools) OVER (PARTITION BY platform ORDER BY creation_month) AS cumulative_pools,
AVG(new_pools) OVER (PARTITION BY platform ORDER BY creation_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_new_pools_3m
FROM monthly_pools
ORDER BY platform, creation_month;
QueryRunArchived: QueryRun has been archived