nateraw/defog-sqlcoder-7b-2 – Run with an API on Replicate

nateraw / defog-sqlcoder-7b-2

A capable large language model for natural language to SQL generation.

Cold

Public
21.3K runs
L40S
GitHub

Run with an API

Playground API Examples README Versions

Input

question

*string

Shift + Return to add a new line

Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.

table_metadata

*string

Shift + Return to add a new line

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);

CREATE TABLE customers (
   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
   name VARCHAR(50), -- Name of the customer
   address VARCHAR(100) -- Mailing address of the customer
);

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson 
  name VARCHAR(50), -- Name of the salesperson
  region VARCHAR(50) -- Geographic sales region 
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
  product_id INTEGER, -- ID of product sold
  customer_id INTEGER,  -- ID of customer who made purchase
  salesperson_id INTEGER, -- ID of salesperson who made the sale
  sale_date DATE, -- Date the sale occurred 
  quantity INTEGER -- Quantity of product sold
);

CREATE TABLE product_suppliers (
  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
  product_id INTEGER, -- Product ID supplied
  supply_price DECIMAL(10,2) -- Unit price charged by supplier
);

-- sales.product_id can be joined with products.product_id
-- sales.customer_id can be joined with customers.customer_id 
-- sales.salesperson_id can be joined with salespeople.salesperson_id
-- product_suppliers.product_id can be joined with products.product_idCREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);

CREATE TABLE customers (
   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
   name VARCHAR(50), -- Name of the customer
   address VARCHAR(100) -- Mailing address of the customer
);

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson 
  name VARCHAR(50), -- Name of the salesperson
  region VARCHAR(50) -- Geographic sales region 
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
  product_id INTEGER, -- ID of product sold
  customer_id INTEGER,  -- ID of customer who made purchase
  salesperson_id INTEGER, -- ID of salesperson who made the sale
  sale_date DATE, -- Date the sale occurred 
  quantity INTEGER -- Quantity of product sold
);

CREATE TABLE product_suppliers (
  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
  product_id INTEGER, -- Product ID supplied
  supply_price DECIMAL(10,2) -- Unit price charged by supplier
);

-- sales.product_id can be joined with products.product_id
-- sales.customer_id can be joined with customers.customer_id 
-- sales.salesperson_id can be joined with salespeople.salesperson_id
-- product_suppliers.product_id can be joined with products.product_id

max_new_tokens

integer

The maximum number of tokens the model should generate as output.

Default: 512

temperature

number

The value used to modulate the next token probabilities.

Default: 0.6

top_p

number

A probability threshold for generating the output. If < 1.0, only keep the top tokens with cumulative probability >= top_p (nucleus filtering). Nucleus filtering is described in Holtzman et al. (http://arxiv.org/abs/1904.09751).

Default: 0.9

top_k

integer

The number of highest probability tokens to consider for generating the output. If > 0, only keep the top k tokens with highest probability (top-k filtering).

Default: 50

presence_penalty

number

Presence penalty

Default: 0

frequency_penalty

number

Frequency penalty

Default: 0

prompt_template

string

Shift + Return to add a new line

### Task
Generate a SQL query to answer [QUESTION]{question}[/QUESTION]

### Instructions
- If you cannot answer the question with the available database schema, return 'I do not know'

### Database Schema
The query will run on a database with the following schema:
{table_metadata}

### Answer
Given the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]
[SQL]### Task
Generate a SQL query to answer [QUESTION]{question}[/QUESTION]

### Instructions
- If you cannot answer the question with the available database schema, return 'I do not know'

### Database Schema
The query will run on a database with the following schema:
{table_metadata}

### Answer
Given the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]
[SQL]

The template used to format the prompt. The input prompt is inserted into the template using the `{prompt}` placeholder.

Default: "### Task\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\n{table_metadata}\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\n[SQL]\n"

Run this model in Node.js with one line of code:

npx create-replicate --model=nateraw/defog-sqlcoder-7b-2

or set up a project from scratch

Install Replicate’s Node.js client library:

npm install replicate

Set the REPLICATE_API_TOKEN environment variable:

export REPLICATE_API_TOKEN=<paste-your-token-here>

Find your API token in your account settings.

Import and set up the client:

import Replicate from "replicate";

const replicate = new Replicate({
  auth: process.env.REPLICATE_API_TOKEN,
});

Run nateraw/defog-sqlcoder-7b-2 using Replicate’s API. Check out the model's schema for an overview of inputs and outputs.

const output = await replicate.run(
  "nateraw/defog-sqlcoder-7b-2:ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806",
  {
    input: {
      top_k: 50,
      top_p: 0.9,
      question: "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
      temperature: 0,
      max_new_tokens: 512,
      table_metadata: "CREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), -- Name of the product\n  price DECIMAL(10,2), -- Price of each unit of the product\n  quantity INTEGER  -- Current quantity in stock\n);\n\nCREATE TABLE customers (\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n   name VARCHAR(50), -- Name of the customer\n   address VARCHAR(100) -- Mailing address of the customer\n);\n\nCREATE TABLE salespeople (\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \n  name VARCHAR(50), -- Name of the salesperson\n  region VARCHAR(50) -- Geographic sales region \n);\n\nCREATE TABLE sales (\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n  product_id INTEGER, -- ID of product sold\n  customer_id INTEGER,  -- ID of customer who made purchase\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\n  sale_date DATE, -- Date the sale occurred \n  quantity INTEGER -- Quantity of product sold\n);\n\nCREATE TABLE product_suppliers (\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n  product_id INTEGER, -- Product ID supplied\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\n);\n\n-- sales.product_id can be joined with products.product_id\n-- sales.customer_id can be joined with customers.customer_id \n-- sales.salesperson_id can be joined with salespeople.salesperson_id\n-- product_suppliers.product_id can be joined with products.product_id",
      prompt_template: "### Task\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\n{table_metadata}\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\n[SQL]",
      presence_penalty: 0,
      frequency_penalty: 0
    }
  }
);

console.log(output);

To learn more, take a look at the guide on getting started with Node.js.

Install Replicate’s Python client library:

pip install replicate

Set the REPLICATE_API_TOKEN environment variable:

export REPLICATE_API_TOKEN=<paste-your-token-here>

Find your API token in your account settings.

Import the client:

import replicate

Run nateraw/defog-sqlcoder-7b-2 using Replicate’s API. Check out the model's schema for an overview of inputs and outputs.

output = replicate.run(
    "nateraw/defog-sqlcoder-7b-2:ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806",
    input={
        "top_k": 50,
        "top_p": 0.9,
        "question": "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
        "temperature": 0,
        "max_new_tokens": 512,
        "table_metadata": "CREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), -- Name of the product\n  price DECIMAL(10,2), -- Price of each unit of the product\n  quantity INTEGER  -- Current quantity in stock\n);\n\nCREATE TABLE customers (\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n   name VARCHAR(50), -- Name of the customer\n   address VARCHAR(100) -- Mailing address of the customer\n);\n\nCREATE TABLE salespeople (\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \n  name VARCHAR(50), -- Name of the salesperson\n  region VARCHAR(50) -- Geographic sales region \n);\n\nCREATE TABLE sales (\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n  product_id INTEGER, -- ID of product sold\n  customer_id INTEGER,  -- ID of customer who made purchase\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\n  sale_date DATE, -- Date the sale occurred \n  quantity INTEGER -- Quantity of product sold\n);\n\nCREATE TABLE product_suppliers (\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n  product_id INTEGER, -- Product ID supplied\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\n);\n\n-- sales.product_id can be joined with products.product_id\n-- sales.customer_id can be joined with customers.customer_id \n-- sales.salesperson_id can be joined with salespeople.salesperson_id\n-- product_suppliers.product_id can be joined with products.product_id",
        "prompt_template": "### Task\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\n{table_metadata}\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\n[SQL]",
        "presence_penalty": 0,
        "frequency_penalty": 0
    }
)

# The nateraw/defog-sqlcoder-7b-2 model can stream output as it's running.
# The predict method returns an iterator, and you can iterate over that output.
for item in output:
    # https://replicate.com/nateraw/defog-sqlcoder-7b-2/api#output-schema
    print(item, end="")

To learn more, take a look at the guide on getting started with Python.

Set the REPLICATE_API_TOKEN environment variable:

export REPLICATE_API_TOKEN=<paste-your-token-here>

Find your API token in your account settings.

Run nateraw/defog-sqlcoder-7b-2 using Replicate’s API. Check out the model's schema for an overview of inputs and outputs.

curl -s -X POST \
  -H "Authorization: Bearer $REPLICATE_API_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Prefer: wait" \
  -d $'{
    "version": "ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806",
    "input": {
      "top_k": 50,
      "top_p": 0.9,
      "question": "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
      "temperature": 0,
      "max_new_tokens": 512,
      "table_metadata": "CREATE TABLE products (\\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\\n  name VARCHAR(50), -- Name of the product\\n  price DECIMAL(10,2), -- Price of each unit of the product\\n  quantity INTEGER  -- Current quantity in stock\\n);\\n\\nCREATE TABLE customers (\\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\\n   name VARCHAR(50), -- Name of the customer\\n   address VARCHAR(100) -- Mailing address of the customer\\n);\\n\\nCREATE TABLE salespeople (\\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \\n  name VARCHAR(50), -- Name of the salesperson\\n  region VARCHAR(50) -- Geographic sales region \\n);\\n\\nCREATE TABLE sales (\\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\\n  product_id INTEGER, -- ID of product sold\\n  customer_id INTEGER,  -- ID of customer who made purchase\\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\\n  sale_date DATE, -- Date the sale occurred \\n  quantity INTEGER -- Quantity of product sold\\n);\\n\\nCREATE TABLE product_suppliers (\\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\\n  product_id INTEGER, -- Product ID supplied\\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\\n);\\n\\n-- sales.product_id can be joined with products.product_id\\n-- sales.customer_id can be joined with customers.customer_id \\n-- sales.salesperson_id can be joined with salespeople.salesperson_id\\n-- product_suppliers.product_id can be joined with products.product_id",
      "prompt_template": "### Task\\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\\n\\n### Instructions\\n- If you cannot answer the question with the available database schema, return \'I do not know\'\\n\\n### Database Schema\\nThe query will run on a database with the following schema:\\n{table_metadata}\\n\\n### Answer\\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\\n[SQL]",
      "presence_penalty": 0,
      "frequency_penalty": 0
    }
  }' \
  https://api.replicate.com/v1/predictions

To learn more, take a look at Replicate’s HTTP API reference docs.

You can run this model locally using Cog. First, install Cog:

brew install cog

If you don’t have Homebrew, there are other installation options available.

Run this to download the model and run it in your local environment:

cog predict r8.im/nateraw/defog-sqlcoder-7b-2@sha256:ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806 \
  -i 'top_k=50' \
  -i 'top_p=0.9' \
  -i 'question="Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two."' \
  -i 'temperature=0' \
  -i 'max_new_tokens=512' \
  -i $'table_metadata="CREATE TABLE products (\\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\\n  name VARCHAR(50), -- Name of the product\\n  price DECIMAL(10,2), -- Price of each unit of the product\\n  quantity INTEGER  -- Current quantity in stock\\n);\\n\\nCREATE TABLE customers (\\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\\n   name VARCHAR(50), -- Name of the customer\\n   address VARCHAR(100) -- Mailing address of the customer\\n);\\n\\nCREATE TABLE salespeople (\\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \\n  name VARCHAR(50), -- Name of the salesperson\\n  region VARCHAR(50) -- Geographic sales region \\n);\\n\\nCREATE TABLE sales (\\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\\n  product_id INTEGER, -- ID of product sold\\n  customer_id INTEGER,  -- ID of customer who made purchase\\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\\n  sale_date DATE, -- Date the sale occurred \\n  quantity INTEGER -- Quantity of product sold\\n);\\n\\nCREATE TABLE product_suppliers (\\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\\n  product_id INTEGER, -- Product ID supplied\\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\\n);\\n\\n-- sales.product_id can be joined with products.product_id\\n-- sales.customer_id can be joined with customers.customer_id \\n-- sales.salesperson_id can be joined with salespeople.salesperson_id\\n-- product_suppliers.product_id can be joined with products.product_id"' \
  -i $'prompt_template="### Task\\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\\n\\n### Instructions\\n- If you cannot answer the question with the available database schema, return \'I do not know\'\\n\\n### Database Schema\\nThe query will run on a database with the following schema:\\n{table_metadata}\\n\\n### Answer\\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\\n[SQL]"' \
  -i 'presence_penalty=0' \
  -i 'frequency_penalty=0'

To learn more, take a look at the Cog documentation.

Run this to download the model and run it in your local environment:

docker run -d -p 5000:5000 --gpus=all r8.im/nateraw/defog-sqlcoder-7b-2@sha256:ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806
curl -s -X POST \
  -H "Content-Type: application/json" \
  -d $'{
    "input": {
      "top_k": 50,
      "top_p": 0.9,
      "question": "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
      "temperature": 0,
      "max_new_tokens": 512,
      "table_metadata": "CREATE TABLE products (\\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\\n  name VARCHAR(50), -- Name of the product\\n  price DECIMAL(10,2), -- Price of each unit of the product\\n  quantity INTEGER  -- Current quantity in stock\\n);\\n\\nCREATE TABLE customers (\\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\\n   name VARCHAR(50), -- Name of the customer\\n   address VARCHAR(100) -- Mailing address of the customer\\n);\\n\\nCREATE TABLE salespeople (\\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \\n  name VARCHAR(50), -- Name of the salesperson\\n  region VARCHAR(50) -- Geographic sales region \\n);\\n\\nCREATE TABLE sales (\\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\\n  product_id INTEGER, -- ID of product sold\\n  customer_id INTEGER,  -- ID of customer who made purchase\\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\\n  sale_date DATE, -- Date the sale occurred \\n  quantity INTEGER -- Quantity of product sold\\n);\\n\\nCREATE TABLE product_suppliers (\\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\\n  product_id INTEGER, -- Product ID supplied\\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\\n);\\n\\n-- sales.product_id can be joined with products.product_id\\n-- sales.customer_id can be joined with customers.customer_id \\n-- sales.salesperson_id can be joined with salespeople.salesperson_id\\n-- product_suppliers.product_id can be joined with products.product_id",
      "prompt_template": "### Task\\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\\n\\n### Instructions\\n- If you cannot answer the question with the available database schema, return \'I do not know\'\\n\\n### Database Schema\\nThe query will run on a database with the following schema:\\n{table_metadata}\\n\\n### Answer\\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\\n[SQL]",
      "presence_penalty": 0,
      "frequency_penalty": 0
    }
  }' \
  http://localhost:5000/predictions

To learn more, take a look at the Cog documentation.

Output

SELECT c.city, SUM(s.quantity) AS total_sales, SUM(CASE WHEN c.city = 'New York' THEN s.quantity ELSE 0 END) - SUM(CASE WHEN c.city = 'San Francisco' THEN s.quantity ELSE 0 END) AS difference_in_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id GROUP BY c.city ORDER BY total_sales DESC NULLS LAST;

{
  "completed_at": "2024-02-11T03:18:31.215272Z",
  "created_at": "2024-02-11T03:18:28.589237Z",
  "data_removed": false,
  "error": null,
  "id": "enm65blbgntnxuhsewbxdy2qoq",
  "input": {
    "top_k": 50,
    "top_p": 0.9,
    "question": "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
    "temperature": 0,
    "max_new_tokens": 512,
    "table_metadata": "CREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), -- Name of the product\n  price DECIMAL(10,2), -- Price of each unit of the product\n  quantity INTEGER  -- Current quantity in stock\n);\n\nCREATE TABLE customers (\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n   name VARCHAR(50), -- Name of the customer\n   address VARCHAR(100) -- Mailing address of the customer\n);\n\nCREATE TABLE salespeople (\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \n  name VARCHAR(50), -- Name of the salesperson\n  region VARCHAR(50) -- Geographic sales region \n);\n\nCREATE TABLE sales (\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n  product_id INTEGER, -- ID of product sold\n  customer_id INTEGER,  -- ID of customer who made purchase\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\n  sale_date DATE, -- Date the sale occurred \n  quantity INTEGER -- Quantity of product sold\n);\n\nCREATE TABLE product_suppliers (\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n  product_id INTEGER, -- Product ID supplied\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\n);\n\n-- sales.product_id can be joined with products.product_id\n-- sales.customer_id can be joined with customers.customer_id \n-- sales.salesperson_id can be joined with salespeople.salesperson_id\n-- product_suppliers.product_id can be joined with products.product_id",
    "prompt_template": "### Task\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\n{table_metadata}\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\n[SQL]",
    "presence_penalty": 0,
    "frequency_penalty": 0
  },
  "logs": "INFO 02-11 03:18:28 async_llm_engine.py:431] Received request bdd2a2c68ac94d98bf8ecdd2fea93e0d: prompt: \"### Task\\nGenerate a SQL query to answer [QUESTION]Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.[/QUESTION]\\n\\n### Instructions\\n- If you cannot answer the question with the available database schema, return 'I do not know'\\n\\n### Database Schema\\nThe query will run on a database with the following schema:\\nCREATE TABLE products (\\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\\n  name VARCHAR(50), -- Name of the product\\n  price DECIMAL(10,2), -- Price of each unit of the product\\n  quantity INTEGER  -- Current quantity in stock\\n);\\n\\nCREATE TABLE customers (\\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\\n   name VARCHAR(50), -- Name of the customer\\n   address VARCHAR(100) -- Mailing address of the customer\\n);\\n\\nCREATE TABLE salespeople (\\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \\n  name VARCHAR(50), -- Name of the salesperson\\n  region VARCHAR(50) -- Geographic sales region \\n);\\n\\nCREATE TABLE sales (\\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\\n  product_id INTEGER, -- ID of product sold\\n  customer_id INTEGER,  -- ID of customer who made purchase\\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\\n  sale_date DATE, -- Date the sale occurred \\n  quantity INTEGER -- Quantity of product sold\\n);\\n\\nCREATE TABLE product_suppliers (\\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\\n  product_id INTEGER, -- Product ID supplied\\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\\n);\\n\\n-- sales.product_id can be joined with products.product_id\\n-- sales.customer_id can be joined with customers.customer_id \\n-- sales.salesperson_id can be joined with salespeople.salesperson_id\\n-- product_suppliers.product_id can be joined with products.product_id\\n\\n### Answer\\nGiven the database schema, here is the SQL query that answers [QUESTION]Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.[/QUESTION]\\n[SQL]\", prefix_pos: None,sampling params: SamplingParams(n=1, best_of=1, presence_penalty=0.0, frequency_penalty=0.0, repetition_penalty=1.0, temperature=0.0, top_p=1.0, top_k=-1, min_p=0.0, use_beam_search=False, length_penalty=1.0, early_stopping=False, stop=['</s>'], stop_token_ids=[], include_stop_str_in_output=False, ignore_eos=False, max_tokens=512, logprobs=None, prompt_logprobs=None, skip_special_tokens=True, spaces_between_special_tokens=True), prompt token ids: None, lora_request: None.\nINFO 02-11 03:18:28 llm_engine.py:877] Avg prompt throughput: 0.0 tokens/s, Avg generation throughput: 5.2 tokens/s, Running: 1 reqs, Swapped: 0 reqs, Pending: 0 reqs, GPU KV cache usage: 1.0%, CPU KV cache usage: 0.0%\nINFO 02-11 03:18:31 async_llm_engine.py:110] Finished request bdd2a2c68ac94d98bf8ecdd2fea93e0d.\ngeneration took 2.463s",
  "metrics": {
    "predict_time": 2.588424,
    "total_time": 2.626035
  },
  "output": [
    " SELECT",
    " c",
    ".",
    "city",
    ",",
    " SUM",
    "(",
    "s",
    ".",
    "quantity",
    ")",
    " AS",
    " total",
    "_",
    "s",
    "ales",
    ",",
    " SUM",
    "(",
    "CASE",
    " WHEN",
    " c",
    ".",
    "city",
    " =",
    " '",
    "New",
    " York",
    "'",
    " THEN",
    " s",
    ".",
    "quantity",
    " ELSE",
    " ",
    "0",
    " END",
    ")",
    " -",
    " SUM",
    "(",
    "CASE",
    " WHEN",
    " c",
    ".",
    "city",
    " =",
    " '",
    "San",
    " Francisco",
    "'",
    " THEN",
    " s",
    ".",
    "quantity",
    " ELSE",
    " ",
    "0",
    " END",
    ")",
    " AS",
    " difference",
    "_",
    "in",
    "_",
    "s",
    "ales",
    " FROM",
    " sales",
    " s",
    " JOIN",
    " customers",
    " c",
    " ON",
    " s",
    ".",
    "customer",
    "_",
    "id",
    " =",
    " c",
    ".",
    "customer",
    "_",
    "id",
    " GROUP",
    " BY",
    " c",
    ".",
    "city",
    " ORDER",
    " BY",
    " total",
    "_",
    "s",
    "ales",
    " DESC",
    " NULL",
    "S",
    " LA",
    "ST",
    ";",
    ""
  ],
  "started_at": "2024-02-11T03:18:28.626848Z",
  "status": "succeeded",
  "urls": {
    "stream": "https://streaming-api.svc.us.c.replicate.net/v1/streams/cqx75gzmdt4seyemtjrhvc7futhse6ltxok2obugalrrl6vao2wa",
    "get": "https://api.replicate.com/v1/predictions/enm65blbgntnxuhsewbxdy2qoq",
    "cancel": "https://api.replicate.com/v1/predictions/enm65blbgntnxuhsewbxdy2qoq/cancel"
  },
  "version": "ced935b577fb52644d933f77e2ff8902744e4c58a2f50023b3a1db80b7a75806"
}

Generated in

2.6 seconds

Tweak it Report

INFO 02-11 03:18:28 async_llm_engine.py:431] Received request bdd2a2c68ac94d98bf8ecdd2fea93e0d: prompt: "### Task\nGenerate a SQL query to answer [QUESTION]Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\nCREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), -- Name of the product\n  price DECIMAL(10,2), -- Price of each unit of the product\n  quantity INTEGER  -- Current quantity in stock\n);\n\nCREATE TABLE customers (\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n   name VARCHAR(50), -- Name of the customer\n   address VARCHAR(100) -- Mailing address of the customer\n);\n\nCREATE TABLE salespeople (\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \n  name VARCHAR(50), -- Name of the salesperson\n  region VARCHAR(50) -- Geographic sales region \n);\n\nCREATE TABLE sales (\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n  product_id INTEGER, -- ID of product sold\n  customer_id INTEGER,  -- ID of customer who made purchase\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\n  sale_date DATE, -- Date the sale occurred \n  quantity INTEGER -- Quantity of product sold\n);\n\nCREATE TABLE product_suppliers (\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n  product_id INTEGER, -- Product ID supplied\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\n);\n\n-- sales.product_id can be joined with products.product_id\n-- sales.customer_id can be joined with customers.customer_id \n-- sales.salesperson_id can be joined with salespeople.salesperson_id\n-- product_suppliers.product_id can be joined with products.product_id\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.[/QUESTION]\n[SQL]", prefix_pos: None,sampling params: SamplingParams(n=1, best_of=1, presence_penalty=0.0, frequency_penalty=0.0, repetition_penalty=1.0, temperature=0.0, top_p=1.0, top_k=-1, min_p=0.0, use_beam_search=False, length_penalty=1.0, early_stopping=False, stop=['</s>'], stop_token_ids=[], include_stop_str_in_output=False, ignore_eos=False, max_tokens=512, logprobs=None, prompt_logprobs=None, skip_special_tokens=True, spaces_between_special_tokens=True), prompt token ids: None, lora_request: None.
INFO 02-11 03:18:28 llm_engine.py:877] Avg prompt throughput: 0.0 tokens/s, Avg generation throughput: 5.2 tokens/s, Running: 1 reqs, Swapped: 0 reqs, Pending: 0 reqs, GPU KV cache usage: 1.0%, CPU KV cache usage: 0.0%
INFO 02-11 03:18:31 async_llm_engine.py:110] Finished request bdd2a2c68ac94d98bf8ecdd2fea93e0d.
generation took 2.463s

Run time and cost

This model costs approximately $0.069 to run on Replicate, or 14 runs per $1, but this varies depending on your inputs. It is also open source and you can run it on your own computer with Docker.

This model runs on Nvidia L40S GPU hardware. Predictions typically complete within 72 seconds. The predict time for this model varies significantly based on the inputs.

Readme

See full model card here