To measure your competency with the topics covered in the previous $3$ lessons, let's work through a $5-$part challenge that brings all of the concepts together.

In this challenge, we will utilize the retail.products table. Complete each part in order as they progressively build on each other.

You will see a column called sku in the retail.products table. This is retail jargon for a product code or internal name for a product. In different lessons or exercises, the field will sometimes be called product$\_$code, but know that the two fields are equivalent.

To see the Task's Checks, Feedback, Expected and Actual Results, you may have to enlarge your results window. To see the Hints, you may have to condense the Task.

Task #$01$: Pull all of the products that are currently on markdown.

In your results, include:

product$\_$id

sku

original$\_$price

current$\_$price

on$\_$markdown

Want a hint?

If a product is currently on markdown, there will be a $`1`$ for that product in the $`$on$\_$markdown$`$ column.

For each of the next parts of this Challenge, add a new column onto your query from Step $1.$

Task #$02$: Pull the dollar amount that is being discounted for each of the products that are currently on markdown. Add this new column onto the end of your results and call it discount$\_$amount.

Want a hint?

This part is a bit tricky so take your time and really think over the solution. Use the hints if you need to$!$

Task #$03$: Calculate what percentage of the original$\_$price the discount$\_$amount is$.$ Remember to add this calculation to the end of your query as another column and name it discount$\_$pct$.$

Make sure that the result is less than $1$ and contains $2$ decimal places.

Want a hint?

Use the $`$ROUND$`$ function to make sure your new column has only $2$ decimal places. Refer back to the $*$Working with Decimals$*$ portion of the $**$Math with SQL$**$ lesson if you need a refresher on how to use the $`$ROUND$`$ function.

You will need to multiply one field in your calculation by $1\mathrm{.}00,$ otherwise a calculation of all integers will result in an integer. To calculate a percentage, take the calculation from your $`$discount$\_$amount$`$ column and divide it by the $`$original$\_$amount$`.$ Be careful of your parentheses!

Want another hint?

You cannot reference a calculated column name $($like $`$discount$\_$amount$`)$ in the same query. Copy the calculation from the previous step.

Task #$04$: Now that you have the discount$\_$pct$,$ use your code from that calculation and multiply it by the original$\_$price to see if it matches the discount$\_$amount you calculated $.$ Again, do this calculation in a new column and name the column check.

Want a hint?

Task #$05$: Determine what the sale price would be for each item if the Marketing team went off the rails and implemented a sale for $35\%$ off the original price of all items!

Once again, add this new column onto the end of your existing query and name it discount$\_35.$

Want a hint?

There are a couple different ways to get the right number for this last column.

You can either multiply the necessary field by $`0\mathrm{.}65`,$ which is $`1-0\mathrm{.}35`.$

Or you can multiply the necessary field by $`0\mathrm{.}35`$ and subtract that from $`1`.$ Either way should work!