NineRealms(Time-Series) Model 1 MinBPS Adjusted Swap Fees
    Updated 2024-10-02
    WITH swaps AS (
    SELECT
    BLOCK_TIMESTAMP::DATE AS DATE,
    POOL_NAME,
    FROM_ASSET = 'THOR.RUNE' AS DID_BUY_ASSET,
    FROM_AMOUNT_USD,
    1.0 * LIQ_FEE_RUNE_USD / FROM_AMOUNT_USD * 10000 AS SWAP_SLIP_BP,
    LIQ_FEE_RUNE,
    LIQ_FEE_RUNE_USD,
    GREATEST(SWAP_SLIP_BP, {{MinBPS}}) AS ADJUSTED_SWAP_SLIP_BP
    FROM thorchain.defi.fact_swaps
    WHERE DATE >= '2024-01-01'
    AND POOL_NAME NOT LIKE '%/%'
    AND FROM_ASSET NOT LIKE '%/%'
    AND TO_ASSET NOT LIKE '%/%'
    AND POOL_NAME NOT LIKE 'THOR%'
    )
    , stacked AS (
    SELECT
    DATE,
    FALSE AS MIN_BPS_APPLIED,
    POOL_NAME,
    DID_BUY_ASSET,
    SWAP_SLIP_BP,
    FROM_AMOUNT_USD
    FROM swaps
    UNION
    SELECT
    DATE,
    TRUE AS MIN_BPS_APPLIED,
    POOL_NAME,
    DID_BUY_ASSET,
    ADJUSTED_SWAP_SLIP_BP AS SWAP_SLIP_BP,
    FROM_AMOUNT_USD
    FROM swaps
    )
    QueryRunArchived: QueryRun has been archived