Vertigo Coding - Text to SQL Updated: Mar 1

Task Overview 👀
Workflow and Guidelines 🪜
How to test the model response query?
Linters
Additional rules
Common errors
Example prompts
Questions/TODOs

Task Overview 👀
The goal of the task is to:

1. Create a clear prompt in natural language that asks for specific information or an
analysis related to a provided database that makes the LLM model fail to provide the
correct response.
2. Write the query that provides a clean, efficient, and correct response to the prompt.

Workflow and Guidelines 🪜

1. Understand the database that you will be working with & set up the task
a. Database name
b. Database Schema Diagram: This diagram displays the available tables in the
database and their relationships.
2. Write the prompt
a. Select the Task Type you’ll use for your prompt in the dropdown menu. The
task type provides additional information about the type of question that you
should ask in the prompt.

📕 Check the guide to Task Types to learn more about the different task types.
 🌟 Keep things varied and don’t select the same task type for every single task.

b. The prompt you create must be relevant to the tables provided. It must also
comply with 2 conditions:
i. Answerable: The prompt must ask for something that can be answered
via a query result, and that is non ambiguous.
ii. Non Empty: The response that answers the prompt should be a non
empty output (query should return at least 1 row)

🌟 When working with date ranges or other filters, check the table
population script to make sure that your query will return data

3. Verify that the model’s response to your prompt fails

a. Submit your prompt and save it to receive the model's response. Ensure the
response aligns with the assigned Severity, which represents the expected
"degree of incorrectness."
b. In the provided IDE, test the model response, aiming for a response failure at
the assigned severity level or greater. Complexity increases the likelihood of
failure. If the response's failure does not match the assigned severity, adjust
your prompt and retest. Continue iterating until the failure meets or exceeds the
assigned severity.

Video example on how to redo a prompt:

📕 Check the guide to model failures to identify if your model failure meets the
minimum level.

c. Once the model fails in the assigned severity (or higher), click “Save Changes”.
d. You’ll be asked to explain all the issues you found in the model response and
click “Save Changes”
 4. Fix or rewrite the response
a. Then you will need to fix or rewrite the model’s response so that it answers the
prompt correctly

🌟 When working with date ranges or other filters, you may need to slightly adjust
the filters in order to return results from the query.

b. Make sure to:

i. Align your task with this rubric. Reviewers will consult it to assess your
responses. Adherence to the rubric is key to achieving higher scores.
5. Submit your task

Prompt Requirement Checklist

🌟 The following checklist will help you avoid the most common mistakes when writing your
prompts. Make sure to go through it each time before submitting.

The prompt should be clear and precise, avoiding any vagueness in the request,
Clarity
and should define concepts that are not widely recognized.
Write the prompt in natural language, as if you are posing a question to LLM
Natural Language without prior knowledge of SQL. Avoid merely translating SQL operations into
text.
The prompt should not request the use of the current time or date; similarly, the
Current Date Reference
query should not make use of the current date, time, timestamp, etc.
The prompt should specify exactly which fields should be returned by the query.
Prompt Return Value Precision The prompt should be clear if any concatenation with "||" is required (eg. full
name)
The prompt should be well-formatted, documented, presented, and written in
Styling/Grammar
perfect grammar.
The prompt should cause the provided model to fail in its response attempt (by
Model Failure 'fail,' we mean that the model responds incorrectly to your prompt). The more
complex the prompt is, the more likely the model will fail.
Be explicit if you want to limit If you want your query to LIMIT or ORDER BYthen the prompt must directly ask
or order your response for the results to be limited or sorted.

Good Example Prompts (DO NOT COPY)

Identify the zip codes where the average time to clear an incident exceeds 30 days for each year. Exclude
years with less than 50 incidents and rank these zip codes based on the average clearance time in
 descending order, showing only the top 10 zip codes for each year
Calculate the number of male and female patients that were hospitalized, between the last recorded date and
200 days prior to it. Aggregate the results at a products_role level. Produce these results only for VITAMIN at
the brand level. And return the result in string format.
Retrieve the latitude and the date one month prior to the installation date for the 10 most recently installed
bike-share stations.

SQL Coding Checklist

🌟 The following checklist will help you avoid the most common mistakes when writing your
response. Make sure to go through it each time before submitting.

Instruction Following The SQL query should return data requested in the prompt precisely.
- Choose the right type of join function (INNER, LEFT, RIGHT, FULL, …)
- Ensure joining with the right set of columns and conditions
JOINs
- Consider possible 1-to-many or many-to-many relationship when joining two
tables
- Choose when to filter thoughtfully, between using WHERE or ON clause if
Filter
there is a join
Query - Ensure JOIN does not add undesired data which would skew AVG calculation
Accuracy Aggregation - COUNT DISTINCT occurrence when data could contain duplicates
- Ensure data type is numeric before numeric aggregation like MIN, MAX, AVG
- Consider the presence of Null values in columns that are neither primary nor
Null Values foreign keys
- Check if you need to filter out Null values to get precise results
- Ensure data is CAST to correct type, especially when dealing with string or
Data Type
datetime data
Compilation The SQL query must compile in the sphere engine IDE with no error.
- Use CTE or subquery to compress repeated code snippet or to enhance code
CTE / Subquery
readability
- Avoid obvious computational inefficiency like querying one data repeatedly in
Coding Style Computation Efficiency
multiple places
- Use table aliases to accurately reference columns in each table when there is
Table Alias
ambiguity
How to test the model response query?
1. Open the Workspace and select the [Link] file
2. Paste the query you want to test.
3. Click the "Run" button to execute the query and see the results.

Additional rules
It is important to strictly follow our standards and rules, as we maintain a zero-tolerance policy
for cheating, spamming, or low-quality contributions. Violating any of these guidelines could
result in removal from the project. This includes:

1. Do not use LLM Tools:

- Generating prompts or explanations using an LLM tool is strictly prohibited.
2. Gibberish in Prompts:
- Writing gibberish (e.g. Write a qheheiur wdnewk) in the prompts or explanations.
3. Effortless prompts.
- Submissions that lack genuine effort, such as overly simplistic prompts or
explanations, are not acceptable.
4. Repeated Prompts:
- Submitting the same prompts repeatedly is against our guidelines and is
grounds for disciplinary action.