excessive-yellow

defi__joshexcessive-yellow

Updated 2025-05-28

Title: Avalanche Liquidity Providers Analytics - Modified for Accurate LP Tracking

Description: Comprehensive analysis of LP user behavior, segmented by wallet tiers,

including metrics for user retention, churn, activity, volume, and platform-specific metrics

-- WITH lp_users AS (

-- SELECT

-- origin_from_address as provider,

-- DATE_TRUNC('month', block_timestamp) as lp_month,

-- COUNT(DISTINCT tx_hash) as lp_count,

-- SUM(CASE

-- WHEN event_name IN ('Mint', 'AddLiquidity') THEN amount_in_usd

-- ELSE 0

-- END) as total_liquidity_added_usd,

-- SUM(CASE

-- WHEN event_name IN ('Mint', 'AddLiquidity') THEN amount_in_usd

-- WHEN event_name IN ('Burn', 'RemoveLiquidity') THEN -amount_out_usd

-- ELSE 0

-- END) as net_liquidity_position_usd

-- FROM avalanche.defi.ez_dex_swaps s

-- JOIN avalanche.defi.dim_dex_liquidity_pools p

-- ON s.contract_address = p.pool_address

-- WHERE block_timestamp >= DATEADD('month', -12, CURRENT_DATE())

-- AND block_timestamp < CURRENT_DATE()

-- AND event_name IN ('Mint', 'AddLiquidity', 'Burn', 'RemoveLiquidity')

-- GROUP BY 1,2

-- HAVING lp_count >= 1

-- ),

-- pool_metrics AS (

-- SELECT

-- origin_from_address as provider,

-- DATE_TRUNC('month', block_timestamp) as lp_month,

-- COUNT(DISTINCT pool_address) as unique_pools,

-- COUNT(DISTINCT p.platform) as unique_platforms,

Last run: 29 days ago

	Liquidity Providers' Tier	Month	Active LPs	Total LP Actions	Total Liquidity Added (USD)	Net Liquidity Position (USD)	Avg Liquidity per LP (USD)	Retained LPs	Retention Rate %	Churned LPs	Churn Rate %	New LPs	Net LP Growth	Reactivated LPs	Activation Rate %	Reactivation Rate %	Avg Actions per LP	Cohort Retention %	Avg Pools per LP	Avg Platforms per LP	Avg Unique Tokens per LP	Avg TraderJoe Pools	Avg Pangolin Pools	Avg Pharaoh Pools	Avg Uniswap Pools	Avg Swaps in LP Pools	Avg Swap Volume in LP Pools (USD)
1	🐳 Whale	2025-05-01 00:00:00.000	1525	2894597	5705461880.29	5705461880.29	3741286.48	0	0	1525	100	1525	21	197	100		1898.1	0	24.01	2.95	11.79	13.72	3.41	3.79	2.16	570.46	11224736.18
2	🐳 Whale	2025-04-01 00:00:00.000	1504	2632633	8999144619.83	8999144619.83	5983473.82	1207	80.25	297	19.75	297	-291	105	16.55	8.7	1750.42	80.25	25.08	3	11.97	15.02	3.94	3.02	2.03	589.16	14362479.41
3	🐳 Whale	2025-03-01 00:00:00.000	1795	2908809	15142449754	15142449754	8435905.16	1321	73.59	474	26.41	474	-34	150	25.92	11.36	1620.51	73.59	25.35	3.2	12.45	15.53	3.61	3.18	2.01	419.4	10676263.58
4	🐳 Whale	2025-02-01 00:00:00.000	1829	2885654	12427147117.03	12427147117.03	6794503.62	1492	81.57	337	18.43	337	47	157	18.91	10.52	1577.72	81.57	26.7	3.16	13.21	16.64	3.87	3.27	2.05	437.42	12847875.33
5	🐳 Whale	2025-01-01 00:00:00.000	1782	2648254	14576003041.94	14576003041.94	8179575.22	1450	81.37	332	18.63	332	-64	105	17.98	7.24	1486.11	81.37	27.36	3.01	14.07	17.65	3.8	2.34	2.65	489.32	15575182.48
6	🐳 Whale	2024-12-01 00:00:00.000	1846	2340096	17472567070.23	17472567070.23	9465095.92	1457	78.93	389	21.07	389	71	144	21.92	9.88	1267.66	78.93	27.93	2.83	14.53	18.78	3.85	1.85	2.43	373.16	16229024.79
7	🐳 Whale	2024-11-01 00:00:00.000	1775	1869242	14937868731.15	14937868731.15	8415700.69	1497	84.34	278	15.66	278	241	186	18.12	12.42	1053.09	84.34	26.13	2.68	14.16	19.01	3.27	1.14	1.91	418.11	28412349.21
8	🐳 Whale	2024-10-01 00:00:00.000	1534	947454	8055143940.88	8055143940.88	5251071.67	1336	87.09	198	12.91	198	117	139	13.97	10.4	617.64	87.09	21.75	2.51	11.85	15.37	2.89	1.04	1.78	205.77	14363154.18
9	🐳 Whale	2024-09-01 00:00:00.000	1417	740698	7469616504.9	7469616504.9	5271430.14	1203	84.9	214	15.1	214	50	103	15.65	8.56	522.72	84.9	18.88	2.49	10.21	12.89	2.7	1.07	1.57	196.21	12978064.01
10	🐳 Whale	2024-08-01 00:00:00.000	1367	893341	7921608361.92	7921608361.92	5794885.41	1128	82.52	239	17.48	239	9	83	17.6	7.36	653.5	82.52	23.31	2.69	12.26	15.9	3.36	1.26	1.93	267.59	18199668.13
11	🐳 Whale	2024-07-01 00:00:00.000	1358	833991	6559746389.88	6559746389.88	4830446.53	1116	82.18	242	17.82	242	204	87	20.97	7.8	614.13	82.18	21.64	2.59	11.67	15.12	2.76	1.29	1.78	289.16	13386470.49
12	🐳 Whale	2024-06-01 00:00:00.000	1154	673066	3800379470.8	3800379470.8	3293223.11	970	84.06	184	15.94	184	0	0	15.94	0	583.25	84.06	21.64	2.48	11.84	14.88	3.05	0.96	1.92	191.52	5596776.3
13	🐳 Whale	2024-05-01 00:00:00.000	1154	864378	6245160491.34	6245160491.34	5411750.86	1154	100	0	0	0		0		0	749.03	100	22.81	2.45	12.32	15.78	3.25	0.92	2.05	210.64	6682570.27
14	🦈 Shark	2025-05-01 00:00:00.000	2697	205349	22003055.53	22003055.53	8158.34	0	0	2697	100	2697	-3	739	99.89		76.14	0	7.07	2.15	4.21	4.17	1.17	1.07	0.38	32.12	128428.17
15	🦈 Shark	2025-04-01 00:00:00.000	2700	240799	17422926.47	17422926.47	6452.94	1700	62.96	1000	37.04	1000	-1896	390	21.76	22.94	89.18	62.96	7.43	2.15	4.33	4.55	1.29	0.88	0.41	28.25	146738.04
16	🦈 Shark	2025-03-01 00:00:00.000	4596	256448	47149691.69	47149691.69	10258.85	2069	45.02	2527	54.98	2527	-155	717	53.19	34.65	55.8	45.02	7.06	2.24	4.52	4.8	0.8	0.95	0.31	28.47	218470.67
17	🦈 Shark	2025-02-01 00:00:00.000	4751	328192	44664857.26	44664857.26	9401.15	3194	67.23	1557	32.77	1557	59	911	33.18	28.52	69.08	67.23	7.13	2.3	4.46	4.73	0.87	1.03	0.32	32.02	272432.27
18	🦈 Shark	2025-01-01 00:00:00.000	4692	212190	37754637.06	37754637.06	8046.6	2992	63.77	1700	36.23	1700	-855	670	30.65	22.39	45.22	63.77	6.62	2.07	4.42	4.76	0.8	0.54	0.33	26.65	182095.81
19	🦈 Shark	2024-12-01 00:00:00.000	5547	146950	56498922.02	56498922.02	10185.49	3196	57.62	2351	42.38	2351	123	813	43.34	25.44	26.49	57.62	6.37	1.92	4.36	4.89	0.73	0.29	0.31	18.72	167470.27
20	🦈 Shark	2024-11-01 00:00:00.000	5424	107370	50449642.75	50449642.75	9301.19	3703	68.27	1721	31.73	1721	1213	1113	40.87	30.06	19.8	68.27	6.24	1.83	4.42	5.16	0.6	0.19	0.17	21.72	144298.99

16KB

52s

flipside

flipside