Back

SQL sorcerer

Transform everyday language into SQL queries.

System
Transform the following natural language requests into valid SQL queries. Assume a database with the following tables and columns exists:

Customers:
- customer_id (INT, PRIMARY KEY)
- first_name (VARCHAR)
- last_name (VARCHAR)
- email (VARCHAR)
- phone (VARCHAR)
- address (VARCHAR)
- city (VARCHAR)
- state (VARCHAR)
- zip_code (VARCHAR)

Products:
- product_id (INT, PRIMARY KEY)
- product_name (VARCHAR)
- description (TEXT)
- category (VARCHAR)
- price (DECIMAL)
- stock_quantity (INT)

Orders:
- order_id (INT, PRIMARY KEY)
- customer_id (INT, FOREIGN KEY REFERENCES Customers)
- order_date (DATE)
- total_amount (DECIMAL)
- status (VARCHAR)

Order_Items:
- order_item_id (INT, PRIMARY KEY)
- order_id (INT, FOREIGN KEY REFERENCES Orders)
- product_id (INT, FOREIGN KEY REFERENCES Products)
- quantity (INT)
- price (DECIMAL)

Reviews:
- review_id (INT, PRIMARY KEY)
- product_id (INT, FOREIGN KEY REFERENCES Products)
- customer_id (INT, FOREIGN KEY REFERENCES Customers)
- rating (INT)
- comment (TEXT)
- review_date (DATE)

Employees:
- employee_id (INT, PRIMARY KEY)
- first_name (VARCHAR)
- last_name (VARCHAR)
- email (VARCHAR)
- phone (VARCHAR)
- hire_date (DATE)
- job_title (VARCHAR)
- department (VARCHAR)
- salary (DECIMAL)

Provide the SQL query that would retrieve the data based on the natural language request.
user
Get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders.

To get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders, you can use the following SQL query:

sql
SELECT c.customer_id, c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
LEFT JOIN Reviews r ON c.customer_id = r.customer_id
WHERE r.review_id IS NULL
GROUP BY c.customer_id, c.first_name, c.last_name;

Explanation:

  1. The query starts with the Customers table and joins it with the Orders table using the customer_id column to establish the relationship between customers and their orders.
  2. It then performs a left join with the Reviews table on the customer_id column to identify customers who have not provided any reviews.
  3. The WHERE clause filters out customers who have provided reviews by checking if the review_id is null, indicating they have not left any reviews.
  4. The GROUP BY clause groups the results by customer_id, first_name, and last_name to calculate the total amount spent for each customer.
  5. The SUM(o.total_amount) aggregates the total_amount column from the Orders table to calculate the total amount spent by each customer.

The result will include the customer_id, first_name, last_name, and the total_spent for each customer who has placed orders but has not provided any reviews.

sql
SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c 
JOIN Orders o ON c.customer_id = o.customer_id
LEFT JOIN Reviews r ON c.customer_id = r.customer_id
WHERE r.review_id IS NULL
GROUP BY c.customer_id

This query first joins the Customers and Orders tables to find all customers who have placed orders. It then left joins to the Reviews table and checks for null values to only include customers that do not have any reviews. The SUM() aggregates the total_amount for each customer, and the result is grouped by customer_id to avoid duplicate rows per customer. The select statement retrieves the first/last names of the customers, along with their total spend.

To get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders, you can use the following SQL query:

sql
SELECT c.customer_id, c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
LEFT JOIN Reviews r ON c.customer_id = r.customer_id
WHERE o.order_id IS NOT NULL AND r.review_id IS NULL
GROUP BY c.customer_id, c.first_name, c.last_name;

Here's how the query works:

  1. The FROM clause starts with the Customers table.
  2. The LEFT JOIN clause is used to join the Orders table with the Customers table based on the customer_id column. This will include all customers, even those who have not placed any orders.
  3. Another LEFT JOIN clause is used to join the Reviews table with the combined result from the previous join. This will include all customers, even those who have not provided any reviews.
  4. The WHERE clause filters the result to include only those customers who have placed orders (o.order_id IS NOT NULL) but have not provided any reviews (r.review_id IS NULL).
  5. The GROUP BY clause groups the rows by customer_id, first_name, and last_name.
  6. The SUM(o.total_amount) calculates the total amount spent by each customer on their orders, and the AS total_spent aliases the calculated column.

The query will return the customer_id, first_name, last_name, and total_spent columns for the customers who meet the specified criteria.

API request

python
import anthropic

  client = anthropic.Anthropic(
      # defaults to os.environ.get("ANTHROPIC_API_KEY")
      api_key="my_api_key",
  )
  message = client.messages.create(
      model="claude-3-opus-20240229",
      max_tokens=2000,
      temperature=0,
      system="Transform the following natural language requests into valid SQL queries. Assume a database with the following tables and columns exists:\n\nCustomers:\n- customer_id (INT, PRIMARY KEY)\n- first_name (VARCHAR)\n- last_name (VARCHAR)\n- email (VARCHAR)\n- phone (VARCHAR)\n- address (VARCHAR)\n- city (VARCHAR)\n- state (VARCHAR)\n- zip_code (VARCHAR)\n\nProducts:\n- product_id (INT, PRIMARY KEY)\n- product_name (VARCHAR)\n- description (TEXT)\n- category (VARCHAR)\n- price (DECIMAL)\n- stock_quantity (INT)\n\nOrders:\n- order_id (INT, PRIMARY KEY)\n- customer_id (INT, FOREIGN KEY REFERENCES Customers)\n- order_date (DATE)\n- total_amount (DECIMAL)\n- status (VARCHAR)\n\nOrder_Items:\n- order_item_id (INT, PRIMARY KEY)\n- order_id (INT, FOREIGN KEY REFERENCES Orders)\n- product_id (INT, FOREIGN KEY REFERENCES Products)\n- quantity (INT)\n- price (DECIMAL)\n\nReviews:\n- review_id (INT, PRIMARY KEY)\n- product_id (INT, FOREIGN KEY REFERENCES Products)\n- customer_id (INT, FOREIGN KEY REFERENCES Customers)\n- rating (INT)\n- comment (TEXT)\n- review_date (DATE)\n\nEmployees:\n- employee_id (INT, PRIMARY KEY)\n- first_name (VARCHAR)\n- last_name (VARCHAR)\n- email (VARCHAR)\n- phone (VARCHAR)\n- hire_date (DATE)\n- job_title (VARCHAR)\n- department (VARCHAR)\n- salary (DECIMAL)\n\nProvide the SQL query that would retrieve the data based on the natural language request.",
      messages=[{"role":"user","content":"Get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders."}]
  )
  print(message.content)
typescript
import Anthropic from "@anthropic-ai/sdk";

  const anthropic = new Anthropic({
      apiKey: "my_api_key", // defaults to process.env["ANTHROPIC_API_KEY"]
  });
  
  const msg = await anthropic.messages.create({
      model: "claude-3-opus-20240229",
      max_tokens: 2000,
      temperature: 0,
      system: "Transform the following natural language requests into valid SQL queries. Assume a database with the following tables and columns exists:\n\nCustomers:\n- customer_id (INT, PRIMARY KEY)\n- first_name (VARCHAR)\n- last_name (VARCHAR)\n- email (VARCHAR)\n- phone (VARCHAR)\n- address (VARCHAR)\n- city (VARCHAR)\n- state (VARCHAR)\n- zip_code (VARCHAR)\n\nProducts:\n- product_id (INT, PRIMARY KEY)\n- product_name (VARCHAR)\n- description (TEXT)\n- category (VARCHAR)\n- price (DECIMAL)\n- stock_quantity (INT)\n\nOrders:\n- order_id (INT, PRIMARY KEY)\n- customer_id (INT, FOREIGN KEY REFERENCES Customers)\n- order_date (DATE)\n- total_amount (DECIMAL)\n- status (VARCHAR)\n\nOrder_Items:\n- order_item_id (INT, PRIMARY KEY)\n- order_id (INT, FOREIGN KEY REFERENCES Orders)\n- product_id (INT, FOREIGN KEY REFERENCES Products)\n- quantity (INT)\n- price (DECIMAL)\n\nReviews:\n- review_id (INT, PRIMARY KEY)\n- product_id (INT, FOREIGN KEY REFERENCES Products)\n- customer_id (INT, FOREIGN KEY REFERENCES Customers)\n- rating (INT)\n- comment (TEXT)\n- review_date (DATE)\n\nEmployees:\n- employee_id (INT, PRIMARY KEY)\n- first_name (VARCHAR)\n- last_name (VARCHAR)\n- email (VARCHAR)\n- phone (VARCHAR)\n- hire_date (DATE)\n- job_title (VARCHAR)\n- department (VARCHAR)\n- salary (DECIMAL)\n\nProvide the SQL query that would retrieve the data based on the natural language request.",
      messages: [{"role":"user","content":"Get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders."}]
  });
  console.log(msg);