1-- Token Trends Analysis for Chart Comparison
2SELECT 
3    createdat::date as date,
4    
5    -- Daily token metrics
6    COUNT(*) as daily_tokens_issued,
7    COUNT(DISTINCT issuer) as daily_unique_issuers,
8    SUM(holders) as daily_new_token_holders,
9    
10    -- Cumulative token metrics
11    SUM(COUNT(*)) OVER (ORDER BY createdat::date) as cumulative_tokens_issued,
12    SUM(SUM(holders)) OVER (ORDER BY createdat::date) as cumulative_token_holders,
13    
14    -- Token quality metrics
15    COUNT(CASE WHEN meta_token_trust_level >= 3 THEN 1 END) as daily_high_trust_tokens,
16    COUNT(CASE WHEN amms > 0 THEN 1 END) as daily_tokens_with_amm,
17    COUNT(CASE WHEN blackholed = 'true' THEN 1 END) as daily_blackholed_tokens,
18    
19    -- Average metrics for the day
20    ROUND(AVG(holders), 0) as avg_holders_per_token,
21    ROUND(AVG(COALESCE(meta_token_trust_level, 0)), 2) as avg_trust_level,
22    
23    -- 7-day rolling averages for smoothed trends
24    ROUND(AVG(COUNT(*)) OVER (
25        ORDER BY createdat::date 
26        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
27    ), 1) as tokens_issued_7d_avg,
28    
29    ROUND(AVG(COUNT(DISTINCT issuer)) OVER (
30        ORDER BY createdat::date 
31        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
32    ), 1) as unique_issuers_7d_avg
33
34FROM @xrpscan.xrp_ledger.tokens
35WHERE createdat >= '2025-01-01'  -- Focus on recent activity
36  AND createdat IS NOT NULL
37GROUP BY createdat::date
38ORDER BY createdat::date ASC;