Most Web3 projects in the space earn revenue by taking a fee of an event's volume e.g Aave earns revenue from flashloan volume, Opensea earns revenue from sales volume, etc. Therefore, calculating total volume for an event is essential in tracking a protocols revenue.

SELECT [e.signed_at:aggregation] as date,
       sum(prices.price_in_usd * to_float64_adj(data1, prices.num_decimals)) as amount_deposited
FROM blockchains.all_chains e
    LEFT JOIN (
      SELECT contract_address, dt, price_in_usd, num_decimals
      FROM reports.token_prices prices
      WHERE [signed_at:daterange]
      AND chain_name = 'eth_mainnet'
       ) prices
            ON prices.contract_address = lower(extract_address(hex(topic1)))
                AND date_trunc('day', e.signed_at) = prices.dt
WHERE log_emitter = unhex('7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9')
AND topic0 = unhex('de6857219544bb5b7746f48ed30be6386fefc61b2f864cacf559893bf50fd951')
AND chain_name = 'eth_mainnet'
AND [signed_at:daterange]
GROUP BY date

Want to learn how we got there? 👇

The following query will retrieve entries from the all_chains data table.

SELECT * 
FROM blockchain.all_chains
LIMIT 10
-- The simplest query.

We are going to transform this query into one that queries total USD volume for deposits into Aave’s V2 lending pool. For this you need to use 2 data tables:

  1. blockchains.all_chains holds the topics and data fields needed for this analysis.
    1. The topics field holds:
      1. the deposit topic hash so your query is only looking at deposit events. (topic0)
      2. the token address so you can find the usd value of the tokens deposited (topic1)
    2. The data fields holds:
      1. the amount of tokens deposited (data1)
  2. reports.token_prices holds token prices and decimal values.

    1. Decimal Values (num_decimals):

      1. Blockchains don’t work with decimal numbers which is why they multiply values by 10^x to turn decimal numbers (price or amount of a token) into integers. For example, instead of a transaction's gas costs being 0.000000000000000001 ether, it's shown as 1 wei. The decimal field on Etherscan - as seen below - refers to how divisible a token can be, from 0 to 18. Therefore, you must query the num_decimals column to transform raw token amounts into their original quantity.

        Screen Shot 2022-10-26 at 5.28.23 pm.png

    2. Prices (price_in_usd):

      1. The price_in_usd column in the reports.token_prices table holds the historical prices of most tokens.
-- What's typically used in databases like postgres
SELECT * 
FROM blockchain.all_chains
	LEFT JOIN reports.token_prices prices
		ON

-- What you should use for Clickhouse
SELECT * 
FROM blockchain.all_chains
	LEFT JOIN (
			SELECT * 
			FROM reports.token_prices prices
			)
		ON

As you can see the LEFT JOIN statement is actually going to be a subquery. Queries running on Clickhouse - the data warehouse Increment uses - will run with the first LEFT JOIN statement; however, the second is a LOT more efficient. So in the name of efficiency, we will be using the second LEFT JOIN statement.

The query is yet to have arguments in the ON statement - we will come back to that.

Next you need to add four WHERE statements that specify:

  1. the chain_name as ‘eth_mainnet’ because your query is looking at deposit events on Ethereum.

  2. [signed_at:daterange]. To learn more about the date range parameter filter, click here

  3. the deposit topic hash which filters our query for deposit events. All events have a specific event topic hash. The hash is always located in the topic0 column in the all_chains table.

  4. the log_emitter as the Aave V2 lending pool. The log_emitter is the contract responsible for the state change and is shown in the Address section on Etherscan.

    Screen Shot 2022-10-26 at 5.41.15 pm.png

However, the blockchains.all_chains table stores entries in the log_emitter and topic0 column like this: