ChatGPT for Querying Data

Table of Contents

1. Challenge

   Set up the GPT API

   1. Before delving into the lab you will need to set up the GPT API. At the top of the lab, copy the API Key.
   2. Once you have copied the key, you will open the ChatGPT_for_Querying_Data notebook.
      • You will be working in this notebook for the remainder of the lab.
   3. In its second cell replace <TODO_ENTER_API_KEY_HERE> with your API key. (Make sure you keep the API key a string.)
   4. Run all the cells in the corresponding section. (Run them in order, up to and including the ask_ai() call.)
      
      The system prompt is already set up and specified for SQLite, which is what will work with your mock data. You could easily swap out SQLite for any other type of database language and the principles of the lab would stay the same.

   Note:

   • You at any time can see the ChatGPT_for_Querying_Data_Solutions notebook for one set of solutions and their results.
   • The prompts will produce slightly different queries, even with the exact same prompt. In testing, the queries were always correct, but could have benign differences like different alias names, or extra or fewer non-critical columns.
2. Challenge

   Create SQL data

   Run the cells in the corresponding section to create the data.

   The synthetic data created will be example data containing customers, products, and orders. This is a very basic schema of tables containing information related to customer sales. Table displays are provided for readability of these datasets, but when prompting the model you will directly provide a prompting schema.

3. Challenge

   ## Create the prompt for the database schema

   Run the cells in the corresponding section to create the prompt for the database schema, and to create a simple helper function.

   ---

   The prompt is very similar to a basic schema with a few added features:

   • It has additional text describing what to expect in each table.
   • Also, it contains a Relationships section which provides information on how tables should be joined.
   • A the end, it has some logic-based formulas to ensure you can keep your prompts high-level and consistent.

   You could enhance this schema by providing a couple examples of data-formating to enhance the models accuracy when generating queries.

   Finally there is a helper function called run to make your sql read a bit more naturally when running, instead of using the pandas call. Bonus: Lots of information was defined that will then feed into your prompts. This allows the AI to make better queries, roughly mimicking a RAG (Retrieval Augmented Generation). You will be augmenting the generation to meet your database schema by providing the structure of the database in each prompt. Ideally, you could put this into your system prompt, allowing for dynamic, easy query updates when using a specific AI for creating SQL prompts.

4. Challenge

   ## Create and run GPT-assisted queries

   Grab five orders

   Do the following in the corresponding sub-section in the notebook (Grab five orders in this case)

   1. Add the following in as the argument to ask_ai: f"can you give me a query that only returns 5 orders from this database schema {Database_schema}"
      • Feel free to experiment a bit with other prompts!
   2. Copy the resulting output.
      • First extract just the SQL query from the output.
      • You'll then need to also format it, replacing any \n with actual newlines. (For this one, after extraction there very likley won't be any \n characters, but there will be in subsequent queries.)
      • As mentioned in Step 1, you can view the solution notebook if needed.
   3. Paste the formatted output into the run call.
      • Paste it within the """"s.

   Note: You will perform these types of actions for each section here!

   ---

   This query is a good start, as it's simple, returning five orders from the order table. There are a couple key things to keep in mind:

   • Make sure you limit the scope of your request. The word only helps ensure this.
   • The {database_schema} bit, which in this case is the entire prompt string you defined earlier, ensures the model understands the database structure.

   Prompting an AI model is an iterative process, so if you run into any issues throughout these queries, make sure to revise your input to the model.

   • Output will vary a bit, but during testing, they were always correct, as per the note at the end of the first Step in this lab.

   ---

   Group products, calculate revenue, and order descending

   (Do the following in the corresponding sub-section in the notebook.)

   1. Perform actions similar to what was done in the first section.

      Determine what argument to add to ask_ai to perform the following query:

      • List the revenue. This is calculated by quantity * price.
      • The query should group by product_name.
      • Order by revenue from highest to lowest.
      • Tip: But, remember you defined revenue already in your database scheme, along with joins (via Relationships). So, your prompt doesn't need to specify such granular things.

   Note: Keep in mind the following for this and the remaining sections:

   • Include the {Database_schema} so the model understands your database structure.
   • As mentioned earlier, you can view the solution notebook if needed.

   ---

   Group by category and calculate profits

   1. Perform actions similar to what was done earlier.

      Determine what argument to add to ask_ai to perform the following query:

      • Prompt the model for the profits by category.
      • For each category, the query should return total profit.
      • Tip: LLM's or large language models such as GPT work best when explicitly talked to like a person. Try phrasing your needs for prompts like you're asking a junior data analyst.

   Feel free to experiment by adding quantity, price, or cost.

   ---

   Calculate revenue by month

   1. Perform actions similar to what was done earlier.

      Determine what argument to add to ask_ai to perform the following query:

      • Prompt the AI for a basic query that grabs revenue by month
      • Order it by month.

   --- ### Group by customer and calculate revenue

   1. Perform actions similar to what was done earlier.

      Determine what argument to add to ask_ai to perform the following query:

      • Prompt the model for revenue by customer
      • Order it from highest to lowest.

   This query will require all tables to be joined together, but the prompting doesn't need to specify this! The model already knows the relationships between all the tables from your Database_schema.

   ---