Numbers on the blockchain are formatted as hexadecimal strings - as seen below.

Increment stores hexadecimals as byte code. To transform topic3 from byte code to a Floating Point number - as seen in the image above - it must go through the following process:

byte code ---> hexadecimal ---> Floating Point Number

Use to_float64_raw() to transform topic3 from byte code to a Floating Point Number. The to_float64_raw() function returns a Float64 (data type):

Here's an example query👇

SELECT to_float64_raw(data0) 
FROM blockchains.all_chains
WHERE chain_name =  'eth_mainnet'
AND tx_hash = unhex('E207FF3ED73F1BBEA87B64B3D41025EADCC867A5ADECE7FB3CBC0731F7F7DA2F') -- Transfer Event

When doing math, it's crucial that your numbers are in the same data type - using different data types will return an error (e.g string * integer). Prices in the token_prices table are stored as a Floating Point number. Therefore, to multiply token price by an on-chain token amount, it must be in the Floating Point format. To transform values into a Floating Point number, use to_float64_raw().

When you are using token amounts in your analysis, you often need to divide token amounts by 10^number decimal . You can use the to_float64_adj(x,y) function to shorthand this equation - where x is the data or topic field and y is the num_decimals (this will divide by 10^y).

SELECT [signed_at:aggregation] as date
, sum(prices.price_in_usd * to_float64_raw(data1)/pow(10, prices.num_decimals)) 
, sum(prices.price_in_usd * to_float64_adj(data1, prices.num_decimals))
-- These two SELECT statements are the same
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