BlockTrackertest
Updated 2024-08-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
›
⌄
-- select *
-- from thorchain.defi.fact_swaps
-- where affiliate_address ='okw'
-- limit 100
-- forked from banbannard / Affiliate Fee - RUNE @ https://flipsidecrypto.xyz/banbannard/q/Zqikq5qhAE20/affiliate-fee---rune
with base as (select tx_id,
date_trunc('day', 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 block_timestamp::date >= dateadd(month, -1, current_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,
QueryRunArchived: QueryRun has been archived