nateraw/sqlcoder-70b-alpha – Run with an API on Replicate

nateraw / sqlcoder-70b-alpha

Cold

Public
577 runs
A100 (80GB)

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.

The question to answer with a SQL query.

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

The database schema to use when generating the SQL query.

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.7

do_sample

boolean

Whether or not to use sampling; otherwise use greedy decoding.

Default: true

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.95

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

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 before passing it to the model. For no template, you can set this to `{prompt}`.

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/sqlcoder-70b-alpha

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/sqlcoder-70b-alpha using Replicate’s API. Check out the model's schema for an overview of inputs and outputs.

const output = await replicate.run(
  "nateraw/sqlcoder-70b-alpha:d6cd1065e6982faeb224e9ccdd0811afd9e104623383268eeb2d7a5c7e7bd3b3",
  {
    input: {
      top_k: 50,
      top_p: 0.95,
      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.",
      do_sample: false,
      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]\n"
    }
  }
);
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/sqlcoder-70b-alpha using Replicate’s API. Check out the model's schema for an overview of inputs and outputs.

output = replicate.run(
    "nateraw/sqlcoder-70b-alpha:d6cd1065e6982faeb224e9ccdd0811afd9e104623383268eeb2d7a5c7e7bd3b3",
    input={
        "top_k": 50,
        "top_p": 0.95,
        "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.",
        "do_sample": False,
        "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]\n"
    }
)

# The nateraw/sqlcoder-70b-alpha 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/sqlcoder-70b-alpha/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/sqlcoder-70b-alpha 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": "d6cd1065e6982faeb224e9ccdd0811afd9e104623383268eeb2d7a5c7e7bd3b3",
    "input": {
      "top_k": 50,
      "top_p": 0.95,
      "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.",
      "do_sample": false,
      "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]\\n"
    }
  }' \
  https://api.replicate.com/v1/predictions

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

Output

SELECT c.address, SUM(s.quantity) AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id GROUP BY c.address ORDER BY total_sales DESC NULLS LAST;

{
  "completed_at": "2024-02-08T08:39:13.884136Z",
  "created_at": "2024-02-08T08:39:06.743839Z",
  "data_removed": false,
  "error": null,
  "id": "tgpwvi3bakqvfx2atw7mxbftqm",
  "input": {
    "top_k": 50,
    "top_p": 0.95,
    "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.",
    "do_sample": false,
    "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]\n"
  },
  "logs": "=== Formatted Prompt ===\n### 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### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n### Database Schema\nThe query will run on a database with the following schema:\nCREATE TABLE products (\nproduct_id INTEGER PRIMARY KEY, -- Unique ID for each product\nname VARCHAR(50), -- Name of the product\nprice DECIMAL(10,2), -- Price of each unit of the product\nquantity INTEGER  -- Current quantity in stock\n);\nCREATE TABLE customers (\ncustomer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\nname VARCHAR(50), -- Name of the customer\naddress VARCHAR(100) -- Mailing address of the customer\n);\nCREATE TABLE salespeople (\nsalesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson\nname VARCHAR(50), -- Name of the salesperson\nregion VARCHAR(50) -- Geographic sales region\n);\nCREATE TABLE sales (\nsale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\nproduct_id INTEGER, -- ID of product sold\ncustomer_id INTEGER,  -- ID of customer who made purchase\nsalesperson_id INTEGER, -- ID of salesperson who made the sale\nsale_date DATE, -- Date the sale occurred\nquantity INTEGER -- Quantity of product sold\n);\nCREATE TABLE product_suppliers (\nsupplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\nproduct_id INTEGER, -- Product ID supplied\nsupply_price DECIMAL(10,2) -- Unit price charged by supplier\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### 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]\n========================\nSetting `pad_token_id` to `eos_token_id`:2 for open-end generation.",
  "metrics": {
    "predict_time": 7.137386,
    "total_time": 7.140297
  },
  "output": [
    "",
    "SELECT ",
    "",
    "",
    "",
    "c.address, ",
    "",
    "",
    "",
    "",
    "",
    "SUM(s.quantity) ",
    "AS ",
    "",
    "",
    "",
    "total_sales ",
    "FROM ",
    "sales ",
    "s ",
    "JOIN ",
    "customers ",
    "c ",
    "ON ",
    "",
    "",
    "",
    "",
    "s.customer_id ",
    "= ",
    "",
    "",
    "",
    "",
    "c.customer_id ",
    "GROUP ",
    "BY ",
    "",
    "",
    "c.address ",
    "ORDER ",
    "BY ",
    "",
    "",
    "",
    "total_sales ",
    "DESC ",
    "",
    "NULLS ",
    "",
    "",
    "",
    "LAST;"
  ],
  "started_at": "2024-02-08T08:39:06.746750Z",
  "status": "succeeded",
  "urls": {
    "stream": "https://streaming-api.svc.us.c.replicate.net/v1/streams/aofcgneajn7hr5fvb4trnffuyaumdn4kvr5ds7esjaqzat434evq",
    "get": "https://api.replicate.com/v1/predictions/tgpwvi3bakqvfx2atw7mxbftqm",
    "cancel": "https://api.replicate.com/v1/predictions/tgpwvi3bakqvfx2atw7mxbftqm/cancel"
  },
  "version": "d6cd1065e6982faeb224e9ccdd0811afd9e104623383268eeb2d7a5c7e7bd3b3"
}

Generated in

7.1 seconds

Tweak it Report

=== Formatted Prompt ===
### Task
Generate 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]
### 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:
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_id
### Answer
Given 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]
[SQL]
========================
Setting `pad_token_id` to `eos_token_id`:2 for open-end generation.

Run time and cost

This model costs approximately $0.029 to run on Replicate, or 34 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 A100 (80GB) GPU hardware. Predictions typically complete within 21 seconds.

Readme

This model doesn't have a readme.