Apex

-- Pull fundamental perpetual trading data for Apex
SELECT
    date,
    perp_volume,
    price,
    market_cap,
    token_volume,
    perp_volume / NULLIF(token_volume, 0) as perp_to_token_volume_ratio
FROM
    art_share.apex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze perpetual trading activity across different chains
SELECT
    date,
    chain,
    perp_volume,
    LAG(perp_volume, 1) OVER (PARTITION BY chain ORDER BY date) as prev_day_volume,
    LAG(perp_volume, 7) OVER (PARTITION BY chain ORDER BY date) as volume_7d_ago,
    (perp_volume - LAG(perp_volume, 1) OVER (PARTITION BY chain ORDER BY date)) / NULLIF(LAG(perp_volume, 1) OVER (PARTITION BY chain ORDER BY date), 0) * 100 as volume_change_1d,
    (perp_volume - LAG(perp_volume, 7) OVER (PARTITION BY chain ORDER BY date)) / NULLIF(LAG(perp_volume, 7) OVER (PARTITION BY chain ORDER BY date), 0) * 100 as volume_growth_7d
FROM
    art_share.apex.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    chain, date ASC

-- Compare perpetual trading volumes across different chains
SELECT
    chain,
    SUM(perp_volume) as total_volume,
    AVG(perp_volume) as avg_daily_volume,
    MAX(perp_volume) as peak_daily_volume,
    MIN(perp_volume) as min_daily_volume,
    COUNT(CASE WHEN perp_volume > 0 THEN 1 END) as active_days,
    STDDEV(perp_volume) as volume_volatility,
    STDDEV(perp_volume) / NULLIF(AVG(perp_volume), 0) as coefficient_of_variation
FROM
    art_share.apex.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY
    chain
ORDER BY
    total_volume DESC

-- Analyze APEX token market performance vs perpetual trading activity
SELECT
    date,
    perp_volume,
    price,
    market_cap,
    token_volume,
    token_turnover_circulating,
    token_turnover_fdv,
    token_volume / NULLIF(perp_volume, 0) * 100 as token_vs_perp_volume_ratio,
    market_cap / NULLIF(perp_volume, 0) as mcap_to_trading_volume_ratio,
    token_turnover_circulating * 100 as daily_token_turnover_percentage,
    token_turnover_fdv * 100 as daily_fdv_turnover_percentage
FROM
    art_share.apex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Track Apex perpetual trading volume trends and growth patterns
SELECT
    date,
    perp_volume,
    -- Moving averages
    AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_volume_7d,
    AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_volume_30d,
    AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) as avg_volume_90d,
    -- Growth rates
    LAG(perp_volume, 7) OVER (ORDER BY date) as volume_7d_ago,
    LAG(perp_volume, 30) OVER (ORDER BY date) as volume_30d_ago,
    (perp_volume - LAG(perp_volume, 7) OVER (ORDER BY date)) / NULLIF(LAG(perp_volume, 7) OVER (ORDER BY date), 0) * 100 as volume_growth_7d,
    (perp_volume - LAG(perp_volume, 30) OVER (ORDER BY date)) / NULLIF(LAG(perp_volume, 30) OVER (ORDER BY date), 0) * 100 as volume_growth_30d,
    -- Trend analysis
    perp_volume / NULLIF(AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 0) as volume_vs_30d_avg_ratio
FROM
    art_share.apex.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze which chains dominate Apex's perpetual trading volume
WITH daily_totals AS (
    SELECT
        date,
        SUM(perp_volume) as total_daily_volume
    FROM
        art_share.apex.ez_metrics_by_chain
    WHERE
        date >= DATEADD(month, -2, CURRENT_DATE())
    GROUP BY
        date
),
chain_shares AS (
    SELECT
        ebc.date,
        ebc.chain,
        ebc.perp_volume,
        dt.total_daily_volume,
        ebc.perp_volume / NULLIF(dt.total_daily_volume, 0) * 100 as daily_share_percentage
    FROM
        art_share.apex.ez_metrics_by_chain ebc
    JOIN
        daily_totals dt ON ebc.date = dt.date
    WHERE
        ebc.date >= DATEADD(month, -2, CURRENT_DATE())
)
SELECT
    chain,
    AVG(daily_share_percentage) as avg_share_percentage,
    MAX(daily_share_percentage) as peak_share_percentage,
    MIN(daily_share_percentage) as min_share_percentage,
    SUM(perp_volume) as total_volume,
    COUNT(*) as active_days,
    STDDEV(daily_share_percentage) as share_volatility
FROM
    chain_shares
WHERE
    perp_volume > 0
GROUP BY
    chain
ORDER BY
    avg_share_percentage DESC

-- Analyze APEX token market metrics and trading patterns
SELECT
    date,
    price,
    market_cap,
    fdmc,
    token_volume,
    token_turnover_circulating,
    token_turnover_fdv,
    perp_volume,
    -- Token valuation metrics
    market_cap / NULLIF(fdmc, 0) * 100 as circulating_percentage,
    token_volume / NULLIF(market_cap, 0) * 100 as daily_volume_to_mcap_ratio,
    -- Protocol activity correlation
    perp_volume / NULLIF(token_volume, 0) as perp_to_token_volume_ratio,
    market_cap / NULLIF(perp_volume, 0) as mcap_per_dollar_traded
FROM
    art_share.apex.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND price > 0
ORDER BY
    date ASC

-- Identify and analyze high volume trading periods
WITH volume_stats AS (
    SELECT
        AVG(perp_volume) as avg_volume,
        STDDEV(perp_volume) as stddev_volume,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY perp_volume) as volume_95th_percentile
    FROM
        art_share.apex.ez_metrics
    WHERE
        date >= DATEADD(month, -3, CURRENT_DATE())
        AND perp_volume > 0
)
SELECT
    date,
    perp_volume,
    price,
    token_volume,
    vs.avg_volume,
    vs.volume_95th_percentile,
    (perp_volume - vs.avg_volume) / NULLIF(vs.stddev_volume, 0) as volume_z_score,
    perp_volume / NULLIF(vs.avg_volume, 0) as volume_multiple,
    CASE
        WHEN perp_volume > vs.volume_95th_percentile THEN 'Top 5%'
        WHEN perp_volume > vs.avg_volume + 2 * vs.stddev_volume THEN 'Very High'
        WHEN perp_volume > vs.avg_volume + vs.stddev_volume THEN 'High'
        WHEN perp_volume < vs.avg_volume - vs.stddev_volume THEN 'Low'
        ELSE 'Normal'
    END as volume_category
FROM
    art_share.apex.ez_metrics
CROSS JOIN
    volume_stats vs
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
    AND perp_volume > vs.avg_volume + vs.stddev_volume
ORDER BY
    perp_volume DESC

-- Weekly aggregated performance summary for Apex
SELECT
    DATE_TRUNC('week', date) as week,
    SUM(perp_volume) as total_weekly_volume,
    AVG(perp_volume) as avg_daily_volume,
    MAX(perp_volume) as peak_daily_volume,
    MIN(perp_volume) as min_daily_volume,
    AVG(price) as avg_token_price,
    SUM(token_volume) as total_token_volume,
    AVG(market_cap) as avg_market_cap,
    AVG(token_turnover_circulating) * 7 as estimated_weekly_turnover
FROM
    art_share.apex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY
    DATE_TRUNC('week', date)
ORDER BY
    week DESC

-- Compare performance and growth across different chains
WITH chain_metrics AS (
    SELECT
        chain,
        date,
        perp_volume,
        LAG(perp_volume, 7) OVER (PARTITION BY chain ORDER BY date) as volume_7d_ago,
        LAG(perp_volume, 30) OVER (PARTITION BY chain ORDER BY date) as volume_30d_ago,
        ROW_NUMBER() OVER (PARTITION BY chain ORDER BY perp_volume DESC) as volume_rank
    FROM
        art_share.apex.ez_metrics_by_chain
    WHERE
        date >= DATEADD(month, -2, CURRENT_DATE())
)
SELECT
    date,
    chain,
    perp_volume,
    volume_7d_ago,
    volume_30d_ago,
    volume_rank,
    (perp_volume - volume_7d_ago) / NULLIF(volume_7d_ago, 0) * 100 as growth_7d,
    (perp_volume - volume_30d_ago) / NULLIF(volume_30d_ago, 0) * 100 as growth_30d,
    CASE
        WHEN volume_rank = 1 THEN 'Peak Performance'
        WHEN volume_rank <= 5 THEN 'High Performance'
        ELSE 'Standard Performance'
    END as performance_tier
FROM
    chain_metrics
WHERE
    date >= DATEADD(month, -1, CURRENT_DATE())
    AND volume_7d_ago IS NOT NULL
ORDER BY
    date DESC, perp_volume DESC