How to calculate BTC supply in profit in dune analytics?

Question

I'm trying to calculate the BTC supply in profit (%) in dune analytics. Based on the metric definition: The percentage of circulating supply in profit, i.e. the percentage of existing coins whose price at the time they last moved was lower than the current price

So my idea is to query:

• a dataset of total supply of BTC on a specific date (TOTAL_SUPPLY)
• a dataset for amount of BTC token that bought at a lower price than its current price (TOTAL_BOUGHT_AMOUNT)

then the formula is:

Percentage Supply in profit = (TOTAL_BOUGHT_AMOUNT/TOTAL_SUPPLY)*100

TOTAL_BOUGHT_AMOUNT dataset I got from table dex.trades as the code below (for a specific day: 15/10/2023)

with
  profit as (
    select
      tx_hash,
      block_time,
      blockchain,
      token_bought_amount as bought_amount,
      amount_usd / token_bought_amount as value_per_token,
      amount_usd,
      token_bought_symbol
    from
      dex.trades
    where
      token_bought_symbol in ('ETH', 'stETH', 'WETH')
      and date_trunc('day', block_time) = timestamp '2023-10-15'
      and (amount_usd / token_bought_amount) < 2161.67 --current_price
  )

select
  sum(bought_amount)
from
  profit

But the result I got from this was kind of weird. Did I misunderstand any point?

Thx in advance!

Answer 1

There's a few issues here:

• Query is for 3 tokens (ETH, stETH, and WETH) - not BTC - on all blockchains (eth, arbitrum, polygon, base, optimism, and others)
• considers DEX (decentralized exchanges) only
• only buys on '2023-10-15' are included