Trae + SQLazy: A Practical Guide to Writing Complex SQL

1. Introduction

AI-assisted data development is reshaping the way SQL is traditionally written. Today’s mainstream approaches can be broadly divided into two categories: one is the “end-to-end” approach, which directly generates native SQL; the other is the “layered” approach, which generates structured intermediate representations and then compiles them into SQL. The former is easy to get started with but difficult to handle complex business scenarios; the latter adds an abstraction layer, which enables engineering-grade guarantees of auditability, debuggability, and reproducibility.

The approach adopted in this article is a combination of Trae (ByteDance’s AI programming tool) as the “brain”, responsible for understanding requirements, clarifying ambiguities, and generating SQLazy step-by-step scripts (.nspl); and SQLazy’s dedicated IDE 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, ranging from simple to complex, covering typical scenarios such as statistical aggregation, multi-table merging, cross-subgroup data filling, and amount allocation. The complete process – from requirement input to validation completion– is demonstrated, with emphasis on documenting the errors encountered and the reasoning behind corrections.

1. Tool Collaboration Model

2.1 Trae’s Roles and Capabilities

Trae plays three key roles in this workflow:

1. Automatically Load the Project Knowledge Base

Deploy the global knowledge base specification file (.md) described in Section 3.1 – Environment Setupto the project and register it as a centralized specification:

SQLazy script output format specifications (three tab-separated columns, one function per step)

Hard constraints (reserved word handling, cross-step reference rules, etc.)

Loading paths for function and feature documentation

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

1. Structured Four-Step Output

Trae is constrained to generate solutions following the four steps below instead of directly providing a final answer:

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

2. Requirement Decomposition: Break down business requirements into a sequence of data processing steps.

3. Feature Matching: Match each step with the appropriate SQLazy feature.

4. Code Generation: Generate the final step-by-step script (.nspl).

This enforced output process ensures the auditability of the solution – the rationale behind each step is clearly visible.

1. 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 unsupported assumptions about unstated conditions.

2.2 SQLazy's Core Value

SQLazy provides a dedicated IDE for writing and executing .nspl scripts. Its core value lies in three areas:

1. Clear Step Semantics, Low Audit Complexity

Taking "longest streak of consecutive up days for a stock" as an example, the .nspl script requires only 5 steps: filter → sort → segment →count → find the maximum. Reading the entire script feels like reading a business operation checklist, rather than parsing complex nested SQL. Each line represents one step; the output of each step becomes the input for the next, with the entire logic laid out explicitly.

1. Step-by-Step Execution, Quick Problem Identification

In the IDE, you can execute each step individually 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.

1. One Script, Compiled Everywhere

After validation passes, one click compiles to native SQL for MySQL, PostgreSQL, Oracle and other mainstream databases. No rewriting per database.

1. 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 sqlazy-plan.md, plan.md files, 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 best to specify the tables, fields, join relationships, grouping dimensions, time range, and output requirements – all in one go.

3.3 Validation and Correction

This is the most critical step in the entire workflow:

1. Construct a small set of representative test data and manually calculate the expected results.

2. Run the script step by step in the SQLazy IDE, comparing intermediate results against expected values.

3. When issues are found, modify the script directly or report them to Trae for regeneration.

4. After validation passes, compile to native SQL for the target database.

5. Hands-on Case Studies

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

Case 1: Longest Streak of Consecutive Up Days for a Stock

Requirement:

/sqlazy-plan Stock price table stock contains three columns – CODE,DT (date), and CL (closing price). Calculate the maximum number of consecutive days the stock with code 100046 has been rising (i.e., each day’s closing price is higher than the previous day’s).

Analysis and Implementation:

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

Simple statistical requirement, generated correctly by AI in one attempt. Run and validate directly in the SQLazy IDE, then compile to native SQL for the target database.

Case 2: Merging Multiple Tables by ID into Single Rows

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 contain the fields from 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 join them while ensuring no IDs are lost.

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

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 columns, Group1 and Group2, are grouping columns, the third column, LineID, is a unique row identifier, and the fourth field, TargetField, is a numeric target column. After sorting by Group1, Group2, and LineID, within the same 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 are NULLs. The goal is to copy the TargetField values from the last Group2 group to the other groups within the same Group1, in the same sequential row order (i.e., matching by row positions in 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:

Problem: LineID is unique across all rows (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.

Validation Example:

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

After t2 ranking, a row sequence number row_idx is generated: 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’s backfill, the first row of every subgroup gets 100, and the second row gets 200.

When LineID is unique across the entire table, it cannot be directly used as a cross-group join key. You must first rank within each subgroup to obtain a row sequence number, and use the row number as the mapping bridge. The maximum-rank filter can directly select the last subgroup without requiring the additional aggregation and backfill steps.

Case 4: Invoice Amount Split by Account, with Total Preserved

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 by the number of accounts under each project, while ensuring the total sum remains preserved. Within each group, sort by accountcode in ascending order. For the 2nd through the Nth account, calculate splitamount using amount/ total_number_of_accounts and round the result to 2 decimal places. The first account absorbs the rounding remainder; its splitamount equals the invoice’s original amount minus the sum of all other accounts’ splitamount values, so that the allocated amounts exactly match the original invoice 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. It took two iterations to finalize.

First Iteration: Partitioned Aggregation (partially corrected)

Trae used a mathematically equivalent approach – partitioned aggregation to calculate the remainder – convert “first row’s remainder = total amount – sum of all the other rows’ allocated values” within the partition to amount - sum_temp_split + temp_split, where sum_temp_split is the sum of base allocated values across all rows in the partition.

Two issues were found after running:

Issue 1: Line 4 used * as the count formula, causing the SQLazy parser to report the error “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” – they want to merge the base allocation calculation and the final conditional calculation into a single step, reducing the number of intermediate steps.

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)

Combine the original t5 and t6 steps into one – within a single computed-column statement, create three derived columns separated by semicolons.

Below is the final script:

Validation Example:

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

The splitting logic is correct: the first account absorbs the rounding difference of 0.01, and the total is preserved. Three key lessons from this case: ① * is not supported in the current SQLazy environment; specific field names must be used; ② In computed-column 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.

1. Conclusion

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

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

Trae handles understanding requirements, clarifying ambiguities, and generating the structured initial nspl draft – this is what AI does best: “structuring fuzzy problems”.

SQLazy IDE handles syntax validation, step-by-step debugging, and cross-database compilation — this is the reliable execution by a deterministic engine.

Humans are responsible for verifying business rules, 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 in one pass, 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 “AI assistance + human review + small-sample validation” approach. In practice, this workflow can be standardized to make AI a true productivity amplifier rather than a source of risk.