The following are some common queries that can be used to fetch data for bitcoin holdings and activity on a per user basis.

​
Fetch latest UTXO Balance

WITH address AS (

    SELECT

      '1DGBdRopW2mqcB75QvSFRsyRbTL4KhJBrY' AS address

  ),

  outputs_by_address AS (

    SELECT

      address0,

      VALUE,

      utxo_id

    FROM

      bitcoin.unspent_outputs outputs

    WHERE address0 = (select address from address)

  ),

  valid_utxos AS (

    SELECT

      outputs_by_address.*

    FROM

      outputs_by_address

      LEFT JOIN bitcoin.inputs ON inputs.spent_utxo_id = outputs_by_address.utxo_id

    WHERE

      inputs.spent_utxo_id IS NULL

  )

  SELECT

    address0 AS address,

    SUM(VALUE:: DOUBLE PRECISION) AS total_value

  FROM

    valid_utxos

  GROUP BY

    address

​
Fetch all inputs and outputs from transactions that a user participated in

WITH address AS (

    SELECT

      '12F4pRSkiJdU9NdXcdb8LuFn7akoNM8SW4' AS address

  ), inputs as (

    select

      spent_utxo_id,

      'input' as type,

      transaction_hash,

      block_timestamp,

      address0,

      value

    from bitcoin.enriched_inputs

    where address0 = (select address from address)

  ), outputs as (

    select

      utxo_id,

      'output' as type,

      transaction_hash,

      block_timestamp,

      address0,

      value

    from bitcoin.outputs

    where address0 = (select address from address)

  )

  select * from inputs

  union

  select * from outputs

​
Fetch Inscription Balances

WITH address AS (

    SELECT

      '12F4pRSkiJdU9NdXcdb8LuFn7akoNM8SW4' AS address

  )

  select

    to_address AS address,

    SUM(VALUE:: DOUBLE PRECISION) AS total_value

    from bitcoin.ordinals_inscription_transfers

  where to_address = (select address from address)

  group by address

​
Fetch all Inscription Transfers

WITH address AS (

    SELECT

      '12F4pRSkiJdU9NdXcdb8LuFn7akoNM8SW4' AS address

  )

  select

    from_utxo_id,

    to_utxo_id,

    from_address,

    to_address,

    transaction_hash,

    block_timestamp

    cleaned_token_tick,

    inscription_id

    from bitcoin.ordinals_inscription_transfers

  where from_address = (select address from address)

  or to_address = (select address from address)

​
Fetch all BRC-20 Token Transfers

WITH address AS (

    SELECT

      '12F4pRSkiJdU9NdXcdb8LuFn7akoNM8SW4' AS address

  ),

  token_transfers_by_token_tick AS (

    SELECT

      ordinals_token_transfers.transaction_hash,

      ordinals_token_transfers.block_timestamp,

      ordinals_token_transfers.token_tick,

      ordinals_token_transfers.block_number,

      ordinals_token_transfers.transaction_index,

      ordinals_token_transfers.sat_tx_offset

    FROM

      bitcoin.ordinals_token_transfers

    WHERE from_address = (select address from address)

    OR to_address = (select address from address)

  )

  SELECT * FROM token_transfers_by_token_tick;

​
Fetch latest BRC-20 Balance

WITH address AS (

    SELECT

      '12F4pRSkiJdU9NdXcdb8LuFn7akoNM8SW4' AS address

  ),

  token_transfers_by_token_tick AS (

    SELECT

      ordinals_token_transfers.token_tick,

      ordinals_token_transfers.block_number,

      ordinals_token_transfers.transaction_index,

      ordinals_token_transfers.sat_tx_offset,

      ROW_NUMBER() OVER (PARTITION BY token_tick ORDER BY block_number DESC,

        transaction_index DESC,

        sat_tx_offset DESC) AS rn

    FROM

      bitcoin.ordinals_token_transfers

    WHERE from_address = (select address from address)

    OR to_address = (select address from address)

  )

  SELECT * FROM token_transfers_by_token_tick WHERE rn = 1;

​
Fetch Rare Sats

WITH address AS (

    SELECT

      '112uTnRahwBKPvTmj9uwXP5qqNo8g2rdig' AS address

  )

  select * from bitcoin.utxos_with_rare_sats

  where address0 = (select address from address)