BSC

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

-- Analyze BSC fee and revenue metrics
SELECT
    date,
    chain_fees,
    fees,
    burned_fee_allocation,
    burned_fee_allocation / fees * 100 as burn_percentage,
    chain_txns,
    chain_fees / chain_txns as fee_per_transaction,
    burned_fee_allocation / chain_txns as burn_per_transaction
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze user growth and retention on BSC
SELECT
    date,
    chain_dau,
    new_users,
    returning_users,
    new_users / NULLIF(chain_dau, 0) * 100 as new_user_percentage,
    returning_users / NULLIF(chain_dau, 0) * 100 as returning_user_percentage,
    LAG(chain_dau, 7) OVER (ORDER BY date) as dau_week_ago,
    chain_dau / NULLIF(dau_week_ago, 0) - 1 as weekly_dau_growth
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze different user types on BSC
SELECT
    date,
    chain_dau,
    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,
    high_sleep_users / NULLIF(chain_dau, 0) * 100 as high_sleep_percentage,
    dau_over_100_balance / NULLIF(chain_dau, 0) * 100 as wealthy_user_percentage
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze trading volumes on BSC
SELECT
    date,
    chain_spot_volume,
    chain_nft_trading_volume,
    chain_spot_volume / NULLIF(chain_nft_trading_volume, 0) as dex_to_nft_ratio,
    LAG(chain_spot_volume, 7) OVER (ORDER BY date) as dex_volume_week_ago,
    chain_spot_volume / NULLIF(dex_volume_week_ago, 0) - 1 as weekly_dex_volume_growth,
    tvl,
    chain_spot_volume / NULLIF(tvl, 0) as capital_efficiency_ratio
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY
    date ASC

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

-- Track developer activity on BSC
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,
    weekly_commits_core_ecosystem / NULLIF(weekly_developers_core_ecosystem, 0) as avg_commits_per_core_dev
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze relationship between BSC usage and BNB price
SELECT
    date,
    chain_dau,
    chain_txns,
    price,
    market_cap,
    tvl,
    market_cap / NULLIF(tvl, 0) as mcap_to_tvl_ratio,
    price / NULLIF(LAG(price, 30) OVER (ORDER BY date), 0) - 1 as monthly_price_change,
    chain_dau / NULLIF(LAG(chain_dau, 30) OVER (ORDER BY date), 0) - 1 as monthly_dau_change
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze fee efficiency on BSC
SELECT
    date,
    chain_txns,
    chain_fees,
    chain_dau,
    tvl,
    chain_fees / NULLIF(chain_txns, 0) as fees_per_transaction,
    chain_fees / NULLIF(chain_dau, 0) as fees_per_user,
    chain_fees / NULLIF(tvl, 0) * 365 * 100 as annualized_fee_yield,
    burned_fee_allocation / NULLIF(market_cap, 0) * 365 * 100 as annualized_burn_rate
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

-- Analyze metrics for specific applications on BSC
SELECT
    date,
    app,
    friendly_name,
    category,
    txns,
    dau,
    returning_users,
    new_users
FROM
    art_share.bsc.ez_metrics_by_application_v2
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND app IN ('pancakeswap', 'venus', 'biswap')
ORDER BY
    app, date ASC

-- Create a monthly performance dashboard for BSC
SELECT
    DATE_TRUNC('month', date) as month,
    AVG(chain_dau) as avg_daily_users,
    SUM(chain_txns) as total_transactions,
    SUM(chain_fees) as total_fees,
    SUM(burned_fee_allocation) as total_burned,
    AVG(chain_avg_txn_fee) as avg_transaction_fee,
    AVG(tvl) as avg_tvl,
    AVG(price) as avg_token_price,
    LAST_VALUE(market_cap) OVER (PARTITION BY DATE_TRUNC('month', date) ORDER BY date) as end_of_month_market_cap,
    LAST_VALUE(stablecoin_total_supply) OVER (PARTITION BY DATE_TRUNC('month', date) ORDER BY date) as end_of_month_stablecoin_supply
FROM
    art_share.bsc.ez_metrics
WHERE
    date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY
    DATE_TRUNC('month', date)
ORDER BY
    month ASC