Using Few-Shot Prompting to Improve Complex SQL Query Generation Accuracy

Zero-shot prompting usually fails when your database schema has weird naming conventions or complex business logic that isn't explicitly documented in the column names. I spent the last month fighting with Cursor's Composer trying to get it to write recursive CTEs for a hierarchical category tree, and it kept hallucinating join conditions. The fix wasn't a better "system prompt," but moving to a structured few-shot approach within a .cursorrules file.

The core issue is that LLMs treat SQL as a translation task, but complex queries are actually a logic task. If you give the AI five examples of how you actually handle "active users" (which in my DB involves checking three different status flags and a timestamp), it stops guessing and starts mimicking.

Here is the setup I'm using to force higher accuracy:

The "Golden Set" Strategy
I created a dedicated markdown file called sql_patterns.md that acts as a living library of complex queries. Instead of just pasting these into the chat, I reference this file in my .cursorrules so the AI always has the context.

## Complex Query Patterns
**Pattern: Monthly Recurring Revenue (MRR)**
Goal: Calculate MRR excluding trial periods.
Correct Logic: Join `subscriptions` with `plans`, filter `status = 'active'`, and exclude `trial_end > now()`.
Example:
SELECT sum(p.price) FROM subscriptions s JOIN plans p ON s.plan_id = p.id WHERE s.status = 'active' AND s.trial_end < NOW();

Implementing the Prompt Loop
When I need a new, complex query, I don't just ask for the code. I use a "Reference -> Draft -> Verify" loop. I'll prompt Claude 3.5 Sonnet like this:

Using the logic patterns in @sql_patterns.md, write a query to find the top 5 customers by spend in Q3. Ensure you use the same filtering logic for 'completed orders' as seen in the MRR example.

The Productivity Gain
The biggest jump in accuracy comes from providing "Negative Examples." I started adding a section to my patterns file called "Common Pitfalls." For instance, I told the AI: "Do not use COUNT(DISTINCT user_id) when COUNT(id) suffices for performance reasons on the orders table."

By providing 3-5 high-quality pairs of (Natural Language Requirement → Valid SQL), I've seen the "hallucination rate" on joins drop significantly.

Gotchas to Watch For
Token Bloat: If your few-shot examples are too long, you'll eat through your context window and the AI might start ignoring the end of your prompt. Keep examples lean.
Schema Drift: The second I rename a column in a migration, my few-shot examples become "poison." I've had to manually update sql_patterns.md to prevent the AI from suggesting deprecated columns.
Overfitting: Sometimes the AI mimics the structure of the example too closely, even when a different approach is more efficient. I usually tell it: "Use the logic from the example, but optimize the execution plan for the current table size."

For anyone struggling with SQL accuracy, stop trying to describe your schema in the prompt. Just show the AI three queries that actually work and let it extrapolate.