Avalanche

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

-- Analyze Avalanche fee metrics
SELECT
    date,
    chain_fees,
    fees,
    burned_fee_allocation,
    chain_txns,
    chain_fees / chain_txns as fee_per_transaction,
    chain_avg_txn_fee,
    chain_median_txn_fee
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze Avalanche staking metrics
SELECT
    date,
    total_staked,
    total_staked_native,
    price,
    market_cap,
    total_staked / market_cap * 100 as staking_ratio,
    emissions_native,
    emissions_native / total_staked_native * 365 * 100 as annualized_yield
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze different user types on Avalanche
SELECT
    date,
    chain_dau,
    new_users,
    returning_users,
    sybil_users,
    non_sybil_users,
    low_sleep_users,
    high_sleep_users,
    dau_over_100_balance,
    non_sybil_users / NULLIF(chain_dau, 0) * 100 as non_sybil_percentage
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze different volume sources on Avalanche
SELECT
    date,
    settlement_volume,
    chain_spot_volume,
    chain_nft_trading_volume,
    p2p_transfer_volume,
    p2p_native_transfer_volume,
    p2p_token_transfer_volume,
    stablecoin_transfer_volume,
    chain_spot_volume / NULLIF(settlement_volume, 0) * 100 as dex_percentage
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY
    date ASC

-- Track stablecoin usage on Avalanche
SELECT
    date,
    stablecoin_total_supply,
    stablecoin_dau,
    artemis_stablecoin_dau,
    p2p_stablecoin_dau,
    stablecoin_transfer_volume,
    p2p_stablecoin_transfer_volume,
    non_p2p_stablecoin_transfer_volume,
    p2p_stablecoin_transfer_volume / NULLIF(stablecoin_transfer_volume, 0) * 100 as p2p_percentage
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Track bridge activity on Avalanche
SELECT
    date,
    bridge_volume,
    bridge_daa as bridge_dau,
    bridge_volume / NULLIF(bridge_daa, 0) as avg_bridge_volume_per_user,
    chain_dau,
    bridge_daa / NULLIF(chain_dau, 0) * 100 as bridging_user_percentage
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Track developer activity on Avalanche
SELECT
    date,
    weekly_contracts_deployed,
    weekly_contract_deployers,
    weekly_commits_core_ecosystem,
    weekly_commits_sub_ecosystem,
    weekly_developers_core_ecosystem,
    weekly_developers_sub_ecosystem,
    weekly_contracts_deployed / NULLIF(weekly_contract_deployers, 0) as avg_contracts_per_deployer
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze Avalanche tokenomics
SELECT
    date,
    emissions_native,
    burned_fee_allocation_native,
    emissions_native - burned_fee_allocation_native as net_daily_supply_change,
    price,
    (emissions_native - burned_fee_allocation_native) * price as net_daily_supply_change_usd,
    emissions_native / NULLIF(total_staked_native, 0) * 365 * 100 as annualized_emission_rate
FROM
    art_share.avalanche.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze metrics for specific applications on Avalanche
SELECT
    date,
    app,
    friendly_name,
    category,
    txns,
    dau,
    returning_users,
    new_users
FROM
    art_share.avalanche.ez_metrics_by_application_v2
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND app IN ('trader-joe', 'aave-v3', 'gmx')
ORDER BY
    app, date ASC

-- Analyze cross-chain flows to and from Avalanche
SELECT
    date,
    inflow,
    outflow,
    inflow - outflow as net_flow,
    SUM(inflow - outflow) OVER (ORDER BY date) as cumulative_net_flow
FROM
    art_share.avalanche.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC