Activity hours

One row per address, chain, date and hour with at least one transaction. native_value sums the native value the address’s transactions moved in that hour, in the chain’s smallest unit; it is null on solana, where native value is not tracked per transaction.

Columns

Column	Type	Description
`address`	STRING	Address that initiated the transactions: lowercase hex on `ethereum`, `base` and `robinhoodchain`, base58 and case-sensitive on `solana`.
`chain_id`	STRING	Chain the address transacted on.
`chain_family`	STRING	Chain family the chain belongs to: `evm` or `svm`; joins `dimensions.chains`.
`activity_date`	DATE	UTC calendar date of the hour. Partition column. Full history per chain from its first standardized block. The latest date is partial until the UTC day closes and is rewritten by the next run.
`hour_utc`	INT64	Hour of day, 0 through 23, UTC.
`tx_count`	INT64	Transactions the address initiated in that hour, whether or not the address is attributed. Rows on the evm chains include reverted transactions; solana rows are successful, non-vote transactions only.
`native_value`	NUMERIC	Native value the address’s transactions moved in that hour, summed, in the chain’s smallest unit (wei on the evm chains). Null on solana, which has no transaction-level native value.

Sample queries

This table is large and split by day. Bound activity_date and filter chain_id on every query, or you read the whole table and the whole table is billed to you.

One address, one day
Busiest hour over a month
Attributed senders, one day
One address, two chains

Pivots the day’s rows into one, hour_utc becoming 24 columns. tx_count is 0 for an hour with no transactions.

select
    address,
    activity_date,
    sum(if(hour_utc = 0, tx_count, 0)) as hour_00,
    sum(if(hour_utc = 1, tx_count, 0)) as hour_01,
    sum(if(hour_utc = 2, tx_count, 0)) as hour_02,
    sum(if(hour_utc = 3, tx_count, 0)) as hour_03,
    sum(if(hour_utc = 4, tx_count, 0)) as hour_04,
    sum(if(hour_utc = 5, tx_count, 0)) as hour_05,
    sum(if(hour_utc = 6, tx_count, 0)) as hour_06,
    sum(if(hour_utc = 7, tx_count, 0)) as hour_07,
    sum(if(hour_utc = 8, tx_count, 0)) as hour_08,
    sum(if(hour_utc = 9, tx_count, 0)) as hour_09,
    sum(if(hour_utc = 10, tx_count, 0)) as hour_10,
    sum(if(hour_utc = 11, tx_count, 0)) as hour_11,
    sum(if(hour_utc = 12, tx_count, 0)) as hour_12,
    sum(if(hour_utc = 13, tx_count, 0)) as hour_13,
    sum(if(hour_utc = 14, tx_count, 0)) as hour_14,
    sum(if(hour_utc = 15, tx_count, 0)) as hour_15,
    sum(if(hour_utc = 16, tx_count, 0)) as hour_16,
    sum(if(hour_utc = 17, tx_count, 0)) as hour_17,
    sum(if(hour_utc = 18, tx_count, 0)) as hour_18,
    sum(if(hour_utc = 19, tx_count, 0)) as hour_19,
    sum(if(hour_utc = 20, tx_count, 0)) as hour_20,
    sum(if(hour_utc = 21, tx_count, 0)) as hour_21,
    sum(if(hour_utc = 22, tx_count, 0)) as hour_22,
    sum(if(hour_utc = 23, tx_count, 0)) as hour_23
from `facts_accounts.activity_hours`
where address = '0x0000000000000000000000000000000000000000'
  and chain_id = 'robinhoodchain'
  and activity_date = date('2026-09-01')
group by address, activity_date

chain_id and a bounded activity_date range keep the scan to one chain and one month; hour_utc groups every address together.

select
    hour_utc,
    sum(tx_count) as tx_count
from `facts_accounts.activity_hours`
where chain_id = 'robinhoodchain'
  and activity_date >= date('2026-08-01')
  and activity_date < date('2026-09-01')
group by hour_utc
order by hour_utc

Joining dimensions.accounts on address and chain_id narrows the hours to addresses somebody has attributed.

select
    hours.address,
    hours.hour_utc,
    sum(hours.tx_count) as tx_count
from `facts_accounts.activity_hours` as hours
join `dimensions.accounts` as accounts
    on hours.address = accounts.address
    and hours.chain_id = accounts.chain_id
where hours.chain_id = 'robinhoodchain'
  and hours.activity_date = date('2026-09-01')
group by hours.address, hours.hour_utc
order by hours.address, hours.hour_utc

The same address key on two evm chains lets you line up its hour-of-day profile on each side by side.

select
    chain_id,
    hour_utc,
    sum(tx_count) as tx_count
from `facts_accounts.activity_hours`
where address = '0x0000000000000000000000000000000000000000'
  and chain_id in ('ethereum', 'base')
  and activity_date >= date('2026-08-01')
  and activity_date < date('2026-09-01')
group by chain_id, hour_utc
order by chain_id, hour_utc

Overview

Popular datasets

Market verticals

Chain ecosystems

Core entities

Columns

Sample queries

​Columns

​Sample queries

Columns

Sample queries