NineRealmsAffiliate Report Parameters
    Updated 18 hours ago
    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
    )
    , preferred_assets AS (
    SELECT LISTAGG(PREFERRED_ASSET) WITHIN GROUP(ORDER BY PREFERRED_ASSET) AS preferred_assets
    FROM (
    SELECT DISTINCT SPLIT_PART(to_asset, '-',1) AS PREFERRED_ASSET
    FROM thorchain.defi.fact_swaps_events
    WHERE memo LIKE 'THOR-PREFERRED-ASSET-%'
    AND SPLIT_PART(memo, '-',4) IN (SELECT affiliate FROM affiliates)
    AND block_timestamp::date >= '2023-01-01'::DATE
    ) AS p
    )

    SELECT
    '{{affiliates}}' AS AFFILIATE,
    Last run: about 18 hours agoAuto-refreshes every 24 hours
    AFFILIATE
    START_DATE
    END_DATE
    TIME_ZONE
    PREFERRED_ASSETS
    1
    wr2025-01-012025-01-21UTCAVAX.USDCETH.USDC
    1
    60B
    4s