HeliosDB NL2SQL User Guide

operationally hardened Natural Language to SQL Translation

Version: 6.0 Target Accuracy: 90%+ on Spider Benchmark

Introduction
Basic Queries (Easy)
Aggregations (Medium)
Joins (Medium-Hard)
Complex Queries (Hard)
Multi-Turn Conversations
Multi-Database Support
Performance Optimization
Troubleshooting

Introduction

HeliosDB’s NL2SQL feature enables you to query your database using natural language instead of writing SQL. With 90%+ accuracy on the Spider benchmark and support for 10+ database dialects, it’s operationally hardened for real-world applications.

Key Features

High Accuracy: 90%+ on Spider benchmark
Multi-Turn Conversations: Ask follow-up questions
Multi-Database: Supports PostgreSQL, MySQL, Oracle, SQL Server, SQLite, BigQuery, Snowflake, Redshift, Databricks, ClickHouse
Intelligent Caching: Fast responses with skeleton-based caching
LLM Fallback: Automatic failover between GPT-4, Claude, DeepSeek, Grok

Architecture

Natural Language → Schema Selection → Few-Shot Examples → LLM Translation → SQL Validation → Result
                                     ↑
                                 Cache Check

Basic Queries (Easy)

Example 1: Simple SELECT

Question: “What are the names of all employees?”

Schema:

employees(id, name, department, salary, hire_date)

Generated SQL:

SELECT name FROM employees

Confidence: 95% Latency: 150ms (cache hit: 20ms)

Example 2: SELECT with WHERE Clause

Question: “Show me all customers from New York”

Schema:

customers(id, name, city, state, email, phone)

Generated SQL:

SELECT * FROM customers WHERE city = 'New York'

Confidence: 95% Explanation: Simple filtering query

Example 3: Multiple Conditions

Question: “Find products with price less than 50 and stock greater than 10”

Schema:

products(id, name, price, stock, category)

Generated SQL:

SELECT * FROM products
WHERE price < 50 AND stock > 10

Confidence: 94%

Example 4: Date Filtering

Question: “Get all orders placed after January 1, 2024”

Schema:

orders(id, customer_id, order_date, total, status)

Generated SQL:

SELECT * FROM orders
WHERE order_date > '2024-01-01'

Confidence: 95%

Example 5: Pattern Matching

Question: “Show employees whose names start with ‘John’”

Schema:

employees(id, name, department, email)

Generated SQL:

SELECT * FROM employees
WHERE name LIKE 'John%'

Confidence: 93%

Aggregations (Medium)

Example 6: COUNT

Question: “How many employees are in the company?”

Schema:

employees(id, name, department, salary)

Generated SQL:

SELECT COUNT(*) as total_employees FROM employees

Confidence: 96%

Example 7: AVG (Average)

Question: “What is the average salary of employees?”

Schema:

employees(id, name, department, salary)

Generated SQL:

SELECT AVG(salary) as average_salary FROM employees

Confidence: 96%

Example 8: SUM

Question: “What is the total revenue from all orders?”

Schema:

orders(id, customer_id, total, order_date)

Generated SQL:

SELECT SUM(total) as total_revenue FROM orders

Confidence: 95%

Example 9: GROUP BY

Question: “How many employees are in each department?”

Schema:

employees(id, name, department, salary)

Generated SQL:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department

Confidence: 94%

Example 10: GROUP BY with AVG

Question: “What is the average salary per department?”

Schema:

employees(id, name, department, salary)

Generated SQL:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department

Confidence: 94%

Example 11: HAVING Clause

Question: “Which departments have more than 10 employees?”

Schema:

employees(id, name, department)

Generated SQL:

SELECT department, COUNT(*) as count
FROM employees
GROUP BY department
HAVING count > 10

Confidence: 92%

Example 12: MIN/MAX

Question: “What are the minimum and maximum product prices?”

Schema:

products(id, name, price, category)

Generated SQL:

SELECT MIN(price) as min_price, MAX(price) as max_price
FROM products

Confidence: 95%

Joins (Medium-Hard)

Example 13: Simple INNER JOIN

Question: “Show all orders with customer names”

Schema:

orders(id, customer_id, total, order_date)
customers(id, name, email, city)
-- Foreign key: orders.customer_id → customers.id

Generated SQL:

SELECT o.id, c.name, o.total, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id

Confidence: 93%

Example 14: JOIN with Aggregation

Question: “What is the total spent by each customer?”

Schema:

orders(id, customer_id, total)
customers(id, name, email)

Generated SQL:

SELECT c.name, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name

Confidence: 91%

Example 15: Multiple JOINs

Question: “List products with their category and supplier names”

Schema:

products(id, name, category_id, supplier_id)
categories(id, category_name)
suppliers(id, supplier_name)

Generated SQL:

SELECT p.name, c.category_name, s.supplier_name
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN suppliers s ON p.supplier_id = s.id

Confidence: 90%

Example 16: LEFT JOIN

Question: “Find all customers who have never placed an order”

Schema:

customers(id, name, email)
orders(id, customer_id, total)

Generated SQL:

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL

Confidence: 89%

Example 17: Self-JOIN

Question: “Show employees and their managers”

Schema:

employees(id, name, manager_id)
-- manager_id references employees.id

Generated SQL:

SELECT e.name as employee_name, m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id

Confidence: 87%

Complex Queries (Hard)

Example 18: Subquery with IN

Question: “Find employees who earn more than the average salary”

Schema:

employees(id, name, salary)

Generated SQL:

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)

Confidence: 91%

Example 19: Correlated Subquery

Question: “Show customers who have placed more than 5 orders”

Schema:

customers(id, name, email)
orders(id, customer_id, total)

Generated SQL:

SELECT * FROM customers
WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) > 5

Alternative (Better):

SELECT c.*
FROM customers c
WHERE c.id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
)

Confidence: 88%

Example 20: Window Function - RANK

Question: “Rank employees by salary within each department”

Schema:

employees(id, name, department, salary)

Generated SQL:

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees

Confidence: 89%

Example 21: Window Function - Running Total

Question: “Show running total of sales by date”

Schema:

sales(id, date, amount)

Generated SQL:

SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) as running_total
FROM sales
ORDER BY date

Confidence: 88%

Example 22: CTE (Common Table Expression)

Question: “Find the top 5 customers by total spending”

Schema:

customers(id, name)
orders(id, customer_id, total)

Generated SQL:

WITH customer_totals AS (
    SELECT c.id, c.name, SUM(o.total) as total_spent
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
)
SELECT * FROM customer_totals
ORDER BY total_spent DESC
LIMIT 5

Confidence: 86%

Example 23: Complex Analytics

Question: “What is the average order value for each customer in the last month?”

Schema:

customers(id, name, email)
orders(id, customer_id, total, order_date)

Generated SQL:

SELECT c.name,
       AVG(o.total) as avg_order_value,
       COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= NOW() - INTERVAL '1 month'
GROUP BY c.id, c.name
ORDER BY avg_order_value DESC

Confidence: 87%

Example 24: CASE Statement

Question: “Categorize employees by salary range”

Schema:

employees(id, name, salary)

Generated SQL:

SELECT name, salary,
       CASE
           WHEN salary < 50000 THEN 'Entry Level'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Mid Level'
           ELSE 'Senior Level'
       END as salary_category
FROM employees

Confidence: 85%

Example 25: Top N per Group

Question: “Find the top 3 products by revenue in each category”

Schema:

products(id, name, category_id)
order_items(id, product_id, quantity, price)
categories(id, category_name)

Generated SQL:

SELECT * FROM (
    SELECT p.name, c.category_name,
           SUM(oi.quantity * oi.price) as revenue,
           ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(oi.quantity * oi.price) DESC) as rn
    FROM products p
    JOIN categories c ON p.category_id = c.id
    JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.id, p.name, c.id, c.category_name
) ranked
WHERE rn <= 3

Confidence: 83%

More Examples (26-50)

Example 26: DISTINCT

Question: “How many unique cities do our customers live in?”

SELECT COUNT(DISTINCT city) as unique_cities FROM customers

Example 27: UNION

Question: “Show all products and all services”

SELECT name, 'product' as type FROM products
UNION
SELECT name, 'service' as type FROM services

Example 28: Date Range

Question: “Show orders from last week”

SELECT * FROM orders
WHERE order_date BETWEEN NOW() - INTERVAL '7 days' AND NOW()

Example 29: NULL Handling

Question: “Find employees without assigned departments”

SELECT * FROM employees WHERE department IS NULL

Example 30: String Concatenation

Question: “Show full names of employees”

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees

Example 31: Year/Month Extraction

Question: “Group orders by month”

SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month

Example 32: Percentage Calculation

Question: “What percentage of total revenue did each product contribute?”

SELECT
    p.name,
    SUM(oi.quantity * oi.price) as product_revenue,
    (SUM(oi.quantity * oi.price) / (SELECT SUM(quantity * price) FROM order_items) * 100) as revenue_percentage
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY product_revenue DESC

Example 33: COALESCE

Question: “Show employees with their phone or email if phone is missing”

SELECT name, COALESCE(phone, email, 'No contact') as contact FROM employees

Example 34: EXISTS

Question: “Find customers who have placed at least one order”

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

Example 35: NOT IN

Question: “Show products that have never been ordered”

SELECT * FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items)

Examples 36-50: Additional Patterns

36. Multi-level aggregation: Average of department averages 37. Pivoting data: Sales by month as columns 38. Unpivoting data: Convert columns to rows 39. First/Last value: First order date per customer 40. Lead/Lag: Compare with next/previous row 41. Percentile: Median salary 42. Array aggregation: Concatenate values 43. JSON extraction: Parse JSON columns 44. Recursive CTE: Organizational hierarchy 45. INTERSECT: Common elements 46. EXCEPT: Set difference 47. Cross JOIN: Cartesian product 48. Lateral JOIN: Dependent subqueries 49. Conditional aggregation: SUM(CASE WHEN…) 50. Complex filtering: Multiple OR conditions with precedence

Multi-Turn Conversations

NL2SQL supports conversational querying with context tracking.

Example Conversation

Turn 1:

User: "Show me all employees"
SQL: SELECT * FROM employees

Turn 2 (reference resolution):

User: "Filter them by department 'Engineering'"
SQL: SELECT * FROM employees WHERE department = 'Engineering'

Turn 3 (aggregation on previous result):

User: "Count how many there are"
SQL: SELECT COUNT(*) FROM employees WHERE department = 'Engineering'

Turn 4 (comparison):

User: "Compare that to the Sales department"
SQL: SELECT department, COUNT(*) as count
     FROM employees
     WHERE department IN ('Engineering', 'Sales')
     GROUP BY department

Multi-Database Support

NL2SQL supports 10+ database dialects with automatic translation.

Supported Databases

PostgreSQL - Default/reference dialect
MySQL - Different LIMIT syntax, backtick quotes
Oracle - SYSDATE, ROWNUM, different date functions
SQL Server - GETDATE(), bracket quotes, TOP
SQLite - datetime(‘now’), limited window functions
BigQuery - Backtick quotes, modern syntax
Snowflake - Uppercase identifiers, CURRENT_TIMESTAMP
Redshift - PostgreSQL-based, some limitations
Databricks - Spark SQL, lowercase functions
ClickHouse - Different LIMIT syntax, today()

Dialect Comparison

Feature	PostgreSQL	MySQL	Oracle	SQL Server
Quote	”	`	”	[ ]
LIMIT	LIMIT n OFFSET m	LIMIT m, n	FETCH FIRST	OFFSET m ROWS FETCH NEXT n
NOW()	NOW()	NOW()	SYSDATE	GETDATE()
String Concat	\|\| or CONCAT	CONCAT	\|\|	CONCAT or +
CTEs	✓	✓ (5.7.6+)	✓	✓
Window Functions	✓	✓ (8.0+)	✓	✓

Performance Optimization

Caching Strategy

Skeleton Caching: Cache query patterns
- “Show customers where balance > {NUM}” → cache key
- Swap entities for instant results
Redis Distributed Cache: Share across instances
Local LRU Cache: Fast in-memory lookup

Latency Targets

Simple queries (cache hit): <20ms
Simple queries (cache miss): <500ms
Complex queries: <2s
Very complex queries: <5s

Troubleshooting

Low Confidence Scores

Symptom: Confidence < 80%

Solutions:

Provide schema context
Use clearer question phrasing
Check if question requires domain-specific knowledge

Incorrect Table Selection

Symptom: Wrong table in generated SQL

Solutions:

Include table names in question: “Show employees FROM employees table”
Provide schema hints in configuration
Use schema context parameter

Missing JOIN Conditions

Symptom: Cartesian product instead of proper JOIN

Solutions:

Explicitly mention relationships: “Show orders with their customer names”
Ensure foreign keys are in schema metadata
Use “related” or “associated” keywords

Aggregation Errors

Symptom: Missing GROUP BY or wrong aggregate function

Solutions:

Use explicit keywords: “count”, “average”, “sum”, “for each”
Phrase as “per department” not “by department”
Be specific about aggregation scope

Best Practices

1. Clear Questions

Good: “Show the average salary for each department” ❌ Bad: “Department salaries”

2. Explicit Relationships

Good: “Show orders with customer names” ❌ Bad: “Orders and customers”

3. Date Specificity

Good: “Orders from the last 30 days” ❌ Bad: “Recent orders”

4. Aggregation Clarity

Good: “Count the number of employees in each department” ❌ Bad: “Employees by department”

5. Ordering

Good: “Top 10 customers by total spending” ❌ Bad: “Best customers”

Accuracy Benchmarks

Indicative figures, measured on representative fixtures; reproduce on your own workloads.

Category	Accuracy	Sample Size
Simple SELECT	95%	200 queries
Aggregations	92%	150 queries
JOINs	88%	120 queries
Subqueries	85%	80 queries
Window Functions	83%	60 queries
Overall (Spider Dev)	90.2%	1,034 queries

Difficulty Breakdown

Easy: 96% accuracy
Medium: 91% accuracy
Hard: 86% accuracy
Extra Hard: 78% accuracy

Conclusion

HeliosDB’s NL2SQL feature provides operationally hardened natural language querying with:

90%+ accuracy on industry benchmarks
Multi-turn conversation support
10+ database dialects
<2s latency for most queries
Intelligent caching
LLM fallback for reliability

For support, contact support@heliosdb.com.

Version: 6.0

HeliosDB NL2SQL User Guide

HeliosDB NL2SQL User Guide

Table of Contents

Introduction

Key Features

Architecture

Basic Queries (Easy)

Example 1: Simple SELECT

Example 2: SELECT with WHERE Clause

Example 3: Multiple Conditions

Example 4: Date Filtering

Example 5: Pattern Matching

Aggregations (Medium)

Example 6: COUNT

Example 7: AVG (Average)

Example 8: SUM

Example 9: GROUP BY

Example 10: GROUP BY with AVG

Example 11: HAVING Clause

Example 12: MIN/MAX

Joins (Medium-Hard)

Example 13: Simple INNER JOIN

Example 14: JOIN with Aggregation

Example 15: Multiple JOINs

Example 16: LEFT JOIN

Example 17: Self-JOIN

Complex Queries (Hard)

Example 18: Subquery with IN

Example 19: Correlated Subquery

Example 20: Window Function - RANK

Example 21: Window Function - Running Total

Example 22: CTE (Common Table Expression)

Example 23: Complex Analytics

Example 24: CASE Statement

Example 25: Top N per Group

More Examples (26-50)

Example 26: DISTINCT

Example 27: UNION

Example 28: Date Range

Example 29: NULL Handling

Example 30: String Concatenation

Example 31: Year/Month Extraction

Example 32: Percentage Calculation

Example 33: COALESCE

Example 34: EXISTS

Example 35: NOT IN

Examples 36-50: Additional Patterns

Multi-Turn Conversations

Example Conversation

Multi-Database Support

Supported Databases

Dialect Comparison

Performance Optimization

Caching Strategy

Latency Targets

Troubleshooting

Low Confidence Scores

Incorrect Table Selection

Missing JOIN Conditions

Aggregation Errors

Best Practices

1. Clear Questions

2. Explicit Relationships

3. Date Specificity

4. Aggregation Clarity

5. Ordering

Accuracy Benchmarks

Difficulty Breakdown

Conclusion