Calculate amounts and time periods

Estimated study time: 60–75 minutes. Read, predict, run, explain, then attempt the independent work.

A stored price is not yet a sales amount. Each order line contains a quantity, a historical unit_price and a line_discount. In this dataset line_discount is one total discount for the entire line, not a per-unit discount. The agreed line calculation is quantity multiplied by unit_price, minus line_discount. Read the data dictionary before writing arithmetic; multiplying the discount by quantity would answer a different question.

The products table contains a current list price. It is useful for describing the catalogue, but past order sales must use order_items.unit_price. In a real shop, prices change. Joining today's list price into an old order can rewrite history without changing a single stored transaction. This is an example of why knowing column meaning matters more than knowing syntax.

PostgreSQL numeric stores exact decimal values at the declared precision. That is appropriate for this small financial-style fixture. Floating-point types approximate many decimal fractions and can introduce small representation differences. Integers are appropriate for whole quantities. If both sides of division are integers, PostgreSQL integer division truncates the fractional part. A decimal such as 100.0 makes the percentage calculation decimal. Round only the final displayed measure unless the business definition explicitly rounds each transaction.

Label values with CASE

CASE evaluates conditions and returns an expression. WHEN line_discount > 0 THEN 'discounted' identifies discounted lines; ELSE 'full_price' labels the remainder. END closes the expression. This adds a result column; it does not edit the source data. The order of WHEN branches matters because the first true branch wins. Choose branches that match the policy and make the fallback explicit, especially if unknown values are possible.

To select a short list of identifiers, IN (1004,1006) means that the value matches either listed identifier. It is a compact alternative to joining equality conditions with OR. The list uses numbers here; text values in a list would need single quotes.

A CASE expression is also useful for binary features: 1 for an event inside a period and 0 outside it. A binary result is just a defined indicator, not a prediction. If a future model uses it, the event must have been observable before the prediction time. We will return to that distinction in module 11.

Make time labels precise

The month key in this course is the first date of the month, created using date_trunc('month',order_date) and cast to date. Grouping by a full date key preserves the year. Grouping only by the month number would mix January 2025 with January 2026. A final ::text conversion is for stable text output in the practice runner.

Subtracting one PostgreSQL date from another returns the number of days between them. We use a fixed cutoff instead of the machine's current date, so your result will match the worked answer next year. This is a reproducibility choice. If an operational report uses today, record its run date and time zone alongside the result.

Undefined is not automatically zero

A percentage needs a numerator, denominator and population definition. NULLIF(denominator,0) returns NULL when the denominator is zero, which avoids a division-by-zero error. The resulting null percentage means the rate is undefined. Replacing it with zero would claim an observed zero rate even though there was no denominator. That may be appropriate for some displays, but it must be a separate, explicit presentation policy.

Check one calculation by hand before trusting the full output. For order 1006, the bag contributes 800 minus 80, and three pens contribute 3 times 40. The total is 840. A single hand calculation will not prove the entire report, but it catches a mistaken discount unit early.

Worked example 04 calculate

The arithmetic runs independently for each line. The CASE column explains the branch taken without altering any values.

SELECT order_id, line_no,
 quantity * unit_price - line_discount AS line_sales,
 CASE WHEN line_discount > 0 THEN 'discounted' ELSE 'full_price' END AS price_type
FROM aabha_sql.order_items WHERE order_id IN (1004,1006)
ORDER BY order_id,line_no;

Expected result from the bundled dataset:

The runnable file is sql/examples/04_calculate.sql.

Worked example 04 dates

The January 31 order maps to January 1, and March 31 is one day before April 1. These are useful boundary checks.

SELECT order_id, date_trunc('month',order_date)::date::text AS order_month,
 DATE '2025-04-01' - order_date AS days_before_cutoff
FROM aabha_sql.orders WHERE order_id IN (1001,1004,1012)
ORDER BY order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/04_dates.sql.

Worked example 04 ratio

Compare a valid denominator with zero. The null result is intentional evidence of an undefined rate.

SELECT ROUND(100.0 * 3 / NULLIF(14,0),2) AS example_percent,
 100.0 * 3 / NULLIF(0,0) AS undefined_percent;

Expected result from the bundled dataset:

The runnable file is sql/examples/04_ratio.sql.

Independent practice

04a

For order 1005 return before_discount, line_discount and line_sales. Explain in a sentence why the discount is subtracted once.

Hint: The discount belongs to the whole line.

04b

For orders 1008, 1009 and 1012 return order_id and in_march, using CASE to produce 1 for dates in March and 0 otherwise.

Hint: Use the date boundaries taught in module 3. IN (1008,1009,1012) is shorthand for matching any of those identifiers.

Check before moving on

You can distinguish a historical transaction price from a catalogue price, a line discount from a unit discount, and an undefined percentage from zero.