SandeshWhere has the token been used ?
    Updated 5 days ago
    /*
    Query analyzes contract usage patterns on Ethereum over the past month. It tracks:
    - Daily interaction counts with specific contracts
    - Special handling for delegate calls
    - Contract categorization by type and name
    - Integration with contract and label dimensional data

    Key features:
    - Uses CTEs for modular organization
    - Handles delegate transactions separately
    - Categorizes contracts using detailed CASE statements
    - Joins with dimensional tables for contract metadata
    */

    -- First CTE: Captures total contract usage metrics
    -- Combines direct contract interactions and delegate calls
    WITH total_usage AS (
    -- Subquery 1: Track general contract interactions
    SELECT
    block_timestamp::date AS transaction_date,
    from_address AS interacting_contract,
    COUNT(DISTINCT tx_hash) AS daily_interactions
    FROM ethereum.core.fact_traces ft
    WHERE 1=1
    -- Filter for recent transactions within the last month
    AND block_timestamp >= current_date - INTERVAL '1 month'
    -- Target specific contract address
    AND to_address = '0xacd2c239012d17beb128b0944d49015104113650'
    -- Ensure we're only looking at verified contracts
    AND interacting_contract IN (
    SELECT address
    FROM ethereum.core.dim_contracts
    )
    GROUP BY transaction_date, interacting_contract

    UNION ALL
    Last run: 5 days ago
    TRANSACTION_DATE
    INTERACTING_CONTRACT
    DAILY_INTERACTIONS
    CONTRACT_NAME
    CONTRACT_TYPE
    1
    2024-12-19 00:00:00.0000xb28ca7e465c452ce4252598e0bc96aeba553cf823otherother
    2
    2024-12-13 00:00:00.0000x6352a56caadc4f1e25cd6c75970fa768a3304e643otherother
    3
    2024-12-24 00:00:00.0000x4d5eb35e67e1be01e639c8944973031fa5b28ba11otherother
    4
    2024-12-11 00:00:00.0000x1d94bedcb3641ba060091ed090d28bbdccdb7f1d1otherother
    5
    2024-12-24 00:00:00.0000x1d94bedcb3641ba060091ed090d28bbdccdb7f1d1otherother
    6
    2024-12-14 00:00:00.0000x0500001fde3800757d9fc800003d10b8004a11ac1otherother
    7
    2024-12-10 00:00:00.0000x0500001fde3800757d9fc800003d10b8004a11ac1otherother
    8
    2024-12-15 00:00:00.0000x99b1817acb40e76c309e26b2face9da9eff553174otherother
    9
    2024-12-27 00:00:00.0000x73a8a6f5d9762ea5f1de193ec19cdf476c7e86b13MEV BotMEV Bot
    10
    2024-12-10 00:00:00.0000xa7ca2c8673bcfa5a26d8ceec2887f2cc2b0db22a1otherother
    11
    2024-12-17 00:00:00.0000x347723562ba58ec75702ccd73ec91ce15ce92d161otherother
    12
    2024-12-19 00:00:00.0000x97e6567c3b63d2e0ec4ad0b96b356c675da1ab7e1otherother
    13
    2024-12-24 00:00:00.0000x16a293d9d75f56dcafaf47c44fb73539762307149otherother
    14
    2024-12-17 00:00:00.0000x99b1817acb40e76c309e26b2face9da9eff553174otherother
    15
    2024-12-05 00:00:00.0000x4d9cdb3f367a93ef942f1564fee5e58d2b68220e1otherother
    16
    2025-01-04 00:00:00.0000x3c11f6265ddec22f4d049dde480615735f4516461otherother
    17
    2025-01-02 00:00:00.0000x1d94bedcb3641ba060091ed090d28bbdccdb7f1d1otherother
    18
    2024-12-08 00:00:00.0000x1231deb6f5749ef6ce6943a275a1d3e7486f4eae3otherother
    19
    2024-12-08 00:00:00.0000x6a000f20005980200259b80c51020030400010682otherother
    20
    2024-12-28 00:00:00.0000x8421886b3ae96e679987713450b3421051c998a63otherother
    ...
    1310
    123KB
    34s