Deep Dive: Building a Bitcoin wallet

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

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

• Fetch all BRC-20 Token Transfers

• Fetch all Inscription Transfers

• Fetch Rare Sats

• Fetch latest BRC-20 Balance

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)