Skip to content
Nodely

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 `nodely-data-pub.algorand_mainnet_daily.transactions`
(
  block_number INT64 NOT NULL             -- Number of the block in which this transaction was located.
  block_timestamp TIMESTAMP NOT NULL      -- Unix timestamp when the block was assembled
  transaction_hash STRING NOT NULL        -- Hash of the transaction
  transaction_group BYTES                 -- Parent atomic group id (binary)
  transaction_offset INT64 NOT NULL       -- Transaction's index position in the block
  transaction_type STRING NOT NULL        -- One of acc_close acc_payment acc_payment_create app_call app_clear app_close app_delete app_optin app_update asa_clawback asa_config asa_create asa_destroy asa_freeze asa_optin asa_optout asa_transfer asa_unfreeze key_reg key_unreg stpf
  from_addr_id INT64                      -- Sender address id
  to_addr_id INT64                        -- Recipient address id
  value_float FLOAT64 NOT NULL            -- Decimal adjusted amount of Algo or token
  value_int NUMERIC(20) NOT NULL          -- Integer amount of Algo or Token without decimal shift
  close_to_addr_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_addr_id INT64                  -- Account rekey to address id
  auth_by_addr_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_addr_id INT64             -- Address id of the account the token is clawed back from
  token_cfg STRUCT<list ARRAY<STRUCT<element INT64>>> NOT NULL -- new address ids for reserve/clawback/freezer/manger in random order
  token_id INT64                          -- Asset id (token/nft)
  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
  root_offset INT64                       -- transaction offset pointing to the root (outer) transation
  parent_offset INT64                     -- transaction offset pointing to parent transaction
  depth INT64                             -- the depth of the transaction in the tree
  inners_all INT64                        -- number of all inners spawned by this transaction (recursive)
  inners_direct INT64                     -- number of all direct (1st level) inners spawned by this transaction
  PRIMARY KEY (block_number, transaction_offset) NOT ENFORCED,
  FOREIGN KEY (from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (close_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (rekey_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (close_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (rekey_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (auth_by_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (auth_by_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (clawback_from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
  FOREIGN KEY (token_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.tokens`(token_id) NOT ENFORCED
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY block_number, transaction_offset
OPTIONS(
  description="Algorand mainnet transactions. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "production")]
);

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_daily.v_flows_simple` as
with flows as (
-- no clawback
  select
    block_timestamp
    , from_addr_id addr_id
    , to_addr_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_daily.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
  select
    block_timestamp
    , from_addr_id addr_id
    , close_to_addr_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_daily.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
    and close_to_addr_id IS NOT NULL
UNION ALL
  select
    block_timestamp
    , to_addr_id addr_id
    , from_addr_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_daily.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
  select
    block_timestamp
    , close_to_addr_id addr_id
    , from_addr_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_daily.transactions` t
  where
    (t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
    and close_to_addr_id IS NOT NULL
) select * from flows where flow_value > 0;

Aux tables

CREATE TABLE `nodely-data-pub.algorand_mainnet_daily`.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 daily. See: https://nodely.io/docs/public/bigquery",
  labels=[("status", "development")]
);

Example queries

Accounts

Addresses created monthly

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

Daily discinct addresses stats

-- 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_daily.v_flows_simple` flows
  where
    flows.block_timestamp between '2024-04-01' and '2024-04-30'
  group by ts
  order by ts

Top 10

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

Token value

-- 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_daily.token_prices`
  WHERE token_id  = 31566704 /* USDC */
);


SELECT
  sum(th.balance_float * algo_value) / algousdc MC_USD
FROM
  `algorand_mainnet_daily.token_holdings` th
  JOIN `algorand_mainnet_daily.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 */

Demo

SELECT
  TIMESTAMP_TRUNC(block_timestamp, MONTH) yearMonth
  , count(*) newContractDeployments
FROM
  nodely-data-pub.algorand_mainnet_daily.contracts
WHERE
  block_timestamp < TIMESTAMP_TRUNC(current_timestamp(), MONTH)
GROUP BY
  yearMonth;

Video tutorials

Upcoming

  • Base tables
    • contracts_accountstate
  • Aux tables
    • Account labels
    • NFD labels
  • Virtual transaction tables
    • Genesis
    • Legacy rewards
    • 2024+ block rewards