NineRealms(Agg) Period Preferred Asset Gross Revenue
    Updated 20 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
    )
    SELECT
    round(SUM(to_e8 / 1e8), 2) as Amount,
    SPLIT_PART(to_asset, '-',1) as Asset,
    TO_CHAR(Amount, 'FM9,999,999') || ' ' || Asset as formatted_string
    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 >= (SELECT start_date FROM date_range)
    AND block_timestamp < (SELECT end_date FROM date_range) + INTERVAL '1 DAY'
    GROUP BY to_asset;

    Last run: about 20 hours agoAuto-refreshes every 24 hours
    AMOUNT
    ASSET
    FORMATTED_STRING
    1
    22557.14AVAX.USDC22,557 AVAX.USDC
    1
    43B
    2s