mogarchy2024-06-13 07:06 PM copy copy copy
Updated 2025-02-10
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
›
⌄
-- forked from ehsanst / 2024-06-13 07:06 PM copy copy @ https://flipsidecrypto.xyz/ehsanst/q/c6elER8fNHR9/2024-06-13-07-06-pm-copy
-- forked from 2024-06-13 07:06 PM copy @ https://flipsidecrypto.xyz/studio/queries/4e1c67cf-5cb9-42eb-994b-4e997020ea2c
-- forked from 2024-06-13 07:06 PM @ https://flipsidecrypto.xyz/studio/queries/8ccda1d4-7dcf-4c65-8b8b-0c307d8492dc
WITH CTE_Filtered AS (
SELECT
tx_id,
memo,
to_e8
FROM thorchain.defi.fact_swaps_events
),
CTE_MatchingGroups AS (
SELECT
tx_id,
SUM(CASE WHEN memo LIKE '%thor12l42ts3gwuenfqqyjwk2qvq48ym9s4y6z39jua%' THEN to_e8 ELSE 0 END) AS aff_fee
FROM CTE_Filtered
GROUP BY tx_id
HAVING
SUM(CASE WHEN memo LIKE '%thor12l42ts3gwuenfqqyjwk2qvq48ym9s4y6z39jua%' THEN 1 ELSE 0 END) > 0
AND
SUM(CASE WHEN memo LIKE '%lifi/-_:0/20%' THEN 1 ELSE 0 END) > 0
)
SELECT
tx_id,
aff_fee
FROM
CTE_MatchingGroups
ORDER BY
tx_id DESC;
QueryRunArchived: QueryRun has been archived