NineRealms[TEMPLATE] Swaps Dimension Query
    Updated 2024-11-26
    WITH dim AS (
    SELECT DISTINCT
    TX_ID,
    MEMO,
    CASE
    WHEN split_part(MEMO, ':', 5) != '' THEN split_part(MEMO, ':', 5)
    WHEN (MEMO LIKE '%::0' AND MEMO NOT LIKE '+%') THEN 'te-ios'
    ELSE NULL
    END AS AFFILIATE_ADDRESS,
    COALESCE(NULLIF(SPLIT_PART(MEMO, ':', 6), ''), 0)::INT AS AFFILIATE_FEE_BPS,
    LOWER(SPLIT_PART(MEMO, ':', 3)) AS OUTBOUND_ADDRESS,
    CASE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))
    WHEN 'A' THEN 'AVAX.AVAX'
    WHEN 'B' THEN 'BTC.BTC'
    WHEN 'C' THEN 'BCH.BCH'
    WHEN 'N' THEN 'BNB.BNB'
    WHEN 'S' THEN 'BSC.BNB'
    WHEN 'D' THEN 'DOGE.DOGE'
    WHEN 'E' THEN 'ETH.ETH'
    WHEN 'G' THEN 'GAIA.ATOM'
    WHEN 'L' THEN 'LTC.LTC'
    WHEN 'R' THEN 'THOR.RUNE'
    ELSE UPPER(SPLIT_PART(SPLIT_PART(MEMO, ':', 2), '-', 1))
    END AS OUTBOUND_ASSET,
    FIRST_VALUE(FROM_ADDRESS)
    OVER (PARTITION BY TX_ID ORDER BY OUTBOUND_ADDRESS = TO_ADDRESS)
    AS INBOUND_ADDRESS,
    FIRST_VALUE(UPPER(SPLIT_PART(FROM_ASSET, '-', 1)))
    OVER (PARTITION BY TX_ID ORDER BY OUTBOUND_ADDRESS = TO_ADDRESS)
    AS INBOUND_ASSET,
    NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 1), '')::INT / 1e8 AS TRADE_LIMIT,
    SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,
    CASE WHEN IS_STREAMING = TRUE THEN NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 2), '')::INT
    END AS SWAP_INTERVAL,
    CASE WHEN IS_STREAMING = TRUE THEN NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 3), '')::INT
    END AS SWAP_QUANTITY
    QueryRunArchived: QueryRun has been archived