Write a query to return every order_totals.order_ref with integer points, awarding one point per 100 amount_cents.
Always round down towards the lower integer, including for negative refund amounts. For example, −9,950 cents gives −100 points. Return NULL points for a NULL amount.
Ordering: Sort by order_ref in ascending order.
Returnorder_refpoints
About this problem
You practise dividing in the way a business rule actually specifies, rather than in whichever way the operator happens to work. Each order total in order_totals is in cents, refunds are negative, and one point is earned per whole 100 cents rounded down, so a −9,950 cent refund costs 100 points rather than 99. The skill is noticing that “round down” and “drop the fraction” are two different instructions that only look the same on positive numbers.
This is a problem where the sample is doing something specific: every method you might reach for gives the right answer on ot_01 and ot_02, so a query that is wrong still looks right on most of the table. // and trunc() both truncate towards zero, so they return −99 for a −99.5 refund and 0 for a −99 cent one – a customer could refund an order and keep a point. CAST(… AS INTEGER) and round() go the other way and round to the nearest value, so they turn 9,950 cents into 100 points and reward 99 cents with a full point. It is worth building a tiny table of the four candidate expressions against a positive and a negative value before writing the query, because reading them cannot tell you apart what testing can.
Read the lesson: Data Types & CAST (P13) →