Multipartite Cumulative liquidity fees in RUNE
Updated 2024-11-07
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
›
⌄
⌄
/*
This is counting from the block which had the first synth transaction.
Blocks of relevance:
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)
*/
WITH
liqfees AS
(
SELECT fact_swaps_events.block_timestamp, block_id,
(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
FROM thorchain.defi.fact_swaps_events INNER JOIN thorchain.core.dim_block
ON fact_swaps_events.dim_block_id = dim_block.dim_block_id
),
dategrouped AS
(
SELECT DATE(block_timestamp) AS date,
SUM(native_liqfee) AS daily_native_liquidity_fees,
SUM(synth_liqfee) AS daily_synth_liquidity_fees,
daily_native_liquidity_fees + daily_synth_liquidity_fees AS daily_full_liquidity_fees
FROM liqfees
GROUP BY date
QUALIFY ROW_NUMBER() OVER(ORDER BY date DESC) <> 1
--The last date isn't the full day yet, so leave it out.
),
datestoprepare AS
(
QueryRunArchived: QueryRun has been archived