The following query finds the USD balance of a wallet over time. The following SQL can also be used to find the TVL of a protocol (DEX, Bridge, etc).

SELECT [signed_at:aggregation] as date 
			 , sum(sum((token_amount/pow(10, num_decimals)*price_in_usd))) OVER (ORDER BY date) as TVL
FROM (
--Deposits (+)
SELECT signed_at
       ,to_float64_raw(data0) as token_amount -- Positive
       ,hex(log_emitter) as token_address
FROM blockchains.all_chains e
WHERE e.topic2 = unhex('00000000000000000000000088A69B4E698A4B090DF6CF5Bd7B2D47325Ad30A3')
	AND topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef')
               
UNION ALL -- UNION Withdrawals and Deposits

--Withdrawals (-)
SELECT signed_at
       ,-1.0 * to_float64_raw(data0) as token_amount -- Negative 
       ,hex(log_emitter) as token_address
FROM blockchains.all_chains e
WHERE e.topic1 = unhex('00000000000000000000000088A69B4E698A4B090DF6CF5Bd7B2D47325Ad30A3')
	AND topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef')
    ) data

			LEFT JOIN (
			      SELECT contract_address, dt, price_in_usd, num_decimals
			      FROM reports.token_prices
			      WHERE [signed_at:daterange]
			      AND chain_name = 'eth_mainnet'
			       ) prices
			            ON prices.contract_address = lower(data.token_address)
			                AND prices.dt = (SELECT max(dt) from reports.token_prices)
GROUP BY date
ORDER BY date

Want to learn how we got there? 👇

The query will retrieve all the entries in the all_chains data table.

SELECT * 
FROM blockchain.all_chains
-- The simplest query. We are going to transform this query into one that finds the TVL/Token balance of the Nomad bridge over time. 

We are going to transform this query into one that finds the TVL/Token balance of the Nomad bridge over time.

Finding token balances (a.k.a TVL) is a matter of subtracting outflows from inflows. All ERC-20 transfers in and out of an address can be tracked using the Transfer event. Transfer events have a standard structure amongst all EVM compatible chains - let’s here’s the breakdown:

As you can see above the Transfer event gives you all the data needed to find the TVL of a wallet. Whenever the address/contract in question is in the:

• topic1 column, tokens are getting transferred out (-)
• topic2 column, tokens are getting transferred in (+)

Using the lessons learn in the Total Volume For An Event tutorial (I recommend you read it before continuing) the following query can be formulated. Substitute to_float64_adj in the Total Volume For An Event tutorial for to_float64_raw. To learn more about these functions, click here.

--Inflow (+)
SELECT signed_at
       ,to_float64_raw(data1) as token_amount -- Positive
       ,extract_address(hex(topic1)) as token_address
FROM blockchains.all_chains e
WHERE topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef')
               
UNION ALL -- UNION Withdrawals and Deposits

--Outflow (-)
SELECT signed_at
       ,-1.0 * to_float64_raw(data1) as token_amount -- Negative 
       ,extract_address(hex(topic2)) as token_address
FROM blockchains.all_chains e
WHERE topic0 = unhex('ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef')

However, you want to filter out all Transfer events that aren't coming from the address/contract in question. In this example, we will be using the Nomad Bridge Contract (NBC):

88A69B4E698A4B090DF6CF5Bd7B2D47325Ad30A3

Therefore, add

AND e.topic2 = unhex('00000000000000000000000088A69B4E698A4B090DF6CF5Bd7B2D47325Ad30A3')

to the inflow query, and