NineRealmsThis Period Chain Volume Breakdown
    Updated 2024-10-14
    WITH
    affiliates AS (
    SELECT
    TRIM(f.value) AS affiliate
    FROM LATERAL FLATTEN(INPUT => SPLIT('{{affiliates}}', ',')) AS f
    )
    ,
    date_range AS (
    SELECT
    (
    CASE
    WHEN '{{start_date}}' = '' AND '{{end_date}}' = '' THEN DATE_TRUNC('MONTH', CURRENT_DATE)
    WHEN '{{start_date}}' = '' THEN DATE_TRUNC('MONTH', '{{end_date}}'::DATE)
    ELSE '{{start_date}}'::DATE
    END
    ) AS start_date,
    (
    CASE
    WHEN '{{start_date}}' = '' AND '{{end_date}}' = '' THEN CURRENT_DATE
    WHEN '{{end_date}}' = '' THEN DATE_TRUNC('MONTH', '{{start_date}}'::DATE) + INTERVAL '1 MONTH' - INTERVAL '1 DAY'
    ELSE '{{end_date}}'::DATE
    END
    ) AS end_date
    )
    , attempted_txs AS (
    SELECT
    TX_ID,
    BLOCKCHAIN,
    SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,
    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,
    CASE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))
    WHEN 'A' THEN 'AVAX.AVAX'
    QueryRunArchived: QueryRun has been archived