---
title: "HeliosDB NL2SQL User Guide"
---

# HeliosDB NL2SQL User Guide

**Natural Language to SQL Translation**

Target Accuracy: 90%+ on Spider Benchmark

---

## Table of Contents

1. [Introduction](#introduction)
2. [Basic Queries (Easy)](#basic-queries-easy)
3. [Aggregations (Medium)](#aggregations-medium)
4. [Joins (Medium-Hard)](#joins-medium-hard)
5. [Complex Queries (Hard)](#complex-queries-hard)
6. [Multi-Turn Conversations](#multi-turn-conversations)
7. [Multi-Database Support](#multi-database-support)
8. [Performance Optimization](#performance-optimization)
9. [Troubleshooting](#troubleshooting)

---

## Introduction

HeliosDB's NL2SQL feature enables you to query your database using natural language instead of writing SQL. It targets 90%+ accuracy on the Spider benchmark and supports 10+ database dialects.

### 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**:
```sql
employees(id, name, department, salary, hire_date)
```

**Generated SQL**:
```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**:
```sql
customers(id, name, city, state, email, phone)
```

**Generated SQL**:
```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**:
```sql
products(id, name, price, stock, category)
```

**Generated SQL**:
```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**:
```sql
orders(id, customer_id, order_date, total, status)
```

**Generated SQL**:
```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**:
```sql
employees(id, name, department, email)
```

**Generated SQL**:
```sql
SELECT * FROM employees
WHERE name LIKE 'John%'
```

**Confidence**: 93%

---

## Aggregations (Medium)

### Example 6: COUNT

**Question**: "How many employees are in the company?"

**Schema**:
```sql
employees(id, name, department, salary)
```

**Generated SQL**:
```sql
SELECT COUNT(*) as total_employees FROM employees
```

**Confidence**: 96%

---

### Example 7: AVG (Average)

**Question**: "What is the average salary of employees?"

**Schema**:
```sql
employees(id, name, department, salary)
```

**Generated SQL**:
```sql
SELECT AVG(salary) as average_salary FROM employees
```

**Confidence**: 96%

---

### Example 8: SUM

**Question**: "What is the total revenue from all orders?"

**Schema**:
```sql
orders(id, customer_id, total, order_date)
```

**Generated SQL**:
```sql
SELECT SUM(total) as total_revenue FROM orders
```

**Confidence**: 95%

---

### Example 9: GROUP BY

**Question**: "How many employees are in each department?"

**Schema**:
```sql
employees(id, name, department, salary)
```

**Generated SQL**:
```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**:
```sql
employees(id, name, department, salary)
```

**Generated SQL**:
```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**:
```sql
employees(id, name, department)
```

**Generated SQL**:
```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**:
```sql
products(id, name, price, category)
```

**Generated SQL**:
```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**:
```sql
orders(id, customer_id, total, order_date)
customers(id, name, email, city)
-- Foreign key: orders.customer_id → customers.id
```

**Generated SQL**:
```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**:
```sql
orders(id, customer_id, total)
customers(id, name, email)
```

**Generated SQL**:
```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**:
```sql
products(id, name, category_id, supplier_id)
categories(id, category_name)
suppliers(id, supplier_name)
```

**Generated SQL**:
```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**:
```sql
customers(id, name, email)
orders(id, customer_id, total)
```

**Generated SQL**:
```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**:
```sql
employees(id, name, manager_id)
-- manager_id references employees.id
```

**Generated SQL**:
```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**:
```sql
employees(id, name, salary)
```

**Generated SQL**:
```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**:
```sql
customers(id, name, email)
orders(id, customer_id, total)
```

**Generated SQL**:
```sql
SELECT * FROM customers
WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) > 5
```

**Alternative (Better)**:
```sql
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**:
```sql
employees(id, name, department, salary)
```

**Generated SQL**:
```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**:
```sql
sales(id, date, amount)
```

**Generated SQL**:
```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**:
```sql
customers(id, name)
orders(id, customer_id, total)
```

**Generated SQL**:
```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**:
```sql
customers(id, name, email)
orders(id, customer_id, total, order_date)
```

**Generated SQL**:
```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**:
```sql
employees(id, name, salary)
```

**Generated SQL**:
```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**:
```sql
products(id, name, category_id)
order_items(id, product_id, quantity, price)
categories(id, category_name)
```

**Generated SQL**:
```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?"

```sql
SELECT COUNT(DISTINCT city) as unique_cities FROM customers
```

---

### Example 27: UNION

**Question**: "Show all products and all services"

```sql
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"

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

---

### Example 29: NULL Handling

**Question**: "Find employees without assigned departments"

```sql
SELECT * FROM employees WHERE department IS NULL
```

---

### Example 30: String Concatenation

**Question**: "Show full names of employees"

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

---

### Example 31: Year/Month Extraction

**Question**: "Group orders by month"

```sql
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?"

```sql
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"

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

---

### Example 34: EXISTS

**Question**: "Find customers who have placed at least one order"

```sql
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"

```sql
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

1. **PostgreSQL** - Default/reference dialect
2. **MySQL** - Different LIMIT syntax, backtick quotes
3. **Oracle** - SYSDATE, ROWNUM, different date functions
4. **SQL Server** - GETDATE(), bracket quotes, TOP
5. **SQLite** - datetime('now'), limited window functions
6. **BigQuery** - Backtick quotes, modern syntax
7. **Snowflake** - Uppercase identifiers, CURRENT_TIMESTAMP
8. **Redshift** - PostgreSQL-based, some limitations
9. **Databricks** - Spark SQL, lowercase functions
10. **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

1. **Skeleton Caching**: Cache query patterns
   - "Show customers where balance > {NUM}" → cache key
   - Swap entities for instant results

2. **Redis Distributed Cache**: Share across instances
3. **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**:
1. Provide schema context
2. Use clearer question phrasing
3. Check if question requires domain-specific knowledge

### Incorrect Table Selection

**Symptom**: Wrong table in generated SQL

**Solutions**:
1. Include table names in question: "Show employees FROM employees table"
2. Provide schema hints in configuration
3. Use schema context parameter

### Missing JOIN Conditions

**Symptom**: Cartesian product instead of proper JOIN

**Solutions**:
1. Explicitly mention relationships: "Show orders with their customer names"
2. Ensure foreign keys are in schema metadata
3. Use "related" or "associated" keywords

### Aggregation Errors

**Symptom**: Missing GROUP BY or wrong aggregate function

**Solutions**:
1. Use explicit keywords: "count", "average", "sum", "for each"
2. Phrase as "per department" not "by department"
3. 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 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](mailto:support@heliosdb.com).

---