trexolLiquidity Pool Creation Trends
    Updated 2024-09-11
    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