Content

In order to reduce the cost of user understanding, we upgraded the structure of chain data onto silver level to bronze level, retained the most original data structure and opened up the full history data, so some fields were removed, such as the token_symbol , decimals , token_price , amount , cal_value and value .

How to solve

find the token_symbol, decimals from token_chain_info table
find the price from token_price_5mins
calculation method of the amount filed: amount=amount_raw*power(0.1,decimals)
calculation method of the cal_value filed: cal_value=amount_raw*power(0.1,decimals)
calculation method of the value filed: value=amount_raw*power(0.1,decimals)*price

FP Console Output

Column 'bsc_token_transfers.decimals' cannot be resolved

Code Example

SELECT ett.*, token_price.price, tci.token_symbol AS symbol, tci.decimals
        , ett.amount_raw * power(0.1, coalesce(tci.decimals, 18)) AS amount
        , ett.amount_raw * power(0.1, coalesce(tci.decimals, 18)) AS cal_value
        , ett.amount_raw * power(0.1, coalesce(tci.decimals, 18)) * token_price.price AS value
FROM (
        SELECT *
        FROM ethereum_token_transfers
        WHERE block_timestamp > date_add('day', -90, CURRENT_DATE)
) ett
        LEFT JOIN (
                SELECT *
                FROM token_chain_info
                WHERE chain = 'Ethereum'
        ) tci
        ON ett.token_address = tci.token_address
        LEFT JOIN (
                SELECT *
                FROM token_price_5min
                WHERE chain = 'Ethereum'
        ) token_price
        ON ett.token_address = token_price.token_address
                AND date_trunc('hour', ett.block_timestamp) = token_price."timestamp"