SajjadiiiOverall Heatmap Transactions
    Updated 2025-03-05
    -- forked from Overall Heatmap user @ https://flipsidecrypto.xyz/studio/queries/43d3acfb-39ce-4361-b386-16c29b42a5d1

    with mint_mmp_tab AS (
    SELECT
    block_timestamp
    ,tx_hash
    ,origin_from_address AS user_address
    ,amount AS mmp_amount
    FROM kaia.core.ez_token_transfers
    WHERE contract_address = '0x1c7354946d67426ce4e4625d8a82d0db8977ece9' -- mmp token_contract
    AND contract_address = origin_to_address
    AND from_address = '0x0000000000000000000000000000000000000000' -- mint mmp

    ),
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    trace_data AS (
    SELECT
    BLOCK_TIMESTAMP,
    TX_HASH,
    FROM_ADDRESS,
    TO_ADDRESS,
    TRACE_INDEX,
    VALUE_PRECISE
    FROM kaia.core.fact_traces
    WHERE block_timestamp::date >= '2025-01-01'
    AND tx_hash in (SELECT tx_hash FROM kaia.core.fact_event_logs WHERE topics[0] = '0x960a6bb8032c55b0217dc5d5e962cd62168b247a4dfc80213fb185caeb120143')
    -- tx_hash in ( '0x97f54fe7cdad1fe44d7de3bd96f5b693aba84a9eed938b68dfa79347d368bee9' , '0xe82611c41c82c3c10f185acfd8926f388cba1453ae3239b9bc7bbe8d9dc7547a' , '0x46c1d2c7cfccd0e192761a73a402ddc920a4c08122f80ea5fe3ea3947c818884')
    ),
    fees AS (
    SELECT
    TX_HASH,
    SUM(CASE WHEN To_address = '0x4d129c57945e769dde797ae7058da8d6c89fd9db' THEN VALUE_PRECISE ELSE 0 END) AS FIRST_FEE,
    SUM(CASE WHEN TO_ADDRESS = '0xfb1bffc9d739b8d520daf37df666da4c687191ea' THEN VALUE_PRECISE ELSE 0 END) AS SECOND_FEE
    FROM trace_data
    GROUP BY TX_HASH
    ),
    Last run: about 1 month ago
    TX_HOUR
    TX_DAY
    TX_COUNT
    1
    0Sunday3130
    2
    1Sunday3090
    3
    2Sunday2919
    4
    3Sunday10579
    5
    4Sunday13193
    6
    5Sunday13785
    7
    6Sunday14494
    8
    7Sunday14831
    9
    8Sunday14733
    10
    9Sunday15811
    11
    10Sunday15502
    12
    11Sunday15606
    13
    12Sunday7590
    14
    13Sunday7022
    15
    14Sunday6918
    16
    15Sunday8318
    17
    16Sunday8229
    18
    17Sunday8883
    19
    18Sunday8856
    20
    19Sunday9239
    ...
    168
    3KB
    261s