Acala

-- Pull fundamental network activity data for Acala
SELECT
    date,
    chain_txns,
    chain_dau,
    chain_fees,
    chain_avg_txn_fee,
    fees,
    burned_fee_allocation,
    price
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze Acala's token burning mechanism
SELECT
    date,
    fees,
    burned_fee_allocation,
    burned_fee_allocation_native,
    chain_txns,
    burned_fee_allocation / NULLIF(fees, 0) * 100 as burn_percentage,
    burned_fee_allocation / NULLIF(chain_txns, 0) as burned_per_txn,
    burned_fee_allocation_native / NULLIF(chain_txns, 0) as burned_aca_per_txn
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
    AND fees > 0
ORDER BY
    date ASC

-- Track Acala user growth and engagement patterns
SELECT
    date,
    chain_dau,
    chain_wau,
    chain_mau,
    chain_txns,
    chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
    chain_dau / NULLIF(chain_wau, 0) * 100 as daily_weekly_ratio,
    chain_wau / NULLIF(chain_mau, 0) * 100 as weekly_monthly_ratio,
    LAG(chain_dau, 7) OVER (ORDER BY date) as dau_7d_ago,
    (chain_dau - LAG(chain_dau, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_dau, 7) OVER (ORDER BY date), 0) * 100 as dau_growth_7d
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze Acala's fee structure and economic model
SELECT
    date,
    fees,
    burned_fee_allocation,
    fees - burned_fee_allocation as retained_revenue,
    chain_avg_txn_fee,
    chain_txns,
    price,
    burned_fee_allocation / NULLIF(fees, 0) * 100 as burn_rate,
    (fees - burned_fee_allocation) / NULLIF(fees, 0) * 100 as retention_rate,
    fees / NULLIF(chain_dau, 0) as revenue_per_user
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND fees > 0
ORDER BY
    date ASC

-- Analyze ACA token market performance vs network activity
SELECT
    date,
    price,
    market_cap,
    fdmc,
    chain_dau,
    chain_txns,
    fees,
    burned_fee_allocation_native,
    market_cap / NULLIF(fees, 0) as mcap_to_revenue_ratio,
    burned_fee_allocation_native * price as burned_usd_value,
    fees / NULLIF(chain_dau, 0) as revenue_per_user
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Track Acala network growth and adoption trends
SELECT
    date,
    chain_dau,
    chain_txns,
    fees,
    burned_fee_allocation,
    -- 7-day growth rates
    LAG(chain_dau, 7) OVER (ORDER BY date) as dau_7d_ago,
    LAG(chain_txns, 7) OVER (ORDER BY date) as txns_7d_ago,
    LAG(fees, 7) OVER (ORDER BY date) as revenue_7d_ago,
    (chain_dau - LAG(chain_dau, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_dau, 7) OVER (ORDER BY date), 0) * 100 as dau_growth_7d,
    (chain_txns - LAG(chain_txns, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_txns, 7) OVER (ORDER BY date), 0) * 100 as txns_growth_7d,
    (fees - LAG(fees, 7) OVER (ORDER BY date)) / NULLIF(LAG(fees, 7) OVER (ORDER BY date), 0) * 100 as revenue_growth_7d
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze the impact of token burning on supply and market
SELECT
    date,
    burned_fee_allocation_native,
    burned_fee_allocation,
    price,
    market_cap,
    chain_txns,
    -- Cumulative burns
    SUM(burned_fee_allocation_native) OVER (ORDER BY date) as cumulative_burned_aca,
    SUM(burned_fee_allocation) OVER (ORDER BY date) as cumulative_burned_usd,
    -- Moving averages
    AVG(burned_fee_allocation_native) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_daily_burn_7d,
    AVG(burned_fee_allocation) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_daily_burn_30d
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Comprehensive daily performance summary for Acala
SELECT
    date,
    chain_dau,
    chain_txns,
    fees,
    burned_fee_allocation,
    chain_avg_txn_fee,
    price,
    market_cap,
    -- Efficiency metrics
    chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
    fees / NULLIF(chain_txns, 0) as revenue_per_txn,
    burned_fee_allocation / NULLIF(fees, 0) * 100 as burn_percentage,
    fees / NULLIF(chain_dau, 0) as revenue_per_user
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(week, -4, CURRENT_DATE())
ORDER BY
    date DESC

-- Analyze how fees are distributed between burning and retention
SELECT
    date,
    fees,
    burned_fee_allocation,
    fees - burned_fee_allocation as retained_fees,
    chain_txns,
    burned_fee_allocation / NULLIF(fees, 0) * 100 as burn_rate,
    (fees - burned_fee_allocation) / NULLIF(fees, 0) * 100 as retention_rate,
    burned_fee_allocation / NULLIF(chain_txns, 0) as burn_per_txn,
    (fees - burned_fee_allocation) / NULLIF(chain_txns, 0) as retained_per_txn
FROM
    art_share.acala.ez_metrics
WHERE
    date >= DATEADD(month, -1, CURRENT_DATE())
    AND fees > 0
ORDER BY
    date DESC

-- Monthly summary of Acala network performance
WITH monthly_summary AS (
    SELECT
        DATE_TRUNC('month', date) as month,
        AVG(chain_dau) as avg_dau,
        SUM(chain_txns) as total_txns,
        SUM(fees) as total_revenue,
        SUM(burned_fee_allocation) as total_burned,
        SUM(burned_fee_allocation_native) as total_burned_aca,
        AVG(price) as avg_price,
        AVG(market_cap) as avg_market_cap
    FROM
        art_share.acala.ez_metrics
    WHERE
        date >= DATEADD(month, -6, CURRENT_DATE())
    GROUP BY
        DATE_TRUNC('month', date)
)
SELECT
    month,
    avg_dau,
    total_txns,
    total_revenue,
    total_burned,
    total_burned_aca,
    avg_price,
    avg_market_cap,
    total_burned / NULLIF(total_revenue, 0) * 100 as monthly_burn_rate,
    total_revenue / NULLIF(total_txns, 0) as avg_revenue_per_txn,
    total_txns / NULLIF(avg_dau, 0) as avg_txns_per_user_per_day
FROM
    monthly_summary
ORDER BY
    month DESC