Level 7 · Debug: the markup numbers look off

This query runs, returns all 120 products and shows no error message. It is still wrong, which is the most dangerous kind of bug. The ticket gives the clue: every markup equals the price minus 100.

How SQL reads arithmetic

SQL follows school math. * and / run before + and -, and operators of the same level run left to right. So the starter’s expression

unit_price - unit_cost / unit_cost * 100

is read as unit_price - ((unit_cost / unit_cost) * 100). Any number divided by itself is 1, so the whole thing is unit_price - 100. The Alder Oak Coffee Table costs $919, so it shows 819. Parentheses force the order you mean:

SELECT
  name,
  unit_price,
  unit_cost,
  ROUND((unit_price - unit_cost) / unit_cost * 100, 1) AS markup_pct
FROM products;

Now the coffee table, with a cost of $503.50, has a markup of 82.5%: it sells for 82.5% more than it costs. The Vale Velvet Loveseat has no cost on file, so its markup stays NULL, which is honest.

Markup or margin?

The margin level divided the profit by the price. Markup divides the same profit by the cost. For the coffee table that is a 45.2% margin and an 82.5% markup. Both are correct, they answer different questions, and buyers often talk in markup while finance talks in margin. When a ticket says “margin”, ask which one they mean. A quick rule of thumb: margin can never reach 100%, while markup often does, so a “margin” above 100% is really a markup, or a bug.

Common mistakes

• Spotting a pattern and not trusting it. “Every value is price minus 100” is a strong hint that one part of the expression collapsed to a constant. Read the formula operator by operator.
• Adding parentheses in the wrong place: unit_price - (unit_cost / unit_cost) * 100 changes nothing, because it spells out what the database already did.
• Sanity-checking only the first row. Check one cheap and one expensive product, and make sure a product with a NULL cost still shows NULL rather than a number.

In SQL Server

Operator precedence is the same, so the same parentheses fix it. Watch the types too: if both columns were INT there, (price - cost) / cost would truncate to a whole number before the * 100. Multiply first or use 100.0:

SELECT ROUND((ListPrice - StandardCost) * 100.0 / StandardCost, 1) AS MarkupPct
FROM Production.Product;