How to get the latest Transfer event transactions of a token on RSK from Covalent DB?

Question

I am querying the Covalent database in order to get information about rUSDT token Transfer transactions on RSK. The token address on the RSK blockchain is as below:

0xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96

The transfer event log's hash is as below:

0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef

Here is my query as below:

SELECT
    t.signed_at, t.fees_paid
FROM chain_rsk_mainnet.block_log_events e
INNER JOIN chain_rsk_mainnet.block_transactions t ON
  e.block_id = t.block_id
  AND e.tx_offset = t.tx_offset
WHERE
  e.topics @> array[E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'::bytea]
  AND e.topics[1] = E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND e.sender = E'\\xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96'
ORDER BY t.signed_at DESC
LIMIT 1000

I am expecting to get the transaction gas fees for the latest 1000 transfer events, but for some reason I never get a response for this query. Can someone tell me what can be the problem here?

Answer 1

You should not order by time field because t.signed_at is not an indexed field whereas e.block_id and e.tx_offset are. Order by both of them.

Replace this statement

ORDER BY t.signed_at DESC

with this one

ORDER BY e.block_id DESC, e.tx_offset DESC

so that the the final query is as follows

SELECT
    t.signed_at, t.fees_paid 
FROM chain_rsk_mainnet.block_log_events e 
INNER JOIN chain_rsk_mainnet.block_transactions t ON
  e.block_id = t.block_id
  AND e.tx_offset = t.tx_offset
WHERE 
  e.topics @> array[E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'::bytea]
  AND e.topics[1] = E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND e.sender = E'\\xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96'
ORDER BY e.block_id DESC, e.tx_offset DESC
LIMIT 1000