CTE Chain

Medium
1
70.6% Acceptance

Create a chain of three Common Table Expressions (CTEs) to perform successive transformations on the invoice data.

Table to be used: Invoice

Instructions:

  1. Use the Invoice table from the Chinook database.
  2. Create a sequence of three CTEs.
  3. The first CTE (CTE1) should take the Total column from the Invoice table and multiply it by 1.1, naming this new column TransformedTotal1.
  4. The second CTE (CTE2) should take the TransformedTotal1 column from CTE1 and multiply it by 0.9, naming this new column TransformedTotal2.
  5. The third CTE (CTE3) should take the TransformedTotal2 column from CTE2 and multiply it by 1.05, naming this new column TransformedTotal.
  6. The final output should have the columns:
    • InvoiceId: Represents the ID of the invoice.
    • TransformedTotal: The final transformed total after all the operations performed in the CTEs.
  7. The results should be ordered by the InvoiceId in ascending order.

Expected Columns in Final Output:

  • InvoiceId
  • TransformedTotal

Order By: InvoiceId