Available data and assumptions
Stories on CD records orders, order dates, sales channel (in-store or mail order), customer IDs, products and quantities. For this exercise assume each order line also records its actual unit selling price after discount. Consider completed sales within one chosen calendar year; exclude cancelled orders and returns, and exclude tax and shipping from sales revenue.
Views can expose calculations over stored data. Tables can also support generated or virtual columns; views are not the only way to calculate a value. Here the task is to specify information needs, not to write SQL.
- Propose four distinct calculations useful to the owners.
- For each, state the input data, formula or aggregation, grouping/time period and decision it supports. For a percentage, define the denominator and how to handle zero.
Award up to 5 points for each of four calculations: 1 for appropriate inputs, 2 for a correct formula, 1 for grouping/time period and edge cases, and 1 for a business purpose. Other well-defined calculations are acceptable. This is a self-assessment; the page displays a model answer and does not grade free text.