Updated 3 days ago
    WITH SwapData AS (
    SELECT
    DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS "Date",
    blockchain,
    COUNT(*) AS "Swap Count",
    COUNT(DISTINCT FROM_ADDRESS) AS "Unique Swapper Count",
    SUM(CASE WHEN FROM_ASSET = 'THOR.RUNE' THEN FROM_AMOUNT
    WHEN TO_ASSET = 'THOR.RUNE' THEN TO_AMOUNT
    ELSE 0 END) AS "Total Volume THOR.RUNE"
    FROM thorchain.defi.fact_swaps
    WHERE blockchain = 'BASE'
    AND block_timestamp BETWEEN '2024-12-11' AND '2025-02-11'
    GROUP BY 1, 2
    ),
    PriceData AS (
    SELECT
    DATE_TRUNC('HOUR', hour) AS "Date",
    AVG(price) AS "Price"
    FROM thorchain.price.ez_prices_hourly
    WHERE
    symbol = 'RUNE'
    AND DATE_TRUNC('day', hour) BETWEEN '2024-10-28' AND CURRENT_DATE - 1
    GROUP BY 1
    ),
    SwapDataWithPrice AS (
    SELECT
    sd.*,
    pd."Price",
    sd."Total Volume THOR.RUNE" * pd."Price" as "Volume_USD"
    FROM SwapData sd
    LEFT JOIN PriceData pd ON sd."Date" = pd."Date"
    ),
    CumulativeData AS (
    SELECT
    "Date",
    SUM("Unique Swapper Count") OVER (ORDER BY "Date") AS "Cumulative Users",
    Last run: 3 days ago
    Date
    BLOCKCHAIN
    Swap Count
    Unique Swapper Count
    Cumulative Users
    Cumulative Volume
    Cumulative Swap
    RUNE VOLUME
    Cumulative RUNE Volume
    Volume USD
    1
    2025-01-29 00:00:00.000BASE656171901427892.79390924862746977.298595331427892.7939092496303.462120426
    2
    2025-01-12 00:00:00.000BASE11110.05721044112.9067082210.05721044110.057210441
    3
    2025-01-21 00:00:00.000BASE2427967805308.80758554189145501.8434564805308.8075855483066.120275248
    4
    2025-01-14 00:00:00.000BASE748221.1244175261257.38451135221.124417526184.778126547
    5
    2025-01-22 00:00:00.000BASE5572805665.8394210044194102.59535503805665.839421004357.031835504
    6
    2025-01-19 00:00:00.000BASE242104654886.69467383837511240.9893218254886.69467383841928.890170389
    7
    2025-01-24 00:00:00.000BASE167341141065375.6677283761569553.778339611065375.6677283728756.872802226
    8
    2025-01-26 00:00:00.000BASE45111441151126.6933110269432736.671941451151126.693311027471.114400158
    9
    2025-01-15 00:00:00.000BASE10412429.4216001342263.5052386429.421600134208.297182608
    10
    2025-02-08 00:00:00.000BASE7472991942996.922328261419125186.18387121942996.9223282629467.835129304
    11
    2025-01-17 00:00:00.000BASE76315220.1145076246932.953488755220.114507624114.67814085
    12
    2025-01-16 00:00:00.000BASE4013255105.436366774621336.004219045105.4363667744676.01476664
    13
    2025-01-27 00:00:00.000BASE2781521154053.8659425869701143.42680921154053.865942582927.172631552
    14
    2025-02-07 00:00:00.000BASE136582921913529.087198951411788604.263831811913529.08719895113413.457704717
    15
    2025-02-01 00:00:00.000BASE80182231512540.8138675891955095.346871421512540.8138675810904.042304839
    16
    2025-02-02 00:00:00.000BASE1662182411650719.8811361110857114197.576254991650719.88113611138179.067268538
    17
    2025-01-23 00:00:00.000BASE17958801036618.79492614598971724.520343211036618.79492614230952.955505136
    18
    2025-01-28 00:00:00.000BASE1001211731331589.33178882797173361.762746381331589.33178882177535.46584624
    19
    2025-01-31 00:00:00.000BASE972051501636.77156274911575.743637311501636.77156274168.150874828
    20
    2025-02-06 00:00:00.000BASE133102841800115.629494241275214101.360973171800115.6294942418613.796484584
    30
    3KB
    2s