feyikemi2024-06-23 05:39 PM
Updated 2024-06-23
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
›
⌄
--Credit to BlockTracker
-- CTE to preprocess data
WITH initial_data AS (
SELECT
tx_id,
date_trunc('day', block_timestamp) AS date,
affiliate_address AS aff_addr,
affiliate_fee_basis_points AS aff_fee_bp,
split_part(from_asset, '-', 1) AS from_asset_base,
CASE
WHEN from_asset_base ILIKE '%/%' THEN split_part(from_asset_base, '/', 2)
ELSE split_part(from_asset_base, '.', 2)
END AS from_asset_name,
split_part(to_asset, '-', 1) AS to_asset_base,
CASE
WHEN to_asset_base ILIKE '%/%' THEN split_part(to_asset_base, '/', 2)
ELSE split_part(to_asset_base, '.', 2)
END AS to_asset_name,
CONCAT(from_asset_name, ' -> ', to_asset_name) AS asset_path,
CASE
WHEN asset_path ILIKE '%RUNE' THEN 2
ELSE 1
END AS numbering,
SUM(from_amount_usd / rune_usd) AS swap_vol
FROM thorchain.defi.fact_swaps
WHERE date >= '2024-01-01'
GROUP BY tx_id, date, aff_addr, aff_fee_bp, from_asset, to_asset
),
-- CTE to aggregate swap directions
agg_directions AS (
SELECT
date,
tx_id,
aff_addr,
QueryRunArchived: QueryRun has been archived