-- Token Trends Analysis for Chart Comparison
SELECT 
    createdat::date as date,
    
    -- Daily token metrics
    COUNT(*) as daily_tokens_issued,
    COUNT(DISTINCT issuer) as daily_unique_issuers,
    SUM(holders) as daily_new_token_holders,
    
    -- Cumulative token metrics
    SUM(COUNT(*)) OVER (ORDER BY createdat::date) as cumulative_tokens_issued,
    SUM(SUM(holders)) OVER (ORDER BY createdat::date) as cumulative_token_holders,
    
    -- Token quality metrics
    COUNT(CASE WHEN meta_token_trust_level >= 3 THEN 1 END) as daily_high_trust_tokens,
    COUNT(CASE WHEN amms > 0 THEN 1 END) as daily_tokens_with_amm,
    COUNT(CASE WHEN blackholed = 'true' THEN 1 END) as daily_blackholed_tokens,
    
    -- Average metrics for the day
    ROUND(AVG(holders), 0) as avg_holders_per_token,
    ROUND(AVG(COALESCE(meta_token_trust_level, 0)), 2) as avg_trust_level,
    
    -- 7-day rolling averages for smoothed trends
    ROUND(AVG(COUNT(*)) OVER (
        ORDER BY createdat::date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 1) as tokens_issued_7d_avg,
    
    ROUND(AVG(COUNT(DISTINCT issuer)) OVER (
        ORDER BY createdat::date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 1) as unique_issuers_7d_avg

FROM @xrpscan.xrp_ledger.tokens
WHERE createdat >= '2025-01-01'  -- Focus on recent activity
  AND createdat IS NOT NULL
GROUP BY createdat::date
ORDER BY createdat::date ASC;