Aevo

-- Pull fundamental perpetual trading data for Aevo
SELECT
    date,
    perp_volume,
    price,
    market_cap,
    token_volume
FROM
    art_share.aevo.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,
    (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_growth_1d
FROM
    art_share.aevo.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -1, 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,
    COUNT(CASE WHEN perp_volume > 0 THEN 1 END) as active_days,
    STDDEV(perp_volume) as volume_volatility
FROM
    art_share.aevo.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY
    chain
ORDER BY
    total_volume DESC

-- Analyze AEVO token market performance vs perpetual trading activity
SELECT
    date,
    perp_volume,
    price,
    market_cap,
    token_volume,
    token_turnover_circulating,
    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
FROM
    art_share.aevo.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Track Aevo perpetual trading volume trends and growth
SELECT
    date,
    perp_volume,
    -- 7-day moving average
    AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_volume_7d,
    -- 30-day moving average
    AVG(perp_volume) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_volume_30d,
    -- 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
FROM
    art_share.aevo.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze which chains dominate Aevo's perpetual trading volume
WITH daily_totals AS (
    SELECT
        date,
        SUM(perp_volume) as total_daily_volume
    FROM
        art_share.aevo.ez_metrics_by_chain
    WHERE
        date >= DATEADD(month, -1, 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.aevo.ez_metrics_by_chain ebc
    JOIN
        daily_totals dt ON ebc.date = dt.date
    WHERE
        ebc.date >= DATEADD(month, -1, CURRENT_DATE())
)
SELECT
    chain,
    AVG(daily_share_percentage) as avg_share_percentage,
    SUM(perp_volume) as total_volume,
    COUNT(*) as active_days,
    MAX(daily_share_percentage) as peak_share_percentage
FROM
    chain_shares
GROUP BY
    chain
ORDER BY
    avg_share_percentage DESC

-- Analyze AEVO token market metrics and liquidity
SELECT
    date,
    price,
    market_cap,
    fdmc,
    token_volume,
    token_turnover_circulating,
    token_turnover_fdv,
    perp_volume,
    -- Token metrics
    market_cap / NULLIF(fdmc, 0) * 100 as circulating_percentage,
    token_volume / NULLIF(market_cap, 0) * 100 as daily_volume_to_mcap_ratio,
    -- Correlation with trading activity
    perp_volume / NULLIF(token_volume, 0) as perp_to_token_volume_ratio
FROM
    art_share.aevo.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Weekly aggregated performance summary for Aevo
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,
    AVG(price) as avg_token_price,
    SUM(token_volume) as total_token_volume,
    AVG(market_cap) as avg_market_cap
FROM
    art_share.aevo.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY
    DATE_TRUNC('week', date)
ORDER BY
    week DESC

-- Identify and analyze high volume trading days
WITH volume_stats AS (
    SELECT
        AVG(perp_volume) as avg_volume,
        STDDEV(perp_volume) as stddev_volume
    FROM
        art_share.aevo.ez_metrics
    WHERE
        date >= DATEADD(month, -3, CURRENT_DATE())
)
SELECT
    date,
    perp_volume,
    price,
    token_volume,
    vs.avg_volume,
    (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.avg_volume + 2 * vs.stddev_volume THEN 'Extremely 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.aevo.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

-- Compare growth rates across different chains
WITH chain_growth 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
    FROM
        art_share.aevo.ez_metrics_by_chain
    WHERE
        date >= DATEADD(month, -2, CURRENT_DATE())
)
SELECT
    date,
    chain,
    perp_volume,
    volume_7d_ago,
    volume_30d_ago,
    (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
FROM
    chain_growth
WHERE
    date >= DATEADD(month, -1, CURRENT_DATE())
    AND volume_7d_ago IS NOT NULL
ORDER BY
    date DESC, perp_volume DESC