BigQuery dataset

This dataset is subject to change without notice.
Please provide feedback on missing features to be included in the final ETL.

Public dataset

Nodely maintains a public BigQuery dataset with Algorand mainnet contents:

snapshot only (daily updates once schema is finalized)
schema inspired by Google public blockchain datasets (ETH, …)
dataset is public but not free - Google charges for data read from this dataset
contact [email protected] for datasets and/or SelfService BI with hourly or 5m incremental loads.

Quick start

Go to Algorand dataset @ BigQuery and start playing ;D

Reading list

Schema

Base tables

CREATE TABLE `algonode.algorand_mainnet_dev.transactions`
(
  block_number        INT64 NOT NULL      -- Block number in which this transaction is located
  block_timestamp     TIMESTAMP NOT NULL  -- Unix timestamp when the block was assembled
  block_index         INT64 NOT NULL      -- Transaction's index position in the block
  transaction_hash    STRING NOT NULL     -- Hash of the transaction
  transaction_group   BYTES               -- Parent atomic group id (binary)
  transaction_type    STRING NOT NULL     -- One of
                                          --   acc_payment : simple transfer of Algo to account with non-zero balance
                                          --   acc_payment_create : transfer of Algo to zero balance (nonexisting/closed) account
                                          --   acc_close : transfer of Algo with a secondary transfer of the remider balance
                                          --   app_call : smart contract call
                                          --   app_clear : call "clear-state" part of the SC, clear local SC state
                                          --   app_close : call the SC, clear local SC state
                                          --   app_delete : call the SC, delete the SC
                                          --   app_optin : allocate local state, call th SC
                                          --   app_update : call the SC, replace SC code
                                          --   asa_transfer : simple transfer of Token/NFT
                                          --   asa_config : token/NFT reconfiguraion (of allowed fields)
                                          --   asa_create : token/NFT mint
                                          --   asa_optin asa_optout  : allocate/remove minimal balance on the account for the token
                                          --   asa_clawback : force withdraw token from the account
                                          --   asa_destroy : destroy token/NFT completly
                                          --   asa_freeze asa_unfreeze : freeze/unfreeze token/NFT in the account
                                          --   key_reg key_unreg : un/register participation key
                                          --   stpf : state proof transaction
  from_address_id     INT64               -- Sender address id - join with v_accounts_last_state to get address / state
  to_address_id       INT64               -- Recipient address id - join with v_accounts_last_state to get address / state
  value_float         FLOAT64 NOT NULL    -- Decimal adjusted amount of Algo or token (lossy floating point)
  value_int           NUMERIC(20) NOT NULL-- Integer amount of Algo or Token without decimal shift
                                          -- (raw value, needs decimal shift as in v_tokens_last_state)
  close_to_address_id INT64               -- Address id of recipient of the closing balance
  close_value_float   FLOAT64 NOT NULL    -- Closing balance amount, decimal adjusted
  close_value_int     NUMERIC(20) NOT NULL-- Closing balance amount without decimal shift
  gas                 INT64 NOT NULL      -- Transaction free in microAlgos
  note                STRING              -- Optional note (up to 1KB)
  rekey_to_address_id INT64               -- Account rekey to address id
  auth_by_address_id  INT64               -- Transaction authorized by this address id (if sender is rekeyed)
  has_inners          BOOL NOT NULL       -- Transaction has inner transactions
  is_inner            BOOL NOT NULL       -- Transaction is an inner transaction issued by smart contract
  clawback_from_address_id INT64          -- Address id of the account the token is clawed back from
  token_cfg           STRUCT<list ARRAY<STRUCT<element INT64>>> NOT NULL -- clawback,freeze,manager,reserve address id in random order
  token_id            INT64               -- Asset id (token/nft) - join with v_assets_last_state
  token_total         NUMERIC(20)         -- Total number of assets minted
  token_unit_name     STRING              -- Asset unit name
  contract_id         INT64               -- ID of the contract called
  input               STRING              -- Extra TX data / app call input")
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY block_number,block_index
OPTIONS (
  description="Algorand mainnet transactions, updated every hour. See: https://nodely.io/docs/public/bigquery",
  1labels=[("status", "development")]
);

CREATE TABLE `algonode.algorand_mainnet_dev.accounts`
(
  addr_id               INT64 NOT NULL    -- id of the address
  address               STRING NOT NULL   -- Algorand string representation of the account address
  balance_micro         NUMERIC NOT NULL  -- Account microAlgo balance
  type                  STRING            -- Account type : sig|msig|contract. sig/msig detection needs at least one outflow
  is_deleted            BOOL NOT NULL     -- Is account closed
  is_online             BOOL NOT NULL     -- Is account registered to vote
  block_number          INT64 NOT NULL    -- Block of the first inflow that created the account
  block_timestamp       TIMESTAMP NOT NULL-- Timestamp of the first inflow that created the account
  last_closed_at        INT64             -- Block of the last account close operation
  last_closed_at_ts     TIMESTAMP         -- Timestamp of the last account close operation
  rekeyed_to_address_id INT64             -- Address id of account that is solely authorized to sign for this account
  contract_id           INT64             -- Id of smart contract that owns this account
  nft_count             INT64 NOT NULL    -- Number of different tokens with total numner == 1 this account holds
  token_count           INT64 NOT NULL    -- Number of different tokens (of any type) this account holds
  usd_stables_balance   FLOAT64 NOT NULL  -- USD value of all stable USD tokens this account holds
  voting_from_block     INT64             -- First block this account can vote on
  voting_until_block    INT64             -- Last block this account can vote on
  mbr_micro             INT64 NOT NULL    -- Microalgos locked in this account as 'minimum balance'
  row_ver               INT64 NOT NULL    -- Last updated at this block number")
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY addr_id
OPTIONS (
  description="Account state, updated every hour. Use v_accounts_last_state to render latest state. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

CREATE TABLE `algonode.algorand_mainnet_dev.blocks`
(
  block_timestamp     TIMESTAMP NOT NULL -- Block timestamp
  block_number        INT64 NOT NULL     -- Block number
  proposer_address_id INT64 NOT NULL     -- Address id of block proposer
  voter_address_ids   STRUCT<list ARRAY<STRUCT<element INT64>>> NOT NULL
                                         -- List of cert voters addr ids, up to 75% threshold, random node sample
  block_algo_sum      FLOAT64 NOT NULL   -- Sum of Algo transacted in the block
  block_gas_sum_malgo INT64 NOT NULL     -- Total fees in microAlgos
  block_rewards_malgo INT64 NOT NULL     -- Rewards to proposer in microAlgos
  block_tx_cnt        INT64 NOT NULL     -- Successful transactions in the block
  tokens_dc           INT64 NOT NULL     -- Tokens distinct count in the block
  nft_dc              INT64 NOT NULL     -- NFTs distinct count in the block
  contract_dc         INT64 NOT NULL     -- Contracts distinct count in the block
  token_id_top        INT64 NOT NULL     -- Top transacted token id in the block
  constract_id_top    INT64 NOT NULL     -- Top called contract id in the block
  addresses_new_cnt   INT64 NOT NULL     -- New addresses seen in the block
  group_cnt           INT64 NOT NULL     -- Atomic group count in the block
  group_max           INT64 NOT NULL     -- TX count in the largest group
  addresses_dc        INT64 NOT NULL     -- Addresses distinct count in the block
  pay_cnt             INT64 NOT NULL     -- Count of Algo payment TX
  keyreg_cnt          INT64 NOT NULL     -- Count of key (un)registration TX
  acfg_cnt            INT64 NOT NULL     -- Count of token configuration TX
  axfer_cnt           INT64 NOT NULL     -- Count of token transfer TX
  afrz_cnt            INT64 NOT NULL     -- Count of token (un)freeze TX
  appl_cnt            INT64 NOT NULL     -- Count of contract call TX
  stpf_cnt            INT64 NOT NULL     -- Count of state proof TX
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY block_number
OPTIONS (
  description="Blocks stats, updated every hour. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

CREATE TABLE `algonode.algorand_mainnet_dev.tokens`
(
  token_id            INT64 NOT NULL      -- ID of the native token (ASA)
  block_number        INT64 NOT NULL      -- Block number of token creation
  block_timestamp     TIMESTAMP NOT NULL  -- Timestamp of token creation
  creator_addr_id     INT64               -- ID of the creator address
  freeze_addr_id      INT64               -- ID of the freeze address
  manager_addr_id     INT64               -- ID of the manager address
  reserve_addr_id     INT64               -- ID of the reserve address
  clawback_addr_id    INT64               -- ID of the clawback address
  unit_name           STRING              -- Token unit name  (ticker)
  name                STRING              -- Tokan full name
  url                 STRING              -- URL associated with the token
  is_nft              BOOL NOT NULL       -- Is this an NFT (total = 1)
  arcs STRUCT<list ARRAY<STRUCT<element INT64>>> NOT NULL -- List of standards this token follows
  ipfs_json           STRING              -- JSON object with metadata downloaded from IPFS
  default_frozen      BOOL NOT NULL       -- Is the token frozen by default
  token_total         NUMERIC(20) NOT NULL-- Total number of tokens
  decimals            INT64 NOT NULL      -- Decimal shift
  is_deleted          BOOL NOT NULL       -- Is the token deleted
  closed_at           INT64               -- Block number of the token deletion
  closed_at_timestamp TIMESTAMP           -- Timestamp of the token deletion
  row_ver             INT64 NOT NULL      -- Row Version
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY token_id
OPTIONS(
  description="Native tokens (Algorand Standard Assets), updated every hour. Use v_token_last_state to render latest state. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

CREATE TABLE `algonode.algorand_mainnet_dev.token_holdings`
(
  token_id          INT64 NOT NULL        -- ID of the token / NFT
  addr_id           INT64 NOT NULL        -- Holder address id
  token_total       NUMERIC(20) NOT NULL  -- Total number of tokens minted
  balance_int       NUMERIC(20) NOT NULL  -- Balance without decimal shift
  balance_float     FLOAT64 NOT NULL      -- Balance , decimal shifter
  unit_name         STRING NOT NULL       -- Unit name (ticker)
  is_circulating    BOOL NOT NULL         -- Does this balance count towards circulating supply
  is_frozen         BOOL NOT NULL         -- Is the token frozen
  is_deleted        BOOL NOT NULL         -- Is the holding deleted
  block_number      INT64 NOT NULL        -- Block number when address opted-in to the token
  block_timestamp   TIMESTAMP NOT NULL    -- Opt-in timestamp
  closed_at         INT64                 -- Block number holding got deleted (opted-out)
  closed_at_ts      TIMESTAMP             -- Opt-out timestamp
  row_ver           INT64 NOT NULL        -- Row version
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY token_id
OPTIONS(
  description="Native tokens holdings, updated every hour. Use v_token_holding_last_state to render latest state. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

CREATE TABLE `algonode.algorand_mainnet_dev.contracts`
(
  contract_id INT64 NOT NULL,
  block_number INT64 NOT NULL,
  block_timestamp TIMESTAMP NOT NULL,
  creator_addr_id INT64 NOT NULL,
  is_deleted BOOL NOT NULL,
  closed_at INT64,
  closed_at_ts TIMESTAMP,
  code BYTES,
  code_hash_sha256 BYTES,
  clear_code BYTES,
  clear_code_hash_sha256 BYTES,
  addr_id INT64,
  boxes INT64 NOT NULL,
  users INT64 NOT NULL,
  users_current INT64 NOT NULL,
  row_ver INT64 NOT NULL
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY contract_id
OPTIONS (
  description="Algorand mainnet contracts, updated every hour. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

Base views

-- Simple non zero Algo / NFT / Token flows
-- Each transaction creates 2 or more flows , +close_to, +clawback

-- Caveats:
-- * Clawback not rendered yet
-- * An NFT is a token with a total number or units == 1
--
create view `algorand_mainnet_dev.v_flows_simple` as
with flows as (
-- no clawback
  select
    block_timestamp
    , from_address_id addr_id
    , to_address_id other_addr_id
    , 'outflow' as dir
    , value_float as flow_value
    , gas / pow(10,6) gas_algo
    , case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
    , token_id
    , t.is_inner
    , t.transaction_type
  from
    `algorand_mainnet_dev.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
  select
    block_timestamp
    , from_address_id addr_id
    , close_to_address_id other_addr_id
    , 'outflow' as dir
    , close_value_float as flow_value
    , 0 gas_algo
    , case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
    , token_id
    , t.is_inner
    , t.transaction_type
  from
    `algorand_mainnet_dev.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
    and close_to_address_id IS NOT NULL
UNION ALL
  select
    block_timestamp
    , to_address_id addr_id
    , from_address_id other_addr_id
    , 'inflow' as dir
    , value_float as flow_value
    , 0 gas_algo
    , case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
    , token_id
    , t.is_inner
    , t.transaction_type
  from
    `algorand_mainnet_dev.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
  select
    block_timestamp
    , close_to_address_id addr_id
    , from_address_id other_addr_id
    , 'inflow' as dir
    , close_value_float as flow_value
    , 0 gas_algo
    , case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
    , token_id
    , t.is_inner
    , t.transaction_type
  from
    `algorand_mainnet_dev.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
    and close_to_address_id IS NOT NULL
) select * from flows where flow_value > 0;

-- return only last row version
CREATE VIEW `algorand_mainnet_dev.v_accounts_last_state` AS
SELECT
  *
FROM `algorand_mainnet_dev.accounts`
QUALIFY ROW_NUMBER() OVER(PARTITION BY addr_id ORDER BY row_ver DESC) = 1;

-- return only last row version
CREATE VIEW `algorand_mainnet_dev.v_tokens_last_state` AS
SELECT
  *
FROM `algorand_mainnet_dev.tokens`
QUALIFY ROW_NUMBER() OVER(PARTITION BY token_id ORDER BY row_ver DESC) = 1;

-- return only last row version
CREATE VIEW `algorand_mainnet_dev.v_token_holdings_last_state` AS
SELECT
  *
FROM `algorand_mainnet_dev.token_holdings`
QUALIFY ROW_NUMBER() OVER(PARTITION BY token_id, addr_id ORDER BY row_ver DESC) = 1;

-- return only last row version
CREATE VIEW `algorand_mainnet_dev.v_contracts_last_state` AS
SELECT
   *
FROM `algorand_mainnet_dev.contracts`
QUALIFY ROW_NUMBER() OVER(PARTITION BY contract_id ORDER BY row_ver DESC) = 1

Aux tables

token_prices

CREATE TABLE `algonode.algorand_mainnet_dev.token_prices`
(
  token_id INT64 NOT NULL -- id of the token
  token_name STRING NOT NULL -- token name
  token_ticker STRING NOT NULL -- token ticker
  algo_value FLOAT64 NOT NULL -- token current value in Algo
  market_cap FLOAT64 NOT NULL -- token current MC in Algo
  first_block_number INT64 NOT NULL -- first block with price data")
)
CLUSTER BY token_id
OPTIONS(
  description="Token prices, updated every hour. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

Example queries

Accounts

-- <1 GB processed
SELECT
  TIMESTAMP_TRUNC(bs.block_timestamp, MONTH) ts
  , sum(addresses_new_count) addresses_created
FROM
  `algorand_mainnet_dev.blocks` bs
GROUP BY
  ts
ORDER BY
  ts

-- 40GB processes
SELECT
  TIMESTAMP_TRUNC(txn.block_timestamp, MONTH) ts
  , count(*) addresses_created
FROM
  `algorand_mainnet_dev.transactions` txn
WHERE
  txn.transaction_type = 'acc_payment_create'
GROUP BY
  ts
ORDER BY
  ts

Daily discinct addresses stats

Fast
With account type detection

-- no clawback
 select
  TIMESTAMP_TRUNC(flows.block_timestamp, DAY) ts
  ,count(distinct(flows.addr_id)) uniq_addresses
  ,countIf(dir = 'outflow' and transaction_type = 'acc_payment_create') new_addresses
  ,count(distinct(case when dir = 'outflow' then flows.addr_id else NULL end)) uniq_senders
  ,count(distinct(case when dir = 'outflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_senders_over_100A
  ,count(distinct(case when dir = 'inflow' then flows.addr_id else NULL end)) uniq_reveicers
  ,count(distinct(case when dir = 'inflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_receivers_over_100A
  ,count(distinct(case when dir = 'inflow' then flows.addr_id || ':' || other_addr_id else NULL end)) uniq_pairs
  from
    `algorand_mainnet_dev.v_flows_simple` flows
  where
    flows.block_timestamp between '2024-04-01' and '2024-04-30'
  group by ts
  order by ts

-- no clawback
 select
  TIMESTAMP_TRUNC(flows.block_timestamp, DAY) ts
  ,count(distinct(flows.addr_id)) uniq_addresses
  ,count(distinct(case when coalesce(ame.type,'') != 'contract' then flows.addr_id else NULL end)) uniq_users
  ,countIf(dir = 'outflow' and transaction_type = 'acc_payment_create') new_addresses
  ,count(distinct(case when dir = 'outflow' then flows.addr_id else NULL end)) uniq_senders
  ,count(distinct(case when dir = 'outflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_senders_over_100A
  ,count(distinct(case when dir = 'inflow' then flows.addr_id else NULL end)) uniq_reveicers
  ,count(distinct(case when dir = 'inflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_receivers_over_100A
  ,count(distinct(case when dir = 'inflow' then flows.addr_id || ':' || other_addr_id else NULL end)) uniq_pairs
  ,count(distinct(case when dir = 'inflow' and coalesce(ame.type,'') != 'contract' and coalesce(aoth.type,'') != 'contract' then flows.addr_id || ':' || other_addr_id else NULL end)) uniq_p2p_pairs
  from
    `algorand_mainnet_dev.v_flows_simple` flows
      join `algorand_mainnet_dev.v_accounts_last_state` ame on (flows.addr_id = ame.addr_id)
      join `algorand_mainnet_dev.v_accounts_last_state` aoth on (flows.other_addr_id = aoth.addr_id)
  where
    flows.block_timestamp between '2024-04-01' and '2024-04-30'
  group by ts
  order by ts

ts	u_addr	u_users	n_addr	u_snd	u_snd_100	u_rcv	u_rcv_100	u_pairs	u_p2p_pairs
2024-04-01	60379	58682	8039	35924	6789	56326	7868	125254	100499
2024-04-02	57538	56058	8472	34813	6901	54054	7542	116025	94185
2024-04-03	68982	67593	11078	35524	7105	65422	7688	125994	106034
2024-04-04	91427	78440	7981	38997	7076	88015	12873	157863	118940
2024-04-05	79063	66208	8843	40902	7572	75180	8086	153148	113668
2024-04-06	194888	191872	8005	20760	6017	190567	6271	416394	395175

Top 10

-- White Stars (spawning new accounts)
-- ~10GB
WITH whiteStars AS (
  SELECT
    from_address_id addr_id,
    count(distinct(to_address_id)) spread
  FROM `algorand_mainnet_dev.transactions`
  WHERE
    block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
    AND transaction_type = 'acc_payment_create'
    AND value_int > 0
  GROUP BY from_address_id
  ORDER BY spread DESC
  LIMIT 10
) SELECT
    address , spread as new_accounts
  FROM
    whiteStars JOIN `algorand_mainnet_dev.v_accounts_last_state` acc USING (addr_id)
  ORDER BY new_accounts DESC

-- Spammers (dusting existing accounts)
-- ~10GB
WITH spammers AS (
  SELECT
    from_address_id addr_id,
    count(distinct(to_address_id)) spread
  FROM `algorand_mainnet_dev.transactions`
  WHERE
    block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
    AND transaction_type = 'acc_payment'
    AND value_int > 0
  GROUP BY from_address_id
  ORDER BY spread DESC
  LIMIT 10
) SELECT
    addr_id , spread as victims
  FROM
    spammers
  ORDER BY victims DESC

-- Blackholes (eating from most distinct account )
-- ~10GB
WITH bhole AS (
  SELECT
    to_address_id addr_id,
    count(distinct(from_address_id)) spread
  FROM `algorand_mainnet_dev.transactions`
  WHERE
    block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
    AND transaction_type = 'acc_payment'
    AND value_int > 0
    AND NOT is_inner
  GROUP BY addr_id
  ORDER BY spread DESC
  LIMIT 10
) SELECT
    addr_id , spread as victims
  FROM
    bhole
  ORDER BY victims DESC

Token value

Tokens MCap
TOP 25 holders

-- Circulating (non clawback, non reserve, not burned) tokens value in USD
DECLARE algousdc FLOAT64 DEFAULT 0;

SET (algousdc) = (
  SELECT AS STRUCT algo_value
  FROM `algorand_mainnet_dev.token_prices`
  WHERE token_id  = 31566704 /* USDC */
);

SELECT
  sum(th.balance_float * algo_value) / algousdc MC_USD
FROM
  `algorand_mainnet_dev.v_token_holdings_last_state` th
  JOIN `algorand_mainnet_dev.token_prices` tp using (token_id)
WHERE
  is_circulating
  and th.addr_id not in (2853884730,1970085357,2451993965,1970086565,2451993800,2451993961,3579003880) /* skip known reserve */

-- TOP 25 token holders (except Algo) by USD value
DECLARE algousdc FLOAT64 DEFAULT 0;

SET (algousdc) = (
  SELECT AS STRUCT algo_value
  FROM `algorand_mainnet_dev.token_prices`
  WHERE token_id  = 31566704 /* USDC */
);

WITH tthaid AS (
SELECT
  th.addr_id, sum(th.balance_float * algo_value) / algousdc hold_usd_val
FROM
  `algorand_mainnet_dev.v_token_holdings_last_state` th
  JOIN `algorand_mainnet_dev.token_prices` tp using (token_id)
WHERE
  is_circulating
  and th.addr_id not in (2853884730,1970085357,2451993965,1970086565,2451993800,2451993961,3579003880) /* skip known reserve */
GROUP BY
  th.addr_id
ORDER BY hold_usd_val DESC
LIMIT 25
) SELECT
  hold_usd_val, address
  FROM tthaid JOIN `algorand_mainnet_dev.v_accounts_last_state` using (addr_id)

ToDo

Base tables
- contracts
- account_tokens
- account_contracts
Aux tables
- Account labels
- NFD labels
Virtual transaction tables
- Genesis
- Legacy rewards
- 2024+ block rewards
Transformation
- Creator account = non circulating