A seasonal pricing team is building a two-tier discount model from a base price of $400:
- The member price is
80%of the base price. - The clearance price is
85%of the member price — calculated from the discounted member price, not from the original base.
Write a query to return both prices in a single row.
Output:
- A single row with two columns:
member_priceandclearance_price.
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
400 * 0.80 AS member_price,
400 * 0.80 * 0.85 AS clearance_priceThe shape
The clearance price is the member price scaled by another 15% off, but the calculation has to be written from the base price both times — 400 * 0.80 for the member price, and 400 * 0.80 * 0.85 for the clearance price.
Clause by clause
400 * 0.80 AS member_priceis the first expression: 80% of the base price, returning320. The decimal on0.80introduces a scale to the multiplication, but the result still lands on the whole number320because the underlying math is clean.400 * 0.80 * 0.85 AS clearance_priceis the second expression: the base price scaled down by 20%, then by another 15%. SQL evaluates the chained multiplication left to right —400 * 0.80resolves to320, then320 * 0.85resolves to272. The two discounts compound rather than adding.
Why this and not member_price * 0.85 AS clearance_price
The natural shape would be to reference the member_price alias directly — that's how the business logic reads, and that's how the prompt describes the relationship. SQL won't allow it inside the same SELECT list. Every expression in SELECT evaluates against the same input at the same time, and the aliases only exist as labels on the way out. At the moment clearance_price is being computed, member_price is not yet a usable name. So the calculation has to be written from the underlying base price: 400 * 0.80 * 0.85.
This is the same constraint as on simpler multi-column queries, but it bites harder here because the second column genuinely depends on the first column's value. The fix at this level is to repeat the arithmetic.
The trap
The trap is the 80% + 15% combination. It is tempting to compute the clearance price as the base scaled by the sum of the two discounts — 400 * 0.65, since 20% + 15% = 35% — but that returns 260, not 272. The two discounts don't add; they compound, because the second one applies to the already-discounted price. The correct math is multiplicative: 0.80 * 0.85 = 0.68. Any time discounts stack on each other rather than on the original base, the calculation is a product of the scaling factors, not a sum of the percentages.
You practiced repeating an expression because aliases defined in SELECT are not visible to other items in the same SELECT list. The recurring fix at this scale is to repeat the arithmetic inline; once subqueries and CTEs enter the picture, those become the cleaner ways to compute a value once and reuse it.
