1. Introduction

AI-assisted data development is reshaping the way we write traditional SQL. Current mainstream approaches can be broadly divided into two categories: one is the end-to-end mode that directly generates raw SQL; the other is the layered mode that generates structured intermediate scripts which are then compiled. The former is easy to get started with but difficult to master for complex business scenarios; the latter adds a layer of abstraction but provides engineering-grade guarantees of auditability, debuggability, and reproducibility.

The combined approach adopted in this document is: Trae (ByteDance's AI programming tool) serves as the"brain,"responsible for understanding requirements, clarifying ambiguities, and outputting SQLazy step-by-step scripts (.nspl); the SQLazy dedicated IDE serves as the"execution layer," responsible for syntax validation, step-by-step debugging, and cross-database compilation. Together, they form a closed loop of AI planning + human review + deterministic engine execution.

We selected four real-world cases, from easy to hard, covering typical scenarios such as statistical aggregation, multi-table merging, cross-subgroup filling, and amount splitting. The complete process from requirement input to validation passing is demonstrated, with emphasis on documenting the errors encountered and the reasoning behind corrections.

2. Tool Collaboration Mechanism

2.1 Trae's Role and Capabilities

Trae plays three key roles in this workflow:

1. Automatic Loading of Project Knowledge Base

A global knowledge base specification file (see Section 3.1) is deployed in the project, centrally declaring:

• Output format specifications for SQLazy scripts (three-column tab-separated, single function per step)
• Hard constraints (reserved word handling, cross-step reference rules, etc.)
• Loading paths for function documentation and feature documentation

When the user enters /sqlazy-plan followed by business requirements in the chat box, Trae automatically inherits all the above rules without needing to restate them.

2. Structured Four-Step Output

Trae is constrained to output solutions following these four steps rather than giving conclusions directly:

1. Capability Review: List the functions and features involved in the task

2. Requirement Decomposition: Break down business requirements into ordered data processing steps

3. Feature Matching: Match each step with appropriate SQLazy features

4. Code Implementation: Output the final .nspl step-by-step script

This enforced output flow ensures the auditability of the solution—the rationale for each step choice is clearly visible.

3. Proactive Requirement Clarification

Facing complex requirements, Trae proactively raises key questions, such as: How should the date range be defined? How should null values be handled? Is the grouping key unique? This prevents the AI from making assumptions on its own.

2.2 SQLazy's Core Value

SQLazy provides a dedicated IDE for writing and executing .nspl scripts. Its core value is reflected in three aspects:

1. Clear Step-by-Step Semantics, Low Audit Barrier

Taking "longest consecutive rising days of a stock" as an example, the .nspl script requires only 5 steps: filter → sort → segment → summarize count → summarize max. Reading the entire script is like reading a business operation checklist, rather than parsing complex nested SQL. Each line is one step; the output of the previous step is the input of the next, with logic fully unfolded.

2. Step-by-Step Execution, Quick Problem Localization

In the IDE, you can run each step incrementally and inspect intermediate results in real time. Once a step's output doesn't meet expectations, you can immediately pinpoint the specific logic error without having to dig through dozens of lines of nested SQL.

3. One Script, Multi-Database Compilation

After validation passes, a one-click compile generates standard SQL for mainstream databases such as MySQL, PostgreSQL, Oracle, etc., without rewriting for each database separately.

3. Workflow

3.1 Environment Setup

The project adopts the following standard directory structure:

project_root/

├── plan.md       # Global specification: format conventions, loading paths

├── sqlazy-plan.md  # Command entry: /sqlazy-plan trigger, inherits all rules from plan.md

├── nspl/           # Delivery directory: .nspl scripts stored here

├── function/        # Function reference documentation (auto-loaded)

└── action/        # Action reference documentation (auto-loaded)

After creating a new project in Trae, copy the sqlazy-plan.md, plan.md files, and the function and action directories from the SQLazy installation directory's LLM folder to the project root.

3.2 How to Trigger

Use the /sqlazy-plan command in the Trae chat box to trigger the task, followed by a complete business requirement description. It is recommended to specify the involved tables, fields, join relationships, grouping dimensions, time range, and output requirements in one go.

3.3 Validation and Correction

This is the most critical step in the entire chain:

Construct a small amount of representative test data and manually calculate the expected results

Run the script step by step in the SQLazy IDE, comparing intermediate results with expected values

When issues are found, directly modify the script or feed back to Trae for regeneration

After validation passes, compile to generate the target database's SQL

4. Case Studies

The following four cases, from easy to hard, fully document the problem-solving process.

Case 1: Longest Consecutive Rising Days for a Stock

Requirement:

/sqlazy-plan In the stock price table stock, which contains three columns—CODE (integer),DT (date), and CL (closing price)—calculate the maximum number of consecutive days that the stock with code 100046 has been rising (i.e., each day's closing price is higher than the previous day's closing price).

Analysis and Implementation:

This is the simplest type of statistical requirement. Trae outputs the following script according to the four-step process:

VariableName	Anchor	Statement
t1	stock	filter CODE = 100046
t2		sort DT
t3		segment CL; not_up; as SegGroup
t4		summarize CL count as cnt; group SegGroup
t5		compute cnt - 1, as up_days
t6		summarize up_days max as max_up_days

Simple statistical requirement, generated correctly by AI in one attempt. Run and validate

directly in the SQLazy IDE, then compile to generate the corresponding database SQL.

Case 2: Merging Multiple Tables by ID into a Single Row

Requirement:

/sqlazy-plan There are four data tables T1, T2, T3, and T4 with similar structures. Each table has two fields: the first field is an ID (named id, id2, id3, and id4 respectively), and the second field is named colA, colB, colC, and colD respectively. The goal is to merge these four tables by their ID values into a single result table with 9 columns: the first column, ID_main, stores the ID value, and the remaining 8 columns are the two columns from each of the four tables (i.e., all fields from T1, T2, T3, and T4). Each distinct ID appears as exactly one row in the merged table. If an ID is missing from any of the original tables, the corresponding columns from that table are set to NULL.

Analysis and Implementation:

The core challenge of this requirement is that the four tables have different ID field names (id, id2, id3, id4), and a method is needed to unify them while ensuring all IDs are preserved without loss.

Trae designed a dual-track strategy of "full join + ID coalescing," outputting an 8-step script:

VariableName	Anchor	Statement
t1	T1	derive id as ID_main, id, colA
t2		join ID_main; with T2; id2; take id2, colB; full
t3		compute ifn(ID_main, id2), as ID_main
t4		join ID_main; with T3; id3; take id3, colC; full
t5		compute ifn(ID_main, id3), as ID_main
t6		join ID_main; with T4; id4; take id4, colD; full
t7		compute ifn(ID_main, id4), as ID_main
t8		derive ID_main, id, colA, id2, colB, id3, colC, id4, colD

Same as Case 1, correct on first attempt.

Case 3: Cross-Group Sequential Field Value Filling

Requirement:

/sqlazy-plan Given a data table lines where the first two fields Group1 and Group2 are grouping columns, the third field LineID is a unique row identifier, and the fourth field TargetField is a numeric target column. After sorting by Group1, Group2, and LineID, within each distinct Group1, every Group2 has the same number of records; only the last Group2 (in sort order) has non-null values in TargetField, while all other Group2 groups contain nulls. The requirement is to copy the TargetField values from that last Group2 group to the other groups within the same Group1, in the same sequential row order (i.e., matching positions by sorted order). The final output should be a table containing only the columns Group1, Group2, LineID, and TargetField, sorted in ascending order by the first three columns.

Analysis and Implementation (corrected after one iteration):

The core difficulty of this requirement is that LineID is unique across all rows and cannot be directly used as a cross-group join key. Trae's first version incorrectly used Group1 + LineID as the join key, causing mapping failures. Below is the complete iterative correction process.

First Version (Incorrect)

Trae's first attempt used Group1 + LineID as the join key to fill the TargetField from the last subgroup back to the whole table:

VariableName	Anchor	Statement
t1		sort Group1, Group2, LineID
t2		rank Group2; max; partition Group1
t3		derive Group1, LineID, TargetField as filled_TargetField
t4		join Group1, LineID; with t3; Group1, LineID; take filled_TargetField
t5		derive Group1, Group2, LineID, filled_TargetField as TargetField

Problem: LineID is unique across every row (e.g., 101, 105, 201, 205, 301, 305).

Different Group2 groups share no LineID values. Therefore, when using Group1 + LineID

as the join key, only the last subgroup's own rows can match; all other subgroups' rows

fail to match, TargetField remains NULL, and the fill logic completely fails.

Corrected Version: Using Row Number as Mapping Bridge

Instead of using LineID for joining, the "row number" (positional sequence 1, 2, 3, ... obtained by ranking within each Group2 by LineID) serves as the mapping bridge. Since every Group2 within the same Group1 has the same row count, the "Nth row" naturally corresponds across different Group2 groups.

VariableName	Anchor	Statement
t1	lines	sort Group1, Group2, LineID
t2		rank as seq; partition Group1, Group2
t3		rank Group2; max; partition Group1
t4	t2	join Group1, seq; with t3; Group1, seq; take TargetField as TF_src
t5		compute ifn(TargetField, TF_src) as TargetField
t6		derive Group1, Group2, LineID, TargetField
t7		sort Group1, Group2, LineID

Validation Example:

Assume the data is as follows (all LineID values are unique):

Group1	Group2	LineID	TargetField
A	G2-1	101	NULL
A	G2-1	105	NULL
A	G2-2	201	NULL
A	G2-2	205	NULL
A	G2-3	301	100
A	G2-3	305	200

After t2 ranking, seq is obtained: G2-1(101→1, 105→2), G2-2(201→1, 205→2), G2-3(301→1, 305→2). t3 takes the two records of G2-3. t4 creates a mapping table (A,1→100), (A,2→200). After t5 backfill, the 1st row of every subgroup gets 100, and the 2nd row gets 200.

When LineID is unique across the entire table, it cannot be directly used as a cross-subgroup

join key. You must first rank within each subgroup to obtain a row number, and use the row number

as the mapping bridge. The rank ... max filter can directly select the last subgroup without

requiring an additional summarize + backfill step.

Case 4: Invoice Amount Split by Accounts with Total Conservation

Requirement:

/sqlazy-plan For the invoice table i (containing fields invoiceid, amount, projectid) and the project table p (containing fields id, projectid, accountcode), join them on projectid. For the joined result, add a new allocation field splitamount to implement a splitting logic that distributes the amount evenly by the number of accounts under each project, while ensuring the total sum remains conserved. Within each group, sort by accountcode in ascending order. For the 2nd through the Nth account, splitamount is calculated as amount / total_number_of_accounts, rounded to 2 decimal places. The 1st account bears the rounding remainder; its splitamount is set to the original invoice amount minus the sum of the splitamount values of all other accounts, so that the total exactly matches the original amount.

Analysis and Implementation (finalized after two iterations):

This is the most complex of the four cases. Although Trae's first version correctly identified the partitioned aggregation approach, there were issues with syntax details and step organization, requiring two iterations to finalize.

First Iteration: Partitioned Aggregation (partially corrected)

VariableName	Anchor	Statement
t1	i	join projectid; with p; projectid; take accountcode; inner
t2		sort projectid, accountcode
t3		rank accountcode; as rank; partition projectid
t4		compute *, count; as account_cnt; partition projectid
t5		compute round(amount/account_cnt, 2) as temp_split; temp_split, sum as 'sum_temp_split'; partition projectid
t6		compute if(rank=1 then if(account_cnt>=1 then amount else amount-t5.'sum_temp_split'+temp_split) else temp_split) as splitamount

Two issues were found after running:

Issue 1: Line 4 used * as the count formula, and the SQLazy parser reported 'logic error near [*]'.

The current version does not support * as a count formula.

Issue 2: User feedback: 'Complete the calculation directly in one step based on whether rank=1,

without intermediate temporary columns' — hoping to merge the base allocation calculation and

final conditional calculation into a single step.

Second Iteration: Final Version

Two separate corrections were applied:

• Changed * to the specific field projectid (counting projectid within a partition is equivalent to counting rows)
• Restructured the approach: first derive projectid and accountcode from table p separately, then join with table i. This avoids the single-letter table name issue and makes the join explicit.

VariableName	Anchor	Statement
t0	p	derive projectid, accountcode
t1	i	join projectid; with t0; projectid; take accountcode
t2		sort invoiceid, accountcode
t3		rank as seq; partition invoiceid
t4		compute count, as N; partition invoiceid
t5		compute round(amount / N, 2) as even_split
t6		compute if(seq=1 then amount - even_split*(N-1) else even_split) as splitamount
t7		derive invoiceid, amount, projectid, accountcode, splitamount
t8		sort invoiceid, accountcode

Validation Example:

projectid=1, amount=100.00, 3 accounts (accountcode=1, 2, 3)

VariableName	Result
t1	3 rows, each with invoiceid, amount=100.00, projectid=1, accountcode
t2	Sort: accountcode=1, 2, 3
t3	rank: 1, 2, 3
t4	N = 3
t5	even_split = [33.33, 33.33, 33.33]
t6	splitamount = [33.34, 33.33, 33.33], total = 100.00 ✓

The split logic is correct: the first account bears the rounding difference of 0.01,

and the total is conserved. Three key lessons from this case: ① * is not supported in the current

SQLazy environment; specific field names must be used; ② in compute statements, aggregate

parameters and cross-row parameters are mutually exclusive and cannot be used simultaneously;

③ reserved words (such as sum) used as names must be enclosed in single quotes.

5. Conclusion

The core value of the Trae + SQLazy combination does not lie in "letting AI automatically write SQL," but rather in constraining the AI's uncertainty within an auditable, debuggable intermediate layer, with a deterministic engine completing the final execution.

In this workflow, the three parties have clear division of labor:

• Trae is responsible for understanding requirements, clarifying ambiguities, and generating the structured initial NSPL — this is what AI does best: structuring fuzzy problems
• SQLazy IDE is responsible for syntax validation, step-by-step debugging, and cross-database compilation — this is the reliable execution of a deterministic engine
• Human is responsible for verifying business definitions, validating test results, and correcting logic deviations — this is irreplaceable business judgment

Reviewing the four cases: from the simple statistics that passed on first attempt, to the multi-table merge generated correctly, to the cross-group filling that required correcting the join key, to the invoice split finalized after two iterations—each step confirms the feasibility of the path of "AI assistance + human review + small-sample validation." In practice, this process can be standardized to make AI a true productivity amplifier rather than a source of risk.