AFFILIATE | START_DATE | END_DATE | TIME_ZONE | PREFERRED_ASSETS | |
---|---|---|---|---|---|
1 | wr | 2025-01-01 | 2025-01-21 | UTC | AVAX.USDCETH.USDC |
NineRealmsAffiliate Report Parameters
Updated 18 hours ago
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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
1
60B
4s