flipside

    counts1
    datavortexcounts1
    Updated 2025-05-18

    WITH total_swaps AS (
    SELECT
    COUNT(DISTINCT tx_id) AS total_swaps
    FROM
    sei.defi.ez_dex_swaps
    ),
    weekly_swaps AS (
    SELECT
    DATE_TRUNC('week', block_timestamp) AS swap_date,
    COUNT(DISTINCT tx_id) AS weekly_swap_count,
    SUM(COUNT(DISTINCT tx_id)) OVER (ORDER BY DATE_TRUNC('week', block_timestamp)) AS cumulative_swaps
    FROM
    sei.defi.ez_dex_swaps
    GROUP BY
    swap_date
    ),
    platform_dominance AS (
    SELECT
    platform,
    COUNT(DISTINCT tx_id) AS swap_count,
    (
    COUNT(DISTINCT tx_id) * 1.0 / SUM(COUNT(DISTINCT tx_id)) OVER ()
    ) * 100 AS dominance_percentage
    FROM
    sei.defi.ez_dex_swaps
    GROUP BY
    platform
    ),
    average_swaps_per_user AS (
    SELECT
    COUNT(DISTINCT tx_id) * 1.0 / COUNT(DISTINCT swapper) AS avg_swaps_per_user
    FROM
    sei.defi.ez_dex_swaps
    ),
    new_vs_returning_swappers_cte AS (
    Last run: 10 days ago
    TOTAL_SWAPS
    SWAP_DATE
    WEEKLY_SWAP_COUNT
    CUMULATIVE_SWAPS
    PLATFORM
    SWAP_COUNT
    DOMINANCE_PERCENTAGE
    AVG_SWAPS_PER_USER
    NEW_SWAPPERS
    RETURNING_SWAPPERS
    1
    		88764302023-08-14 00:00:00.00058315831dragonswap573978455.695225.1892941397509
    2
    		88764302023-08-14 00:00:00.00058315831jellyswap178529517.323425.1892941397509
    3
    		88764302023-08-14 00:00:00.00058315831sailorswap156573815.192925.1892941397509
    4
    		88764302023-08-14 00:00:00.00058315831astroport120538911.696325.1892941397509
    5
    		88764302023-08-14 00:00:00.00058315831fuzio67710.065725.1892941397509
    6
    		88764302023-08-14 00:00:00.00058315831seaswap27320.026525.1892941397509
    7
    		88764302023-08-21 00:00:00.000957915410dragonswap573978455.695225.1892941103487
    8
    		88764302023-08-21 00:00:00.000957915410jellyswap178529517.323425.1892941103487
    9
    		88764302023-08-21 00:00:00.000957915410sailorswap156573815.192925.1892941103487
    10
    		88764302023-08-21 00:00:00.000957915410astroport120538911.696325.1892941103487
    11
    		88764302023-08-21 00:00:00.000957915410fuzio67710.065725.1892941103487
    12
    		88764302023-08-21 00:00:00.000957915410seaswap27320.026525.1892941103487
    13
    		88764302023-08-28 00:00:00.000703422444dragonswap573978455.695225.189294228287
    14
    		88764302023-08-28 00:00:00.000703422444jellyswap178529517.323425.189294228287
    15
    		88764302023-08-28 00:00:00.000703422444sailorswap156573815.192925.189294228287
    16
    		88764302023-08-28 00:00:00.000703422444astroport120538911.696325.189294228287
    17
    		88764302023-08-28 00:00:00.000703422444fuzio67710.065725.189294228287
    18
    		88764302023-08-28 00:00:00.000703422444seaswap27320.026525.189294228287
    19
    		88764302023-09-04 00:00:00.000408526529dragonswap573978455.695225.189294157168
    20
    		88764302023-09-04 00:00:00.000408526529jellyswap178529517.323425.189294157168
    ...
    552
    51KB
    11s