Deep Dive: Building A Bitcoin Wallet

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

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

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

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)

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;

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;

WITH address AS (
    SELECT
      '112uTnRahwBKPvTmj9uwXP5qqNo8g2rdig' AS address
  )
  select * from bitcoin.utxos_with_rare_sats
  where address0 = (select address from address)