flipside

    Daily Liquidity Fees v. Block Rewards (FORKED FROM and CREDIT TO Polaris_9R)
    Deebs-DeFi-j9fRbzDaily Liquidity Fees v. Block Rewards (FORKED FROM and CREDIT TO Polaris_9R)
    Updated 2025-03-19
    WITH fees AS (
    SELECT
    BLOCK_TIMESTAMP::DATE AS DATE,
    SUM(LIQ_FEE_RUNE_USD) AS LIQUIDITY_FEES_USD
    FROM thorchain.defi.fact_swaps
    WHERE BLOCK_TIMESTAMP >= {{start_date}}::DATE
    AND BLOCK_TIMESTAMP < {{end_date}}::DATE + INTERVAL '1 DAY'
    GROUP BY 1
    ), rewards AS (
    SELECT
    BLOCK_TIMESTAMP::DATE AS DATE,
    SUM(RUNE_AMOUNT_USD) AS BLOCK_REWARDS_USD
    FROM thorchain.defi.fact_total_block_rewards
    WHERE BLOCK_TIMESTAMP >= {{start_date}}::DATE
    AND BLOCK_TIMESTAMP < {{end_date}}::DATE + INTERVAL '1 DAY'
    GROUP BY 1
    )
    SELECT
    COALESCE(f.DATE, r.DATE) AS DATE,
    COALESCE(LIQUIDITY_FEES_USD, 0) AS LIQUIDITY_FEES_USD,
    COALESCE(BLOCK_REWARDS_USD, 0) AS BLOCK_REWARDS_USD,
    COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0) AS FEES_PLUS_REWARDS,
    CASE
    WHEN COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0) = 0 THEN 0
    ELSE COALESCE(LIQUIDITY_FEES_USD, 0)/(COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0))
    END * 100 AS PCT_LIQUIDTY_FEES,
    0.5 AS HALF_LINE
    FROM fees AS f
    FULL OUTER JOIN rewards AS r
    ON f.date = r.date


    Last run: 26 days ago
    DATE
    LIQUIDITY_FEES_USD
    BLOCK_REWARDS_USD
    FEES_PLUS_REWARDS
    PCT_LIQUIDTY_FEES
    HALF_LINE
    1
    		2023-08-04 00:00:00.00018442.90073475327308.124740145751.02547485340.3114477620.5
    2
    		2023-12-01 00:00:00.00045745.550130511180127.496671209225873.0468017220.2527706510.5
    3
    		2023-07-08 00:00:00.00051721.11242906230342.70773576782063.82016482963.025474960.5
    4
    		2023-08-27 00:00:00.00021789.96550341142106.46734617763896.43284958734.1020062180.5
    5
    		2023-08-31 00:00:00.00026248.16286690343762.04500673470010.20787363637.49190820.5
    6
    		2024-01-03 00:00:00.000122737.65101813153925.663018465276663.31403659544.3635439870.5
    7
    		2023-10-01 00:00:00.000158333.66089671558308.628080331216642.28897704673.0852972630.5
    8
    		2023-09-27 00:00:00.00052931.52548037952026.949136323104958.47461670250.4309210610.5
    9
    		2023-10-15 00:00:00.00014739.71469267647375.58451360262115.29920627823.729604270.5
    10
    		2023-07-27 00:00:00.00043070.22384931128278.98873525471349.21258456560.3653807650.5
    11
    		2023-09-12 00:00:00.00010365.06459274443663.84561507854028.91020782219.1842932850.5
    12
    		2023-07-02 00:00:00.00030545.56884369731648.86152791362194.4303716149.1130293520.5
    13
    		2024-01-01 00:00:00.00030872.931230862143456.930330972174329.86156183417.709491050.5
    14
    		2023-08-01 00:00:00.00017570.03737240227369.22472244744939.26209484939.0972983390.5
    15
    		2023-08-05 00:00:00.00014812.3776254127150.61416852541962.99179393535.2986691180.5
    16
    		2023-08-06 00:00:00.00058603.52261341527375.9120566185979.43467002568.1599301490.5
    17
    		2023-10-16 00:00:00.00056255.572137447513.106555829103768.67869322954.2124780290.5
    18
    		2023-08-20 00:00:00.00055604.77753716951978.541172071107583.31870923951.6853153480.5
    19
    		2024-01-09 00:00:00.00068856.666927104131187.34312532200044.01005242534.4207591660.5
    20
    		2023-12-02 00:00:00.00086424.876103624191064.536982434277489.41308605831.1452877220.5
    ...
    194
    18KB
    8s