picasoAsset in Stellar By Liquidity_Pools
Updated 2025-05-06
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
›
⌄
WITH cte AS (
SELECT
Asset,
COUNT(DISTINCT LIQUIDITY_POOL_ID) AS LPs
FROM (
SELECT
CASE
WHEN ASSET_A_TYPE = 'native' THEN 'XLM'
WHEN ASSET_A_CODE IS NOT NULL THEN ASSET_A_CODE
END AS Asset,
LIQUIDITY_POOL_ID
FROM stellar.defi.fact_liquidity_pools
UNION ALL
SELECT
CASE
WHEN ASSET_B_TYPE = 'native' THEN 'XLM'
WHEN ASSET_B_CODE IS NOT NULL THEN ASSET_B_CODE
END AS Asset,
LIQUIDITY_POOL_ID
FROM stellar.defi.fact_liquidity_pools
)
WHERE Asset IS NOT NULL
GROUP BY Asset
)
SELECT
CASE
WHEN LPs = 1 THEN 'a) 1 LP'
WHEN LPs < 6 THEN 'b) 2-5 LPs'
WHEN LPs < 11 THEN 'c) 6-10 LPs'
WHEN LPs < 21 THEN 'd) 11-20 LPs'
ELSE 'e) >21 LPs'
END AS LPs_Amount,
COUNT(DISTINCT Asset) AS Asset,
SUM(LPs) AS Liquidity_Pools
FROM cte
QueryRunArchived: QueryRun has been archived