Consider this dialog between a user (me) and an LLM. I am guiding the LLM through a process to generate a synthetic database, populate it with synthetic data, query that data, convert the queries to SQL, and then establish keywords to be used to validate future queries to see if they match the expected values. In some cases, I am copying JSON data produced by the LLM into a Python program so that I can query the database and generate the resulting data. In other cases, I am iterating on the LLM output to fix errors in SQL code. I do this by running the query in python, collecting the error and feeding it back to the LLM, along with the original SQL code, asking the LLM to fix it. This may need to be repeated up to 5 times per query.

Your task is to write a Python program that will follow the flow described in this dialog and call the LLM with my API key. Your output must be python code. 

Hint: The program will need to call the LLM in a sequence, just like the dialog below. Sometimes, the program will need to iterate on responses until a SQL query executes against the database successfully, or 5 times, whichever happens sooner.


Dialog:



User: Help me design a realistic database for a demand side platform (DSP) in the ad space. Consider security and access control. Write sql statements to create the 10-20 essential tables in the database. Add sql comments to explain the purpose of each table and a comment for each column. Specify the table CREATE statements in the correct order, so that foreign-key relationships are respected. Start with tables that have no foreign-key relationships. At the end, add a comment section to explain the schema to a technical reviewer.

Your response should consist of executable sql code and sql comments only, compatible with mysql. 

Bot: Answer

User: Tag each table above as extra small (XS), small (S), medium (M), large (L) and extra-large (XL) based on how granular it is. For example: A table with no foreign keys will be extra-small (XS). A table with a many-to-one foreign-key relationship to an extra-small table will be tagged small (S). A table with a many-to-one foreign-key relationship to a small table will be tagged medium (M) etc. A table with a many-to-one foreign-key relationship to an extra-large table will also be tagged extra-large (XL). Use no other tags besides XS, S, M, L and XL. Your response should be table names and tags only, for example:
table1: XS
table2: XL

Bot: Answer

User: Please create sample data for each table. Make the data as realistic as you can, given what you know about demand side platforms (DSP) and advertising. 
For extra-small tables, create 2-5 rows
For small tables, create 10-15 rows
For medium tables, create 20-30 rows
Skip large and extra-large tables for now

Your response should consist of sql statements and sql comments only. Double check that they are compatible with the schema you created earlier. Specify the inserts in the correct order, so that foreign-key constraints are respected. Also consider any other constraints specified in the schema above, such as enumerations.

Bot: Answer

User: Let’s continue generating sample data. Focus on large tables only. Skip tables tagged with other sizes. For tables tagged large (L), create 60-80 rows per table. Before you start, develop a strategy and some criteria for making the data you generate as realistic as possible. For example, pick realistic dates and timestamps for an active system. Today is September 24, 2024.

Double check that the inserts are compatible with the schema you created earlier. Respect foreign-key relationships by checking them against the inserts you made earlier, to tables tagged XS, S and M. Also consider any other constraints specified in the schema above, such as enumerations.

Your response should consist of sql statements and sql comments only. 


Bot: Answer

User: Double check the inserts above. Are all foreign keys respected? Your response should consist of executable sql statements and sql comments only. 

Bot: Answer

User: Let’s continue generating sample data. for extra-large tables only. Create 200 rows per extra-large table. Instead of listing out all the inserts, write sql code that will create the inserts. 

Before you start, develop a strategy and some criteria for making the data you generate as realistic as possible given what you know about DSP and advertising. For example, pick realistic dates and timestamps for an active system. Today is September 24, 2024.

Your response should be executable sql code and sql comments only. Double check that the inserts are compatible with the schema you created earlier. Specify them in the correct order, so that foreign-key constraints are respected.

Bot: Answer

User: You are a domain expert in DSP. Critique the sql code below separately for each table. Is it realistic? Any flaws? For example, no XL table should have less than 10 rows. Increase the number of rows in other XL tables as necessary to make this more realistic.

Please improve the code below to fix any issues you find. Delete previous rows as necessary. Explain your work. Your response should be executable sql code and sql comments only.

Bot: Answer

User: Act as an analyst that is using this DSP. Write a question you might ask about this data, where the answer is quantitative and in the database. Phrase the question as the analyst would. Pick questions with non-blank, non-zero answers based on the data you generated above. Next to the question, include the security context relating to the analyst. The security context consists of tables and IDs relating to the user that are required to restrict query results to only those that the analyst can access.  The role must fit as closely as possible to the analyst description above.

Repeat the instruction above 10 times, for a total of 10 questions. Each time choose a specific user and role described in the database. 

Write the questions in JSON format. For example: 
[
{
  "question": "What is the total number of impressions, clicks, and conversions for each of my campaigns?",
  "security_context": [
    {
      "table": "users",
      "id_column": "user_id",
      "id_value": 3
    }],
…
]

Bot: Answer

User: For each of these 10 questions, write the SQL query that would produce the correct answer. Consider the security context in the WHERE clause. Add comments to explain how you built the query. Make sure the query is compatible with the schema you created earlier in this conversation. If the database does not contain enough data to answer the question, please say what data is missing.
Your response should be in JSON, and should merge the queries into the previous JSON. Do not include newline characters in the sql. For example:
[
  {
    "question": "first question…?",
    "advertiser_id": "1”,
    “expected_sql”: “SELECT … FROM …”,
  },
  {
    "question": "second question…?",
    " advertiser_id ": "2",
    “expected_sql”: “”,
    “error”: “Cannot answer this question because the database is missing …”,
  },

]

Bot: Answer

User: You are a SQL expert. Critique the SQL statements above. Is there any way to simplify them and make them more efficient without changing the results? Please update the JSON.

User: *Repeats the below for each statement that shows an error in the JSON* 
Format and fix this sql query to match the schema above.
SQL Code: …
Error: …
Respond with the revised sql query and sql comments only.

Bot: Answer

User: *Run the following Python program. This program runs queries against the database created, brings back results into the JSON.*

Start program:

import pymysql
import json
import decimal

# Load the JSON data from a file or string
# 'questions-sql.json' was produced by the LLM in a previous step.
with open('questions-sql.json', 'r') as f:
    data = json.load(f)

# Connect to the database
conn = pymysql.connect(
    host='localhost',
    user='django_user',
    password='password',
    database='toric',
    port=3307,
    cursorclass=pymysql.cursors.DictCursor  # Use DictCursor to get results as dictionaries
)
cursor = conn.cursor()

# Process each question in the data
for item in data:
    question = item['question']
    expected_sql = item.get('expected_sql')
    print(f"Processing question: {question}")
    
    # Skip if there's no SQL query
    if not expected_sql:
        item['error'] = "No SQL query provided."
        continue
    
    try:
        # Execute the SQL query
        cursor.execute(expected_sql)
        results = cursor.fetchall()
        
        # Add the results to the item
        item['expected_data'] = results
    except pymysql.MySQLError as err:
        item['error'] = f"Database error: {err}"
    except Exception as e:
        item['error'] = f"Error: {e}"

# Close the database connection
cursor.close()
conn.close()

# Function to handle non-serializable objects during JSON serialization
def default_serializer(obj):
    if isinstance(obj, decimal.Decimal):
        return float(obj)
    elif isinstance(obj, datetime.datetime):
        return obj.isoformat()
    elif isinstance(obj, datetime.date):
        return obj.isoformat()
    else:
        raise TypeError(f'Object of type {obj.__class__.__name__} is not JSON serializable')

# Write the updated data back to a JSON file or print it
# Save to 'output_data.json'
with open('output_data.json', 'w') as f:
    json.dump(data, f, indent=2, default=default_serializer)

print('Saved results to output_data.json')

End program

Python outputs a JSON file

User: Process the JSON file above. For each item in the list, synthesize an answer to the question based on the expected_data key. The answer should be succinct. Numbers in the answer should match numbers in expected_data. The answer should be relevant to the question. You may use bullet points in the answer. Add the answer as an "expected_answer" key in the JSON. Your response should be the resulting JSON only.

Bot: Answer

User: I'm developing an LLM evaluation where the JSON above is the golden dataset. For each entry in the JSON list, review expected_data. It represents the dataset retrieved from the database. If it represents only a single row of data (column names do not repeat) then review the expected_answer and pull out a list of keywords that can be used to determine whether the actual_answer is correct. Add the keyword list into the JSON. 
On the other hand, if expected_data contains more than 1 data row, create an empty keywords list.
For example:
[
 {
      "question": "What is the total spend and number of impressions for our 'Acme Fall Promotion' campaign?",
      "security_context": [
        {
          "table": "users",
          "id_column": "user_id",
          "id_value": 4
        },
        {
          "table": "campaigns",
          "id_column": "advertiser_id",
          "id_value": 1
        }
      ],
      "expected_sql": "SELECT SUM(b.bid_amount) AS total_spend, COUNT(DISTINCT i.impression_id) AS impressions FROM campaigns c JOIN creatives cr ON c.campaign_id = cr.campaign_id JOIN bids b ON cr.creative_id = b.creative_id LEFT JOIN impressions i ON b.bid_id = i.bid_id WHERE c.advertiser_id = 1 AND c.name = 'Acme Fall Promotion';",
      "comments": "Used LEFT JOIN for impressions to include bids that may not have resulted in impressions (though in this case we focus on impressions). Simplified joins and ensured accurate counting by using COUNT(DISTINCT i.impression_id).",
      "expected_data": [
        {
          "total_spend": 2.6,
          "impressions": 0
        }
      ],
      "expected_answer": "The 'Acme Fall Promotion' campaign has a total spend of $2.60 and received 0 impressions.",
      “expected_keywords”: [”$2.60”,”0”]
    }, 
    {
      "question": "What is the total number of impressions, clicks, and conversions for each of my campaigns?",
      "security_context": [
        {
          "table": "users",
          "id_column": "user_id",
          "id_value": 3
        },
        {
          "table": "campaigns",
          "id_column": "advertiser_id",
          "id_value": 1
        }
      ],
      "expected_sql": "SELECT c.campaign_id, c.name AS campaign_name, IFNULL(imp.impressions, 0) AS impressions, IFNULL(clk.clicks, 0) AS clicks, IFNULL(conv.conversions, 0) AS conversions FROM campaigns c LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT i.impression_id) AS impressions FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id GROUP BY cr.campaign_id ) imp ON c.campaign_id = imp.campaign_id LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT cl.click_id) AS clicks FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id JOIN clicks cl ON i.impression_id = cl.impression_id GROUP BY cr.campaign_id ) clk ON c.campaign_id = clk.campaign_id LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT cv.conversion_id) AS conversions FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id JOIN conversions cv ON i.impression_id = cv.impression_id GROUP BY cr.campaign_id ) conv ON c.campaign_id = conv.campaign_id WHERE c.advertiser_id = 1;",
      "comments": "Simplified the query by calculating impressions, clicks, and conversions separately in subqueries to avoid data duplication caused by joining multiple tables. This improves efficiency and ensures accurate counts.",
      "expected_data": [
        {
          "campaign_id": 1,
          "campaign_name": "Acme Summer Sale",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 2,
          "campaign_name": "Acme Fall Promotion",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 3,
          "campaign_name": "Acme Winter Specials",
          "impressions": 2,
          "clicks": 1,
          "conversions": 2
        },
        {
          "campaign_id": 4,
          "campaign_name": "Acme Anniversary Campaign",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 5,
          "campaign_name": "Acme Special Event",
          "impressions": 2,
          "clicks": 1,
          "conversions": 2
        }
      ],
      "expected_answer": "Here are the total impressions, clicks, and conversions for each of your campaigns:\n\n- 'Acme Summer Sale': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Fall Promotion': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Winter Specials': 2 impressions, 1 click, 2 conversions.\n- 'Acme Anniversary Campaign': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Special Event': 2 impressions, 1 click, 2 conversions.",
     “expected_keywords”: []
    },

…


]

Bot: Final Answer looks like this:
[
{
      "question": "What is the total number of impressions, clicks, and conversions for each of my campaigns?",
      "security_context": [
        {
          "table": "users",
          "id_column": "user_id",
          "id_value": 3
        },
        {
          "table": "campaigns",
          "id_column": "advertiser_id",
          "id_value": 1
        }
      ],
      "expected_sql": "SELECT c.campaign_id, c.name AS campaign_name, IFNULL(imp.impressions, 0) AS impressions, IFNULL(clk.clicks, 0) AS clicks, IFNULL(conv.conversions, 0) AS conversions FROM campaigns c LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT i.impression_id) AS impressions FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id GROUP BY cr.campaign_id ) imp ON c.campaign_id = imp.campaign_id LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT cl.click_id) AS clicks FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id JOIN clicks cl ON i.impression_id = cl.impression_id GROUP BY cr.campaign_id ) clk ON c.campaign_id = clk.campaign_id LEFT JOIN ( SELECT cr.campaign_id, COUNT(DISTINCT cv.conversion_id) AS conversions FROM creatives cr JOIN bids b ON cr.creative_id = b.creative_id JOIN impressions i ON b.bid_id = i.bid_id JOIN conversions cv ON i.impression_id = cv.impression_id GROUP BY cr.campaign_id ) conv ON c.campaign_id = conv.campaign_id WHERE c.advertiser_id = 1;",
      "expected_sql_desc": "Simplified the query by calculating impressions, clicks, and conversions separately in subqueries to avoid data duplication caused by joining multiple tables. This improves efficiency and ensures accurate counts.",
      "expected_data": [
        {
          "campaign_id": 1,
          "campaign_name": "Acme Summer Sale",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 2,
          "campaign_name": "Acme Fall Promotion",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 3,
          "campaign_name": "Acme Winter Specials",
          "impressions": 2,
          "clicks": 1,
          "conversions": 2
        },
        {
          "campaign_id": 4,
          "campaign_name": "Acme Anniversary Campaign",
          "impressions": 0,
          "clicks": 0,
          "conversions": 0
        },
        {
          "campaign_id": 5,
          "campaign_name": "Acme Special Event",
          "impressions": 2,
          "clicks": 1,
          "conversions": 2
        }
      ],
      "expected_answer": "Here are the total impressions, clicks, and conversions for each of your campaigns:\n\n- 'Acme Summer Sale': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Fall Promotion': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Winter Specials': 2 impressions, 1 click, 2 conversions.\n- 'Acme Anniversary Campaign': 0 impressions, 0 clicks, 0 conversions.\n- 'Acme Special Event': 2 impressions, 1 click, 2 conversions.",
      "expected_keywords": []
    },
    {
      "question": "What is the total spend and number of impressions for our 'Acme Fall Promotion' campaign?",
      "security_context": [
        {
          "table": "users",
          "id_column": "user_id",
          "id_value": 4
        },
        {
          "table": "campaigns",
          "id_column": [
            "advertiser_id",
            "name"
          ],
          "id_value": [
            1,
            "Acme Fall Promotion"
          ]
        }
      ],
      "expected_sql": "SELECT SUM(b.bid_amount) AS total_spend, COUNT(DISTINCT i.impression_id) AS impressions FROM campaigns c JOIN creatives cr ON c.campaign_id = cr.campaign_id JOIN bids b ON cr.creative_id = b.creative_id LEFT JOIN impressions i ON b.bid_id = i.bid_id WHERE c.advertiser_id = 1 AND c.name = 'Acme Fall Promotion';",
      "expected_sql_desc": "Used LEFT JOIN for impressions to include bids that may not have resulted in impressions (though in this case we focus on impressions). Simplified joins and ensured accurate counting by using COUNT(DISTINCT i.impression_id).",
      "expected_data": [
        {
          "total_spend": 2.6,
          "impressions": 0
        }
      ],
      "expected_answer": "The 'Acme Fall Promotion' campaign has a total spend of $2.60 and received 0 impressions.",
      "expected_keywords": [
        "$2.60",
        "0 impressions"
      ]
    },
…
]