m3jiproductive-yellow
    Updated 8 days ago
    WITH first_trade AS (
    -- Get the first-ever trade date for each trader on Ref Finance
    SELECT
    trader,
    MIN(DATE(block_timestamp)) AS first_trade_date
    FROM near.defi.ez_dex_swaps
    WHERE platform = 'v2.ref-finance.near'
    AND block_timestamp::DATE > '2025-01-01'
    GROUP BY trader
    ),
    daily_trades AS (
    -- Get daily swap volumes and trader activity
    SELECT
    DATE(block_timestamp) AS trade_date,
    trader,
    SUM(CASE WHEN amount_in_usd IS NULL THEN amount_out_usd ELSE 0 END) AS swap_volume_usd
    FROM near.defi.ez_dex_swaps
    WHERE platform = 'v2.ref-finance.near'
    AND block_timestamp::DATE > '2025-01-01'
    GROUP BY 1, 2
    ),
    classified_trades AS (
    -- Categorize traders as 'New' or 'Returning'
    SELECT
    d.trade_date,
    d.trader,
    d.swap_volume_usd,
    CASE
    WHEN d.trade_date = f.first_trade_date THEN 'New Trader'
    ELSE 'Returning Trader'
    END AS trader_type
    FROM daily_trades d
    JOIN first_trade f ON d.trader = f.trader
    )
    SELECT
    trade_date,
    Last run: 8 days ago
    Cohort Day
    Days Since Cohort
    Cohort Size
    Active Users
    Total Transactions
    Retention Rate (%)
    1
    2025-02-05 00:00:00.00007448474484114349100
    2
    2025-02-05 00:00:00.000102835549670
    3
    2025-02-05 00:00:00.000205834376344
    4
    2025-02-05 00:00:00.000305731176291
    5
    2025-02-05 00:00:00.000406247983124
    6
    2025-02-05 00:00:00.000506253582863
    7
    2025-02-05 00:00:00.000603880272316
    8
    2025-02-05 00:00:00.000704443068306
    9
    2025-02-06 00:00:00.0000230012300132510100
    10
    2025-02-06 00:00:00.000101231015080
    11
    2025-02-06 00:00:00.000201568018862
    12
    2025-02-06 00:00:00.000301112714219
    13
    2025-02-06 00:00:00.000401742121342
    14
    2025-02-06 00:00:00.00050860314774
    15
    2025-02-06 00:00:00.00060789312035
    16
    2025-02-06 00:00:00.000701138315633
    17
    2025-02-07 00:00:00.00004137413710868100
    18
    2025-02-07 00:00:00.0001012203175
    19
    2025-02-07 00:00:00.0002011892995
    20
    2025-02-07 00:00:00.0003011582343
    ...
    220
    10KB
    4s