MultipartiteChain origins of (currently-under-100k-RUNE pools) cumulative liquidity fees (in RUNE)
Updated 2024-01-21
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 Chain origins of cumulative liquidity fees (in RUNE) @ https://flipsidecrypto.xyz/edit/queries/a1479e1f-dcbb-4ff4-911b-fbaa1bc9ae09
/*
This is counting from the block which had the first synth transaction.
Notable blocks:
4610718 (2022-03-09): First synth transaction (Mimir MaxSynthPerAssetDepth 500 = 5%).
4897156 (2022-03-29): Mimir MaxSynthPerAssetDepth 1500 = 15%
4931416 (2022-04-01): Mimir VirtualMultSynths set as 1 (to prevent front-running, discovered by Bas1c; overrides Constants)
7134336 (2022-09-01): Mimir MinimumL1OutboundFeeUSD [unset]->100000000 = 1 USD minimum outbound fee.
*/
WITH
under100k AS (
SELECT DISTINCT pool_name
FROM (
SELECT pool_name, rune_e8
FROM thorchain.defi.fact_block_pool_depths
WHERE (pool_name NOT LIKE '%/%') AND (pool_name NOT LIKE 'THOR.%') AND (rune_e8 > 0)AND (asset_e8 > 0) AND (block_timestamp IS NOT NULL)
QUALIFY (block_timestamp = MAX(block_timestamp) OVER(PARTITION BY pool_name))
)
WHERE rune_e8 < 1e5*1e8
),
liqfees AS
(
SELECT block_timestamp,
(CASE WHEN ((to_asset LIKE '%/%') OR (from_asset LIKE '%/%')) THEN 1 ELSE 0 END) *
POWER(10,-8) * liq_fee_in_rune_e8 AS synth_liqfee,
(CASE WHEN ((to_asset LIKE '%/%') OR (from_asset LIKE '%/%')) THEN 0 ELSE 1 END) *
POWER(10,-8) * liq_fee_in_rune_e8 AS native_liqfee,
AS_CHAR(SPLIT(SPLIT(pool_name, '.')[0], '/')[0]) AS chain
FROM thorchain.defi.fact_swaps_events
--WHERE block_id >= 4610718
QueryRunArchived: QueryRun has been archived