Build the Equation

Mechanism

This is a per-row format then aggregate-into-one-string problem: each row of Terms is independently rendered into its own text fragment (sign + coefficient + variable part) with two CASE expressions, and then a single ordered string-aggregation pass glues every fragment together — in descending order of power, with no separator — before =0 is appended. The whole table collapses to exactly one row because the aggregation has no GROUP BY, so the engine treats the entire result set as one group.

The two CASE expressions

Every term must be printed as <sign><|factor|><variable-part>. Those three pieces are produced by two independent decisions:

• Sign depends only on the polarity of factor: positive prints +, negative prints -. Because the problem guarantees factor is in [-100, 100] and cannot be zero, only two arms are ever reachable.
• Variable part depends only on power, which encodes how much of X^<power> to suppress: power > 1 keeps the full X^p; power = 1 drops the ^1 and prints bare X; power = 0 drops the X entirely, leaving just the magnitude.

The coefficient itself is always ABS(factor) — the magnitude — because the sign has already been emitted separately. Splitting sign from magnitude is the trick that makes the descending concatenation read like real algebra (+1X^2-4X+2) instead of doubling up signs.

Worked example, traced

Take the three-row input (power, factor) = (2, 1), (1, -4), (0, 2). Trace each stage:

The aggregation orders fragments by power DESC → +1X^2, -4X, +2, joins them with an empty separator → +1X^2-4X+2, and the outer CONCAT(..., '=0') appends the right-hand side → final answer +1X^2-4X+2=0.

The query (cleaned up)

The version below removes two warts from the naive solution (explained next) but produces the identical correct output:

WITH eqn_reps AS (
    SELECT
        power,
        CASE WHEN factor > 0 THEN '+' ELSE '-' END AS sgn_rep,
        CASE
            WHEN power > 1 THEN CONCAT(ABS(factor), 'X^', power)
            WHEN power = 1 THEN CONCAT(ABS(factor), 'X')
            ELSE                ABS(factor)            -- power = 0
        END AS power_rep
    FROM Terms
)
SELECT CONCAT(
         GROUP_CONCAT(CONCAT(sgn_rep, power_rep)
                      ORDER BY power DESC SEPARATOR ''),
         '=0'
       ) AS equation
FROM eqn_reps;

Why the naive version is over-built

• The rn column is dead weight. The original computes ROW_NUMBER() OVER (ORDER BY power DESC) AS rn and a whole ordered_terms CTE around it, but rn is never referenced again. Ordering is re-stated inside GROUP_CONCAT(... ORDER BY power DESC), which is where it actually takes effect. A pre-sort in a subquery does not reliably survive into an aggregate — SQL set operations carry no inherent row order — so the ROW_NUMBER and its CTE are pure overhead. Dropped here.
• The sign CASE has an unreachable ELSE '' arm. The schema guarantees factor ≠ 0, so the WHEN factor < 0 arm catches everything not caught by > 0. Collapsing to CASE WHEN factor > 0 THEN '+' ELSE '-' END is both shorter and states the invariant. (Had zero been possible, an empty sign would have produced malformed output like +1X^2 0 — so the dead arm was also slightly misleading.)

The systems insight: string aggregation is not portable

GROUP_CONCAT is the single most engine-specific construct in this query. It is a MySQL/MariaDB-ism, and porting this exact SQL to another database will fail at parse time. The same "format-rows-then-fold-into-one-string" mechanism exists everywhere, but under different names and with different syntax for the two things that matter here — ordering inside the fold and choosing the separator:

SQLite is the interesting outlier: its group_concat ignores ordering, so on SQLite the dead ROW_NUMBER/pre-sort the naive version wrote would suddenly become load-bearing-ish — though even then the only safe guarantee is to feed an already-ordered subquery. This is the broad lesson for a systems engineer: string aggregation is an ANSI-SQL gap, so any query that builds delimited text is implicitly tied to one engine. If portability matters, isolate the aggregation behind a generated query or do the final fold in application code.

Pitfalls

• Trusting subquery order to reach the aggregate. Ordering rows in a CTE and assuming GROUP_CONCAT will honor it is the classic bug — most engines are free to reorder. The order clause must live inside the aggregate call (or WITHIN GROUP). The naive rn CTE is a symptom of this misconception even though the query happens to also repeat the order inside the call.
• Hitting the group_concat_max_len ceiling. MySQL silently truncates GROUP_CONCAT output at 1024 bytes by default. A 100-term polynomial easily blows past that, producing a quietly cut-off equation with no error. Bump it with SET SESSION group_concat_max_len = 1000000; in production-scale data.
• Number-to-string coercion of power. CONCAT(ABS(factor), 'X^', power) relies on the engine implicitly casting the integer power to text. MySQL does this; some engines need an explicit CAST(power AS CHAR). Forgetting it is a silent porting failure.
• Assuming a sign always precedes a term. If the spec allowed factor = 0 (it does not here) or a leading term with no sign, the bare +/- split would need rework. Lean on the stated invariants — that is exactly why the dead ELSE '' arm should go.

Takeaways

• Format per row, fold once. Two independent CASE expressions render each term; one ordered, separator-aware aggregate with no GROUP BY collapses the whole table into a single string.
• Separate sign from magnitude. Emitting the sign character independently and concatenating ABS(factor) is what lets descending concatenation read as valid algebra.
• Put the order inside the aggregate. Row order is not carried by subqueries; ORDER BY / WITHIN GROUP inside the aggregate is the only reliable control. A pre-sorting ROW_NUMBER CTE is dead weight here.
• String aggregation is engine-specific. GROUP_CONCAT (MySQL) maps to string_agg (Postgres), STRING_AGG (SQL Server), LISTAGG (Oracle), each with its own ordering/separator syntax and its own truncation traps — a portability boundary worth memorizing.

Re-authored and deepened for this guide. Problem from LeetCode 2118 "Build the Equation" (Hard). Engine-specific string-aggregation syntax verified against the MySQL 8 Reference Manual (GROUP_CONCAT, group_concat_max_len), the PostgreSQL 16 docs (string_agg), Microsoft SQL Server STRING_AGG documentation, Oracle LISTAGG documentation, and the SQLite group_concat reference. Fixes over the original: removed the unused ROW_NUMBER/rn CTE and collapsed the sign CASE's unreachable ELSE '' arm (factor is guaranteed non-zero); added the portability comparison and aggregate-ordering correctness note.