Sammy query

Updated 2025-02-07

WITH get_query AS (

SELECT

value[0]::timestamp_ntz AS week,

value[4]::real AS weekly_affiliate_fees

FROM (

SELECT

livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/7b114a42-14ef-46d5-837f-cc2dbd3ca0a3/latest-run') as response

), lateral FLATTEN (input => response:data:result:rows)

fees AS (

SELECT

DATE_TRUNC('week', day) AS week,

CASE

WHEN week < '2024-07-26' THEN 'Pre-5bps Min'

WHEN week >= '2024-07-26' AND week <= '2024-08-15' THEN '5bps min'

WHEN week > '2024-08-15' AND week <= '2024-10-15' THEN '15bps min'

ELSE '8bps Min'

END AS "Min Fee",

SUM(liquidity_fees_usd) AS liquidity_fees_usd,

SUM(block_rewards_usd) AS block_rewards_usd,

SUM(earnings_to_nodes_usd) AS earnings_to_nodes_usd,

SUM(earnings_to_pools_usd) AS earnings_to_pools_usd,

FROM thorchain.defi.fact_daily_earnings

WHERE day >= '2024-01-01'

GROUP BY week

)

SELECT

DISTINCT f.week,

f."Min Fee",

ROUND(f.liquidity_fees_usd) AS liquidity_fees_usd,

ROUND(f.block_rewards_usd) AS block_rewards_usd,

ROUND(f.earnings_to_nodes_usd) AS earnings_to_nodes_usd,

ROUND(f.earnings_to_pools_usd) AS earnings_to_pools_usd,

gq.weekly_affiliate_fees,

ROUND(f.liquidity_fees_usd + f.block_rewards_usd) AS "Protocol Earnings",

ROUND(f.liquidity_fees_usd + f.block_rewards_usd + gq.weekly_affiliate_fees) AS "Ecosystem Revenue",

Last run: 3 months ago

	WEEK	Min Fee	LIQUIDITY_FEES_USD	BLOCK_REWARDS_USD	EARNINGS_TO_NODES_USD	EARNINGS_TO_POOLS_USD	WEEKLY_AFFILIATE_FEES	Protocol Earnings	Ecosystem Revenue	Earnings from Fees	Earnings from Block Rewards	FIFTY_PERCENT_LINE	Node Earnings from Fees	Node Earnings from Block Rewards
1	2025-02-03 00:00:00.000	8bps Min	506617	164183	382304	288495	61189	670799	731988	75.5	24.5	50	288733	93571
2	2025-01-27 00:00:00.000	8bps Min	1353011	346238	815043	884206	726190	1699249	2425439	79.6	20.4	50	648970	166073
3	2025-01-20 00:00:00.000	8bps Min	1453735	520213	623701	1350247	781152	1973948	2755100	73.6	26.4	50	459331	164370
4	2025-01-13 00:00:00.000	8bps Min	1068512	631553	517554	1182511	862429	1700065	2562494	62.9	37.1	50	325289	192265
5	2025-01-06 00:00:00.000	8bps Min	849160	682241	443032	1088369	484104	1531401	2015505	55.4	44.6	50	245661	197371
6	2024-12-30 00:00:00.000	8bps Min	379296	835832	337649	877479	325463	1215128	1540591	31.2	68.8	50	105396	232254
7	2024-12-23 00:00:00.000	8bps Min	426060	842175	346295	921940	356278	1268235	1624513	33.6	66.4	50	116337	229958
8	2024-12-16 00:00:00.000	8bps Min	948480	961678	489411	1420747	694208	1910158	2604366	49.7	50.3	50	243015	246396
9	2024-12-09 00:00:00.000	8bps Min	841267	1069464	468750	1441981	625800	1910732	2536532	44	56	50	206384	262366
10	2024-12-02 00:00:00.000	8bps Min	1281481	1246267	604501	1923247	934221	2527747	3461968	50.7	49.3	50	306461	298040
11	2024-11-25 00:00:00.000	8bps Min	856798	1030542	478953	1408386	773914	1887339	2661253	45.4	54.6	50	217431	261522
12	2024-11-18 00:00:00.000	8bps Min	1126790	967895	542889	1551796	1060431	2094685	3155116	53.8	46.2	50	292035	250854
13	2024-11-11 00:00:00.000	8bps Min	1484038	1028375	611318	1901095	1208170	2512413	3720583	59.1	40.9	50	361095	250223
14	2024-11-04 00:00:00.000	8bps Min	988336	959966	453811	1494491	723139	1948302	2671441	50.7	49.3	50	230209	223601
15	2024-10-28 00:00:00.000	8bps Min	726329	1017731	774949	969111	454340	1744060	2198400	41.6	58.4	50	322734	452214
16	2024-10-21 00:00:00.000	8bps Min	495127	940782	800051	635858	252312	1435909	1688221	34.5	65.5	50	275872	524179
17	2024-10-14 00:00:00.000	15bps min	682564	927635	934322	675877	288022	1610199	1898221	42.4	57.6	50	396059	538263
18	2024-10-07 00:00:00.000	15bps min	550701	899149	829603	620247	177539	1449850	1627389	38	62	50	315111	514493
19	2024-09-30 00:00:00.000	15bps min	512225	885605	808269	589561	180424	1397830	1578254	36.6	63.4	50	296184	512084
20	2024-09-23 00:00:00.000	15bps min	917904	958676	1055858	820722	218518	1876580	2095098	48.9	51.1	50	516459	539399

7KB