Incompatible data updates


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

  1. find the token_symbol, decimals from token_chain_info table
  2. find the price from token_price_5mins
  3. calculation method of the amount filed: amount=amount_raw*power(0.1,decimals)
  4. calculation method of the cal_value filed: cal_value=amount_raw*power(0.1,decimals)
  5. 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
        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"