NineRealms[TEMPLATE] Swaps Dimension Query
Updated 2024-11-26
999
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 dim AS (
SELECT DISTINCT
TX_ID,
MEMO,
CASE
WHEN split_part(MEMO, ':', 5) != '' THEN split_part(MEMO, ':', 5)
WHEN (MEMO LIKE '%::0' AND MEMO NOT LIKE '+%') THEN 'te-ios'
ELSE NULL
END AS AFFILIATE_ADDRESS,
COALESCE(NULLIF(SPLIT_PART(MEMO, ':', 6), ''), 0)::INT AS AFFILIATE_FEE_BPS,
LOWER(SPLIT_PART(MEMO, ':', 3)) AS OUTBOUND_ADDRESS,
CASE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))
WHEN 'A' THEN 'AVAX.AVAX'
WHEN 'B' THEN 'BTC.BTC'
WHEN 'C' THEN 'BCH.BCH'
WHEN 'N' THEN 'BNB.BNB'
WHEN 'S' THEN 'BSC.BNB'
WHEN 'D' THEN 'DOGE.DOGE'
WHEN 'E' THEN 'ETH.ETH'
WHEN 'G' THEN 'GAIA.ATOM'
WHEN 'L' THEN 'LTC.LTC'
WHEN 'R' THEN 'THOR.RUNE'
ELSE UPPER(SPLIT_PART(SPLIT_PART(MEMO, ':', 2), '-', 1))
END AS OUTBOUND_ASSET,
FIRST_VALUE(FROM_ADDRESS)
OVER (PARTITION BY TX_ID ORDER BY OUTBOUND_ADDRESS = TO_ADDRESS)
AS INBOUND_ADDRESS,
FIRST_VALUE(UPPER(SPLIT_PART(FROM_ASSET, '-', 1)))
OVER (PARTITION BY TX_ID ORDER BY OUTBOUND_ADDRESS = TO_ADDRESS)
AS INBOUND_ASSET,
NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 1), '')::INT / 1e8 AS TRADE_LIMIT,
SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,
CASE WHEN IS_STREAMING = TRUE THEN NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 2), '')::INT
END AS SWAP_INTERVAL,
CASE WHEN IS_STREAMING = TRUE THEN NULLIF(SPLIT_PART(SPLIT_PART(MEMO, ':', 4), '/', 3), '')::INT
END AS SWAP_QUANTITY
QueryRunArchived: QueryRun has been archived