Writing prompts for Avanty
Since Avanty is using several artificial intelligence models to generate SQL, here are some tips on how to write great prompts to generate exactly the SQL query you want.
Generating a new SQL query
Be as precise as possible
It can be helpful to mention table names or column names in your prompt. For example, if you want to know how many users have verified their email address, you can write:
Number of "users" who have verified their email address, excluding users who have been deleted
Refer to table names similar to how they are written in the database
This can be helpful if you have many tables, and some of them have names that sound semantically similar to outsiders. For example, if you have a table called users and another table called customers, try to refer to them as users and customers in your prompt.
Total number of signups per month, for the last 12 months, calculated from the users table
Define business terms
Complex metrics such as "revenue" or "profit" can be defined in many different ways. Try to define them in your prompt, so that Avanty can generate the SQL query you want.
Total revenue for the last 12 months, i.e. sum of the amount column of "payments"
Editing an existing SQL query
When using the Avanty Edit feature, follow the same guidelines as above. Additionally, here are some tips to help you edit an existing SQL query.
Tell Avanty to fix an error message
If your current SQL query returns an error, you can tell Avanty to fix it by writing a prompt that describes the error message.
The current query returns the error 'column "amount" does not exist'. Please fix it.
Describe what the current result looks like, and what you want it to look like
If your current SQL query returns a result, but it's not the result you want, you can describe what the current result looks like, and what you want it to look like.
Add "total" as a column, which should be the sum of transactions made by signups from a given month
Bucket the "amount" column into 5 buckets, and show the number of transactions in each bucket
Order by the "amount" column descending
Join first names and last names to a single name