Divide-and-conquer prompt for LLM-based text-to-SQL conversion

1 . A non-transitory computer-readable medium configured to store computer logic having directions that, when executed, cause one or more processing devices to:

receive a query from a user interface, the query including one or more questions or commands pertaining to datasets stored in a relational database;

generate a prompt having instructions for a Large Language Model (LLM) to handle the query; and

provide the prompt, datasets, and query to the LLM with instructions to convert the query into Structure Query Language (SQL) code,

wherein, in response to determining that the query is complex, the prompt includes an instruction directing the LLM to:

break down the complex query into sub-questions including a plurality of sub-questions comprising at least one parent sub-question and at least two child sub-questions;

extract one or more cascading dependencies between the sub-questions by generating a dependency representation identifying, for each child sub-question, a parent result that the child sub-question depends on;

convert each sub-question into a corresponding sub-SQL script; and

combine the sub-SQL scripts in accordance with the one or more cascading dependencies to formulate a final SQL code that provides correct answers to the complex query, wherein the final SQL code is a single composed SQL program comprising (i) a first portion corresponding to the parent sub-question, (ii) one or more subsequent portions corresponding to the child sub-questions, and (iii) explicit result-passing in which one or more intermediate results produced by executing the first portion are materialized as one or more intermediate relations and referenced by the one or more subsequent portions.

2 . The non-transitory computer-readable medium of claim 1 , wherein the LLM is configured to apply the SQL code, formulated based on the sub-SQL scripts, to the relational database to obtain one or more answers to the complex query.

3 . The non-transitory computer-readable medium of claim 1 , wherein the query is related to a specific subject, and wherein the directions further cause the one or more processing devices to obtain a set of tables related to the specific subject from the relational database.

4 . The non-transitory computer-readable medium of claim 1 , wherein the directions further cause the one or more processing devices to determine if the query is complex.

5 . The non-transitory computer-readable medium of claim 4 , wherein, in response to determining that the query is not complex, the directions further cause the one or more processing devices to provide a simple prompt to the LLM.

6 . The non-transitory computer-readable medium of claim 4 , wherein, in response to determining that the query is complex, the directions further cause the one or more processing devices to add an instruction to the prompt to direct the LLM to adopt a divide-and-conquer strategy for handling the complex query, wherein the divide-and-conquer strategy is configured to direct the LLM to:

a) break down the complex query into the sub-questions,

b) extract the cascading dependencies from the sub-questions,

c) convert each sub-question into the corresponding sub-SQL script, and

d) logically combine the sub-SQL scripts based on the cascading dependencies to formulate a final SQL that will provide correct answers to the complex query.

7 . The non-transitory computer-readable medium of claim 1 , wherein the prompt includes intermediate reasoning steps to enable the LLM to handle a complex query.

8 . The non-transitory computer-readable medium of claim 1 , wherein the directions further cause the one or more processing devices to modify the prompt, when handling the complex query having the one or more cascading dependencies, to instruct the LLM to behave in one or more of the following ways: a) act as expert for translating the query into the SQL code, b) follow a step by step approach, and c) use a Chain of Thought (CoT) technique.

9 . A system comprising:

a processing device; and

memory configured to store a text-to-SQL conversion guidance module having directions that, when executed, enable the processing device to:

receive a query from a user interface, the query including one or more questions or commands pertaining to datasets stored in a relational database,

generate a prompt having instructions for a Large Language Model (LLM) to handle the query, and

provide the prompt, datasets, and query to the LLM with instructions to convert the query into Structure Query Language (SQL) code,

wherein, in response to determining that the query is complex, the prompt includes an instruction directing the LLM to:

break down the complex query into sub-questions including a plurality of sub-questions comprising at least one parent sub-question and at least two child sub-questions;

extract one or more cascading dependencies between the sub-questions by generating a dependency representation identifying, for each child sub-question, a parent result that the child sub-question depends on;

convert each sub-question into a corresponding sub-SQL script; and

combine the sub-SQL scripts in accordance with the one or more cascading dependencies to formulate a final SQL code that provides correct answers to the complex query, wherein the final SQL code is a single composed SQL program comprising (i) a first portion corresponding to the parent sub-question, (ii) one or more subsequent portions corresponding to the child sub-questions, and (iii) explicit result-passing in which one or more intermediate results produced by executing the first portion are materialized as one or more intermediate relations and referenced by the one or more subsequent portions.

10 . The system of claim 9 , wherein the LLM is configured to apply the SQL code, formulated based on the sub-SQL scripts, to the relational database to obtain one or more answers to the complex query.

11 . The system of claim 9 , wherein the query is related to a specific subject, and wherein the directions further enable the processing device to obtain a set of tables related to the specific subject from the relational database.

12 . The system of claim 9 , wherein the directions further enable the processing device to determine if the query is complex.

13 . The system of claim 12 , wherein, in response to determining that the query is not complex, the directions further enable the processing device to provide a simple prompt to the LLM.

14 . The system of claim 12 , wherein, in response to determining that the query is complex, the directions further enable the processing device to add an instruction to the prompt to direct the LLM to adopt a divide-and-conquer strategy for handling the complex query.

15 . The system of claim 14 , wherein the divide-and-conquer strategy is configured to direct the LLM to:

a) break down the complex query into the sub-questions,

b) extract the cascading dependencies from the sub-questions,

c) convert each sub-question into the corresponding sub-SQL script, and

d) logically combine the sub-SQL scripts based on the cascading dependencies to formulate a final SQL that will provide correct answers to the complex query.

16 . The system of claim 9 , wherein the system is a prompt generator configured to provide the prompt to the LLM.

17 . A method comprising the steps of:

receiving a query from a user interface, the query including one or more questions or commands pertaining to datasets stored in a relational database,

generating a prompt having instructions for a Large Language Model (LLM) to handle the query, and

providing the prompt, datasets, and query to the LLM with instructions to convert the query into Structure Query Language (SQL) code,

wherein, in response to determining that the query is complex, the prompt includes an instruction directing the LLM to:

break down the complex query into sub-questions including a plurality of sub-questions comprising at least one parent sub-question and at least two child sub-questions;

extract one or more cascading dependencies between the sub-questions by generating a dependency representation identifying, for each child sub-question, a parent result that the child sub-question depends on;

convert each sub-question into a corresponding sub-SQL script; and

combine the sub-SQL scripts in accordance with the one or more cascading dependencies to formulate a final SQL code that provides correct answers to the complex query, wherein the final SQL code is a single composed SQL program comprising (i) a first portion corresponding to the parent sub-question, (ii) one or more subsequent portions corresponding to the child sub-questions, and (iii) explicit result-passing in which one or more intermediate its produced by executing the first portion are materialized as one or more intermediate relations and referenced by the one or more subsequent portions.

18 . The method of claim 17 , wherein the prompt includes intermediate reasoning steps to enable the LLM to handle a complex query.

19 . The non-transitory computer-readable medium of claim 1 , wherein the formulating of the SQL code is based on the one or more cascading dependencies, such that a result of one sub-SQL script serves as an input to one or more subsequent sub-SQL scripts, wherein the result is materialized as an intermediate relation within the final SQL code and the one or more subsequent sub-SQL scripts reference the intermediate relation to supply one or more filter values or join keys.

20 . The non-transitory computer-readable medium of claim 1 , wherein the directions further cause the one or more processing devices to iteratively test and refine the prompt for handling the complex query having the one or more cascading dependencies, based on one or more answers generated from the SQL code, wherein the iterative testing and refinement is further based on user feedback, provided through the user interface, indicating whether the one or more answers are accurate, wherein iteratively testing comprises: (i) executing the final SQL code against the relational database to obtain the one or more answers, (ii) presenting the one or more answers via the user interface, and (iii) receiving, via the user interface, the user feedback indicating accurate or inaccurate; and wherein iteratively refining comprises, in response to the user feedback indicating inaccurate, modifying the prompt to (a) revise at least one of the sub-questions or the dependency links and (b) regenerate at least one corresponding sub-SQL script and the final SQL code.