How do you select in-context examples for text-to-SQL prompts?

WHAT THIS TESTS: This question probes whether you understand in-context learning as a retrieval and composition problem rather than a random sampling exercise. The interviewer wants to see that you know demonstration selection directly impacts execution accuracy on structured generation tasks like text-to-SQL. Specifically, they are looking for awareness of syntactic structure, semantic coverage, and prompt layout.

A GOOD ANSWER COVERS: First, retrieval criteria. You should mention using the syntactic structure of the SQL query to retrieve candidate examples, not just natural language question similarity. Second, the diversity versus similarity trade-off. Pursue both: semantic similarity keeps the task distribution aligned, while query-pattern diversity ensures coverage of joins, aggregations, nested selects, and different schema layouts. Third, ordering. Place simpler examples earlier and more complex ones later to scaffold the model's reasoning; avoid putting the hardest demonstrations first because they can confuse the pattern matching. Fourth, schema anchoring. Augment examples with relevant database knowledge so the model understands table relationships and column types within the prompt context.

COMMON WRONG ANSWERS: A major red flag is claiming that random examples are sufficient or that more examples always beat better examples. Another is selecting purely on natural language embedding similarity without considering SQL syntax, which often retrieves semantically close but syntactically trivial cases. Ignoring the ordering entirely or front-loading the most complex demonstrations also signals inexperience. Finally, omitting schema context and assuming the model already knows the database structure is a critical gap.

LIKELY FOLLOW-UPS: The interviewer may ask how you would measure diversity, how many examples you would use before context length limits hurt performance, or how you would handle a schema the model has never seen. They might also probe whether you would fine-tune a retriever or use a static heuristic, and how you would evaluate prompt variants offline.

ONE CONCRETE EXAMPLE: Suppose the user asks for the average salary by department. A weak prompt might retrieve three examples that all do simple SELECT all columns from a single table. A strong prompt retrieves one simple SELECT, one GROUP BY aggregation, and one JOIN with aggregation, ordered from simple to complex, each paired with the relevant schema snippet. This covers the needed SQL pattern while keeping the task distribution representative.