Blockchain Data Analytics Templates — Dune Dashboards and On-Chain KPIs

-- Dune SQL: Track protocol TVL over time
-- Replace contract_address with your vault/pool address

WITH daily_balances AS (
    SELECT 
        DATE_TRUNC('day', block_time) AS day,
        SUM(CASE WHEN to = 0xYOUR_CONTRACT_ADDRESS 
            THEN value ELSE -value END) 
            / 1e6 AS usdc_delta  -- USDC has 6 decimals
    FROM erc20_ethereum.evt_Transfer
    WHERE (to = 0xYOUR_CONTRACT_ADDRESS 
           OR "from" = 0xYOUR_CONTRACT_ADDRESS)
      AND contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48  -- USDC
      AND block_time >= DATE '2024-01-01'
    GROUP BY 1
),
cumulative AS (
    SELECT 
        day,
        SUM(usdc_delta) OVER (ORDER BY day) AS tvl_usdc
    FROM daily_balances
)
SELECT 
    day,
    tvl_usdc,
    tvl_usdc * eth_price.price AS tvl_usd
FROM cumulative
LEFT JOIN prices.usd eth_price 
    ON eth_price.symbol = 'USDC'
    AND eth_price.minute = DATE_TRUNC('minute', cumulative.day)
ORDER BY day DESC

-- Count unique depositors per week
SELECT 
    DATE_TRUNC('week', block_time) AS week,
    COUNT(DISTINCT "from") AS new_depositors,
    COUNT(DISTINCT "from") OVER (ORDER BY DATE_TRUNC('week', block_time)) AS cumulative_users
FROM erc20_ethereum.evt_Transfer
WHERE to = 0xYOUR_CONTRACT_ADDRESS
  AND contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48
  AND block_time >= DATE '2024-01-01'
GROUP BY 1
ORDER BY 1 DESC

-- Track fee revenue collected by protocol treasury
WITH fee_events AS (
    SELECT 
        DATE_TRUNC('day', block_time) AS day,
        SUM(CAST(data AS DOUBLE) / 1e6) AS daily_fees_usdc
    FROM ethereum.logs
    WHERE contract_address = 0xYOUR_CONTRACT
      AND topic0 = 0xFEE_COLLECTED_EVENT_SIGNATURE
      AND block_time >= DATE '2024-01-01'
    GROUP BY 1
)
SELECT 
    day,
    daily_fees_usdc,
    SUM(daily_fees_usdc) OVER (ORDER BY day) AS cumulative_fees_usdc,
    daily_fees_usdc * 365 AS annualized_revenue_rate
FROM fee_events
ORDER BY day DESC