check_skedArbitrum Activity Share (Individual)
    Updated 2023-04-06
    WITH label_txs AS (
    SELECT
    label_type,
    COUNT(*) AS txs
    FROM arbitrum.core.fact_token_transfers
    LEFT OUTER JOIN arbitrum.core.dim_labels ON contract_address = address
    WHERE label_type IN ('dex', 'defi', 'dapp', 'nft', 'layer2', 'flotsam', 'cex', 'operator', 'chadmin')
    AND block_timestamp >= CURRENT_DATE - interval '7 day'
    GROUP BY label_type
    )
    SELECT
    txs_dex,
    ROUND(100 * txs_dex::NUMERIC / total_txs, 2) AS pct_share_dex,
    txs_nft,
    ROUND(100 * txs_nft::NUMERIC / total_txs, 2) AS pct_share_nft,
    txs_defi,
    ROUND(100 * txs_defi::NUMERIC / total_txs, 2) AS pct_share_defi,
    txs_dapp,
    ROUND(100 * txs_dapp::NUMERIC / total_txs, 2) AS pct_share_dapp,
    txs_layer2,
    ROUND(100 * txs_layer2::NUMERIC / total_txs, 2) AS pct_share_layer2,
    txs_flotsam,
    ROUND(100 * txs_flotsam::NUMERIC / total_txs, 2) AS pct_share_flotsam,
    txs_cex,
    ROUND(100 * txs_cex::NUMERIC / total_txs, 2) AS pct_share_cex,
    txs_operator,
    ROUND(100 * txs_operator::NUMERIC / total_txs, 2) AS pct_share_operator,
    txs_chadmin,
    ROUND(100 * txs_chadmin::NUMERIC / total_txs, 2) AS pct_share_chadmin
    FROM (
    SELECT
    COALESCE(SUM(CASE WHEN label_type = 'dex' THEN txs END), 0) AS txs_dex,
    COALESCE(SUM(CASE WHEN label_type = 'nft' THEN txs END), 0) AS txs_nft,
    COALESCE(SUM(CASE WHEN label_type = 'defi' THEN txs END), 0) AS txs_defi,
    COALESCE(SUM(CASE WHEN label_type = 'dapp' THEN txs END), 0) AS txs_dapp,
    COALESCE(SUM(CASE WHEN label_type = 'layer2' THEN txs END), 0) AS txs_layer2,
    Run a query to Download Data