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.
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
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
customers in your prompt.
Total number of signups per month, for the last 12 months, calculated from the users table
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"
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.
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.
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