flipside

    Swap Volume By Chain copy
    messariSwap Volume By Chain copy
    Updated 2025-04-08
    -- forked from pietrekt / Swap Volume By Chain @ https://flipsidecrypto.xyz/pietrekt/q/xbS3jmyMS_2N/swap-volume-by-chain

    with init AS (SELECT to_date(block_timestamp) AS day, blockchain, from_asset,
    from_e8 / POWER(10, 8) AS from_amount,
    CASE
    WHEN from_asset LIKE '%/%' THEN REPLACE(from_asset, '/', '.')
    ELSE from_asset
    END AS asset_name
    FROM thorchain.defi.fact_swaps_events),

    prices AS (select to_date(block_timestamp) AS day, pool_name, avg(asset_usd) AS asset_usd
    FROM thorchain.price.fact_prices GROUP BY day, pool_name),

    rune_prices AS (select to_date(hour) AS day, avg(price) AS rune_usd
    FROM thorchain.price.ez_prices_hourly GROUP BY day),


    usd_vol AS (SELECT a.day, blockchain, asset_name, from_amount,
    CASE
    WHEN from_asset = 'THOR.RUNE' THEN rune_usd
    WHEN from_asset = 'THOR.TOR' THEN 0
    ELSE asset_usd
    END AS from_asset_usd
    FROM init AS a LEFT JOIN prices AS b ON a.day = b.day AND a.asset_name = b.pool_name
    LEFT JOIN rune_prices AS c ON a.day = c.day),

    liq_volume AS (SELECT to_date(block_timestamp) as day, pool_name,SPLIT_PART(pool_name, '.', 0) as chain,
    rune_amount_usd, ASSET_AMOUNT_USD,
    CASE
    WHEN rune_amount_usd <> 0 AND asset_amount_usd <> 0 THEN 0
    ELSE (rune_amount_usd + asset_amount_usd) / 2
    END AS liq_volume_usd2
    FROM thorchain.defi.fact_liquidity_actions
    WHERE liq_volume_usd2 > 0),

    swap_vol AS (SELECT day, blockchain, sum(from_amount * from_asset_usd) AS swap_volume_usd2 FROM usd_vol group by day, blockchain),
    Last run: 19 days ago
    DAY
    BLOCKCHAIN
    SWAP_VOLUME_USD
    DAILY_SWAP_VOLUME_USD
    SWAP_VOLUME_USD_CUMULATIVE
    1
    		2025-04-08 00:00:00.000BTC14823906.388764335235955.683281591407308336.2628
    2
    		2025-04-08 00:00:00.000BCH47285.17408318335235955.683281591407308336.2628
    3
    		2025-04-08 00:00:00.000GAIA21300.50299873635235955.683281591407308336.2628
    4
    		2025-04-08 00:00:00.000AVAX241785.74379401635235955.683281591407308336.2628
    5
    		2025-04-08 00:00:00.000BASE3371.05151311535235955.683281591407308336.2628
    6
    		2025-04-08 00:00:00.000BSC189841.57392835635235955.683281591407308336.2628
    7
    		2025-04-08 00:00:00.000THOR16713263.092474735235955.683281591407308336.2628
    8
    		2025-04-08 00:00:00.000DOGE27010.20392800935235955.683281591407308336.2628
    9
    		2025-04-08 00:00:00.000LTC700059.90757995635235955.683281591407308336.2628
    10
    		2025-04-08 00:00:00.000ETH2468132.044217135235955.683281591407308336.2628
    11
    		2025-04-07 00:00:00.000LTC325843.48734183545295640.394785891372072380.5795
    12
    		2025-04-07 00:00:00.000DOGE46788.47266165445295640.394785891372072380.5795
    13
    		2025-04-07 00:00:00.000ETH3110709.5561570545295640.394785891372072380.5795
    14
    		2025-04-07 00:00:00.000BSC1046324.633994945295640.394785891372072380.5795
    15
    		2025-04-07 00:00:00.000GAIA20288.62456093845295640.394785891372072380.5795
    16
    		2025-04-07 00:00:00.000BCH410889.36230306745295640.394785891372072380.5795
    17
    		2025-04-07 00:00:00.000THOR27834921.993851645295640.394785891372072380.5795
    18
    		2025-04-07 00:00:00.000BASE2628.90405071345295640.394785891372072380.5795
    19
    		2025-04-07 00:00:00.000BTC12400507.997473145295640.394785891372072380.5795
    20
    		2025-04-07 00:00:00.000AVAX96737.36239089645295640.394785891372072380.5795
    ...
    11532
    954KB
    8s