shaunoffAffiliate Fee - RUNE copy
Updated 2024-11-14
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
›
⌄
-- forked from winnie-fs / Affiliate Fee - RUNE @ https://flipsidecrypto.xyz/winnie-fs/q/eBgZt8kUEw5J/affiliate-fee---rune
with base as (select tx_id,
date_trunc('{{granularity}}', block_timestamp) as date,
affiliate_address, affiliate_fee_basis_points,
split(from_asset, '-')[0] as from_assets,
case
when from_assets ilike '%/%' then split(from_assets, '/')[1]
else split(from_assets, '.')[1]
end as from_asset_names,
split(to_asset, '-')[0] as to_assets,
case
when to_assets ilike '%/%' then split(to_assets, '/')[1]
else split(to_assets, '.')[1]
end as to_asset_names,
concat(from_asset_names, ' -> ', to_asset_names) as assets,
case when assets ilike '%RUNE' then 2
else 1
end as numbering,
sum(from_amount_usd/rune_usd) as swap_volume
from thorchain.defi.fact_swaps
where date >= '{{start_date}}'
and date <= '{{end_date}}'--and tx_id = '902C27D4CDDF672D7E88F0CCBCB402ED1303827E90631320C849F5DD1294D5E3'
--and tx_id in (select tx_id from thorchain.defi.fact_swaps_events where memo ilike '%/%/%')
group by tx_id, date,
affiliate_address, affiliate_fee_basis_points,
from_asset, to_asset),
base2 as (select date,
tx_id,
affiliate_address, affiliate_fee_basis_points,
array_agg(distinct assets) within group (order by assets asc) as swap_direction, --merging 2 sep path to 1
sum(swap_volume) as swap_volume
from base
group by 1,2,3,4),
QueryRunArchived: QueryRun has been archived