SQL Generation

Turn plain-English questions into correct SQL against a schema. The skill behind analytics chat, natural-language dashboards, and 'just ask the database' interfaces.

Duration

Input Tokens

850

Output Tokens

421

Cost

$0.02

Context

Input

For each dealership, rank employees by total sales amount in descending order. In the results I want to see dealership_name, employee_name , total_sales and sales_rank

Expected output

SELECT d.name AS dealership_name, e.name AS employee_name, SUM(s.sale_price) AS total_sales, RANK() OVER (PARTITION BY d.branch_id ORDER BY SUM(s.sale_price) DESC) AS sales_rank FROM sales s JOIN employees e ON s.employee_id = e.employee_id JOIN dealerships d ON s.branch_id = d.branch_id GROUP BY d.name, e.name, d.branch_id;

Model output

SELECT
  d.name AS dealership_name,
  e.name AS employee_name,
  COALESCE(SUM(s.sale_price), 0) AS total_sales,
  RANK() OVER (
    PARTITION BY d.branch_id
    ORDER BY COALESCE(SUM(s.sale_price), 0) DESC
  ) AS sales_rank
FROM dealerships d
JOIN employees e
  ON e.branch_id = d.branch_id
LEFT JOIN sales s
  ON s.employee_id = e.employee_id
 AND s.branch_id = d.branch_id
GROUP BY
  d.branch_id,
  d.name,
  e.employee_id,
  e.name
ORDER BY
  d.name,
  sales_rank;