Convert address to string in Dune dashboard

Question

I am playing with a query that does the job (not optimized yet), but I need some formatting in the sender column.

This is what I need:

1. Get rid of the initial '\' returned as sender
2. Append a 0 on sender

Here is the Dune Dashboard query I used:

WITH logs as (
    SELECT txs.from AS sender, COUNT(txs.from) AS num_transactions
    FROM xDai.Logs as xlogs, xDai.Transactions as txs
    WHERE xlogs.contract_address = CONCAT('\x', substring('{{contract}}' from 3))::bytea -- STAGE
    AND xlogs.tx_hash = txs.hash
    GROUP BY txs.from
    LIMIT 100
)


SELECT *
FROM logs
ORDER BY num_transactions DESC

I tried with (and similar):

SELECT CONCAT('0', substring(txs.from from 1)) AS sender

Currently it returns:

\x6cc661f688333ccab20670e77e52de350228ded9

I want:

0x6cc661f688333ccab20670e77e52de350228ded9

Any idea how to do it? Thanks a lot!!

Answer 1

Try REPLACE(sender, '\x', '0x') as sender0x or something along these lines