kaibladeLUNA Transaction Volume Data By Address Labels
    Updated 2022-10-07
    WITH luna_tx AS
    (SELECT DISTINCT(tx_hash) AS tx
    FROM ethereum.core.ez_token_transfers
    WHERE contract_address ='0xbd31ea8212119f94a611fa969881cba3ea06fa3d'
    AND block_timestamp::date >= CURRENT_DATE() - INTERVAL'3 month'
    ),

    raw_data AS
    (SELECT block_timestamp, tx_hash, from_address, to_address, tx_json
    FROM ethereum.core.fact_transactions
    WHERE tx_hash IN (SELECT tx FROM luna_tx)),

    labelled_tx AS
    (SELECT tx.*, label.address_name, label.label_type, label.label
    FROM raw_data tx
    LEFT JOIN ethereum.core.dim_labels label
    ON tx.to_address = label.address)


    SELECT DATE_TRUNC('day', block_timestamp) AS "Days",
    COUNT(tx_hash) AS "Transaction Volume",
    (CASE
    WHEN label IS NULL THEN 'Others'
    ELSE label
    END) AS label
    FROM labelled_tx
    GROUP BY "Days", label



    Run a query to Download Data