Example queries

As the data are organised in separate tables we need to perform joins in order to combine the information and retrieve the data we want. Some fields that can be used for joining:

Retrieve the time when a transaction was created

The transaction was added inside a block (that is a "sum" of transactions) and the block itself was produced at a specific slot (it's a point in time). So in order to retrieve the time of a transaction:

SELECT t.tx_hash, b.block_time 
FROM `iog-data-analytics.cardano_mainnet.tx` AS t 
  JOIN `iog-data-analytics.cardano_mainnet.block` AS b 
  ON t.slot_no = b.slot_no
WHERE t.tx_hash = '<tx_hash>'

Check if the outputs of an address where consumed

As it is mentioned above in the Transactions section, a transaction can be uniquely identified by the combination of a slot number and a transaction index (an index that points to a transaction inside a block). Thus a transaction output can be uniquely identified by the combination slot number, transaction index, output index (an index that points to a specific output, from the transaction's output list). So if we'd like to see for a specific address which outputs have been used (ie. consumed):

WITH
  addr_outputs AS (
  SELECT
    epoch_no,
    address,
    CAST(JSON_VALUE(o, '$.idx') AS INT64) AS idx,
    CAST(JSON_VALUE(o, '$.slot_no') AS INT64) AS slot_no,
    CAST(JSON_VALUE(o, '$.txidx') AS INT64) AS txidx
  FROM
    `cardano_mainnet.rel_addr_txout`
  LEFT JOIN
    UNNEST(JSON_EXTRACT_ARRAY(outputs, "$") ) AS o
  WHERE address = '<address>' )
  SELECT 
    tx.epoch_no,
    ao.address,
    ao.slot_no,
    ao.txidx,
    ao.idx,
    consumed_in_slot_no,	
    consumed_in_txidx
  FROM addr_outputs AS ao 
  JOIN `cardano_mainnet.tx_consumed_output` AS cons 
       ON ao.slot_no = cons.slot_no AND ao.txidx = cons.txidx AND ao.idx = cons.index
  JOIN cardano_mainnet.tx ON tx.slot_no = cons.consumed_in_slot_no AND tx.txidx = cons.consumed_in_txidx

Check the rewards for a stake address

A stake address can be in the bech32 format of stake1.... If we'd like to track all the rewards this address has received we would have to first find the stake address key hash encoding and then check the reward table:

SELECT * 
FROM `cardano_mainnet.rel_stake_addr_hash` AS sa 
JOIN `cardano_mainnet.reward` AS r ON  sa.stake_addr_hash = r.stake_addr_hash 
WHERE sa.stake_address = 'stake1...`

Check the balance of an address

We can retrieve the total amount of Lovelaces that went "in" a specific address, as well as the total amount of Lovelaces that went "out". The difference of the two ("in" - "out") will give us the current balance of that address in Lovelaces. In order to do that one can use the vw_address_balance view:

SELECT * FROM `iog-data-analytics.cardano_mainnet.vw_address_balance` 
WHERE address = 'addr1zyxwv...'