Debug Queries

The Query Assistant can also help to debug existing queries that you have written. Give the assistant your broken SQL query as input, along with some context about what is wrong with it. The Query Assistant will do its best to output a fixed query.

Examples

Pesky Chain Targets & ENS

You're trying to find the latest 3 character long ENS name (accounting for the fact that all ENS names end in .eth). You have the following query:

SELECT ens_name
FROM ens_names
WHERE LENGTH(ens_name) = 7
ORDER BY last_refreshed DESC
LIMIT 1;

But Transpose is returning an error: Missing Chain Target. To help fix this, you can provide as input to the Query Assistant:

Here's a SQL query that's broken, because it says missing chain target.  
Please fix it for me:

SELECT ens_name
FROM ens_names
WHERE LENGTH(ens_name) = 7
ORDER BY last_refreshed DESC
LIMIT 1;

The Query Assistant outputs

SELECT ens_name
FROM ethereum.ens_names
WHERE LENGTH(ens_name) = 7
ORDER BY last_refreshed DESC
LIMIT 1;

Which runs perfectly!

Out of order responses

Now that you've got your working query to find the most recently updated 3 character ENS name, you realize what you actually wanted is the oldest updated 3 character names.

Provide as input to the Query Assistant:

Here's a SQL query that's broken, because it seems to be returning the newest results instead of the oldest.  Can you fix it for me?

SELECT ens_name
FROM ethereum.ens_names
WHERE LENGTH(ens_name) = 7
ORDER BY last_refreshed DESC 
LIMIT 1;

The Query Assistant returns

SELECT *
FROM ethereum.ens_names
WHERE LENGTH(ens_name) = 7
ORDER BY last_refreshed ASC
LIMIT 1;

Which does exactly what we want.

Tips and Tricks

Learn some strategies to get the most out of the Query Assistant

Got questions? Join our Discord

Discord is the primary home of the Transpose developer community. Join us to ask questions, share your work, and get help.