SELECT

    mark,

    `from` AS address,

    nonce,

    block_time,

    hash,

    dense_rank() OVER(PARTITION BY `from` ORDER BY date_trunc('YEAR', block_time), weekofyear(block_time)) AS rank_weekofyear,

    dense_rank() OVER(PARTITION BY `from` ORDER BY date_trunc('MONTH', block_time)) AS rank_month,

    row_number() OVER(PARTITION BY `from` ORDER BY block_time DESC) AS rk,

    count(1) OVER(PARTITION BY `from`) AS tx_count,

    max(block_time) OVER(PARTITION BY `from`) AS latest_tx_block_time,

    min(block_time) OVER(PARTITION BY `from`) AS earliest_tx_block_time

FROM

    arbitrum.transactions a

JOIN address_list b ON a.`from` = b.address

WHERE

    !(`to` = '0x000000000000000000000000000000000000006e' AND substring(`data`, 1, 10) = '0x679b6ded') -- ArbRetryableTx

    AND block_time > '2021-05-29' -- frst day

    -- AND success = true

    -- AND block_time < date_trunc('DAY', current_timestamp()) -- Exclude Today