Problem

Table: Terms

+-------------+------+
| Column Name | Type |
+-------------+------+
| power       | int  |
| factor      | int  |
+-------------+------+
power is the column with unique values for this table.
Each row of this table contains information about one term of the equation.
power is an integer in the range [0, 100].
factor is an integer in the range [-100, 100] and cannot be zero.

Problem Definition

You have a very powerful program that can solve any equation of one variable in the world. The equation passed to the program must be formatted as follows:

• The left-hand side (LHS) should contain all the terms.
• The right-hand side (RHS) should be zero.
• Each term of the LHS should follow the format "<sign><fact>X^<pow>" where:
  • <sign> is either "+" or "-".
  • <fact> is the absolute value of the factor.
  • <pow> is the value of the power.
• If the power is 1 , do not add "^<pow>".
  • For example, if power = 1 and factor = 3, the term will be "+3X".
• If the power is 0, add neither "X" nor "^<pow>".
  • For example, if power = 0 and factor = -3, the term will be "-3".
• The powers in the LHS should be sorted in descending order.

Write a solution to build the equation.

Example

Output

Solution

To construct the equation by pivoting the Terms table according to the specified formatting rules, we can follow a systematic approach. This involves organizing the terms based on their powers, formatting each term appropriately, and then concatenating them to form the final equation.

Approach Overview

1. Order Terms by Power Descending:

   • Sort the terms in descending order of their power to ensure the highest power appears first in the equation.

2. Assign Row Numbers Within Each Power:

   • Assign a sequential row number to each term to handle cases where multiple terms have the same power.

3. Format Each Term:

   • Determine the sign (+ or -) based on the factor.
   • Format the term according to the rules:
     • If power > 1, format as "<sign><abs(factor)>X^<power>".
     • If power = 1, format as "<sign><abs(factor)>X".
     • If power = 0, format as "<sign><abs(factor)>".

4. Concatenate Formatted Terms:

   • Combine all formatted terms in the correct order and append =0 to complete the equation.

SQL Query

WITH ordered_terms AS (
    SELECT 
        power, 
        factor,
        ROW_NUMBER() OVER (ORDER BY power DESC) AS rn
    FROM Terms
),
eqn_reps AS (
    SELECT 
        power, 
        factor,
        rn,
        CASE
            WHEN factor > 0 THEN '+'
            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 CONCAT(ABS(factor))
        END AS power_rep
    FROM ordered_terms
)
SELECT CONCAT(
            GROUP_CONCAT(CONCAT(e.sgn_rep, e.power_rep) ORDER BY e.power DESC SEPARATOR ''),
            '=0'
        ) AS equation
FROM eqn_reps e;

Step-by-Step Approach

Step 1: Order Terms by Power Descending and Assign Row Numbers

Sort the terms in descending order of their power and assign a row number to each term. This ensures that higher-powered terms appear first in the equation.

SQL Query:

WITH ordered_terms AS (
    SELECT 
        power, 
        factor,
        ROW_NUMBER() OVER (ORDER BY power DESC) AS rn
    FROM Terms
)
SELECT *
FROM ordered_terms;

Explanation:

• ROW_NUMBER() OVER (ORDER BY power DESC) AS rn:
  • Assigns a unique sequential number to each term based on the descending order of power.
• WITH ordered_terms AS (...):
  • Creates a Common Table Expression (CTE) named ordered_terms to hold the sorted terms with their assigned row numbers.

Intermediate Output After Step 1 (ordered_terms):

sql
+-------+--------+----+
| power | factor | rn |
+-------+--------+----+
| 2     | 1      | 1  |
| 1     | -4     | 2  |
| 0     | 2      | 3  |
+-------+--------+----+

Step 2: Format Each Term with Appropriate Sign and Structure

Determine the sign (+ or -) for each term based on the factor and format the term according to the rules specified.

SQL Query:

WITH ordered_terms AS (
    SELECT 
        power, 
        factor,
        ROW_NUMBER() OVER (ORDER BY power DESC) AS rn
    FROM Terms
),
eqn_reps AS (
    SELECT 
        power, 
        factor,
        rn,
        CASE
            WHEN factor > 0 THEN '+'
            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 CONCAT(ABS(factor))
        END AS power_rep
    FROM ordered_terms
)
SELECT *
FROM eqn_reps;

Explanation:

• sgn_rep:
  • Assigns a '+' if factor > 0, '-' if factor < 0, and '' otherwise.
• power_rep:
  • Formats the term based on power:
    • If power > 1, formats as "<abs(factor)>X^<power>".
    • If power = 1, formats as "<abs(factor)>X".
    • If power = 0, formats as "<abs(factor)>".

Intermediate Output After Step 2 (eqn_reps):

sql
+-------+--------+----+--------+-----------+
| power | factor | rn | sgn_rep| power_rep |
+-------+--------+----+--------+-----------+
| 2     | 1      | 1  | +      | 1X^2      |
| 1     | -4     | 2  | -      | 4X        |
| 0     | 2      | 3  | +      | 2         |
+-------+--------+----+--------+-----------+

Step 3: Concatenate Formatted Terms and Append '=0'

Combine all formatted terms in the correct order and append =0 to complete the equation.

SQL Query:

WITH ordered_terms AS (
    SELECT 
        power, 
        factor,
        ROW_NUMBER() OVER (ORDER BY power DESC) AS rn
    FROM Terms
),
eqn_reps AS (
    SELECT 
        power, 
        factor,
        rn,
        CASE
            WHEN factor > 0 THEN '+'
            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 CONCAT(ABS(factor))
        END AS power_rep
    FROM ordered_terms
)
SELECT CONCAT(
            GROUP_CONCAT(CONCAT(e.sgn_rep, e.power_rep) ORDER BY e.power DESC SEPARATOR ''),
            '=0'
        ) AS equation
FROM eqn_reps e;

Explanation:

• GROUP_CONCAT(CONCAT(e.sgn_rep, e.power_rep) ORDER BY e.power DESC SEPARATOR ''):
  • Concatenates all formatted terms (sgn_rep + power_rep) in descending order of power without any separators.
• CONCAT(..., '=0') AS equation:
  • Appends =0 to the concatenated terms to complete the equation.

Final Output:

sql
+--------------+
| equation     |
+--------------+
| +1X^2-4X+2=0 |
+--------------+

Explanation of Output:

• Formatted Terms:
  • +1X^2
  • -4X
  • +2
• Concatenated Equation:
  • +1X^2-4X+2=0