AMOUNT | ASSET | FORMATTED_STRING | |
---|---|---|---|
1 | 22557.14 | AVAX.USDC | 22,557 AVAX.USDC |
NineRealms(Agg) Period Preferred Asset Gross Revenue
Updated 20 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
›
⌄
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
1
43B
2s