1WITH stablecoin_metrics AS (
2    SELECT 
3        CASE 
4            WHEN code = 'RLUSD' AND meta_issuer_name = 'Ripple' THEN 'RLUSD (Ripple)'
5            WHEN code = 'USD' AND meta_issuer_name = 'GateHub' THEN 'USD (GateHub)'
6            WHEN code = 'USD' AND meta_issuer_name = 'Bitstamp' THEN 'USD (Bitstamp)'
7            WHEN code = 'USDC' AND meta_issuer_name = 'Circle' THEN 'USDC (Circle)'
8            WHEN code = 'USDT' THEN 'USDT (Tether)'
9            ELSE CONCAT(code, ' (', COALESCE(meta_issuer_name, 'Unknown'), ')')
10        END as stablecoin_name,
11        holders,
12        trustlines,
13        supply,
14        marketcap,
15        price,
16        createdat,
17        -- Stability metrics (how close to $1 equivalent)
18        ABS(price - 0.32) as price_deviation_from_peg,  -- Assuming ~0.32 XRP = $1
19        CASE 
20            WHEN price BETWEEN 0.30 AND 0.34 THEN 'Stable'
21            WHEN price BETWEEN 0.25 AND 0.40 THEN 'Moderate Deviation'
22            ELSE 'High Deviation'
23        END as peg_stability
24    FROM @xrpscan.xrp_ledger.tokens
25    WHERE (code LIKE '%USD%' OR code = 'RLUSD')
26      AND holders > 1000  -- Filter for significant tokens
27)
28
29SELECT 
30    stablecoin_name,
31    holders,
32    ROUND(supply, 0) as supply,
33    ROUND(marketcap, 0) as market_cap,
34    ROUND(price, 6) as price_in_xrp,
35    peg_stability,
36    ROUND(price_deviation_from_peg, 6) as deviation_from_usd_peg,
37    -- Market share calculations
38    ROUND(holders * 100.0 / SUM(holders) OVER(), 2) as holder_market_share_pct,
39    ROUND(supply * 100.0 / SUM(supply) OVER(), 2) as supply_market_share_pct,
40    createdat as created_date,
41    CASE 
42        WHEN stablecoin_name LIKE '%RLUSD%' THEN 'Ripple Official Stablecoin'
43        WHEN stablecoin_name LIKE '%GateHub%' THEN 'Gateway Provider'
44        WHEN stablecoin_name LIKE '%Bitstamp%' THEN 'Exchange Issued'
45        WHEN stablecoin_name LIKE '%Circle%' THEN 'Major Stablecoin Provider'
46        ELSE 'Other Provider'
47    END as issuer_category
48FROM stablecoin_metrics
49ORDER BY holders DESC;