Aggregations and GROUP BY
Understand how to calculate average order value per user segment using SQL GROUP BY while handling NULL values correctly. Learn to differentiate COUNT types, manage grouping levels, and apply WHERE versus HAVING filters. This lesson equips you to write precise aggregate queries, clarify assumptions on missing data, and validate your results for interviews.
An interviewer asks, “Calculate the average order value (AOV) for each user segment.” Return one row per user segment, including enterprise, self_serve, and any additional segments in users. Calculate AOV using orders associated with users in each segment, excluding orders with NULL amounts from the average. The users(user_id, segment) table maps users to segments. The orders(order_id, user_id, amount, created_at, coupon_code) table stores order details, including records with NULL values in amount or coupon_code.
Calculating AOV requires additional consideration when orders.amount contains NULL values. If the enterprise segment has three orders but only two non-NULL amounts, COUNT(*) returns three, while AVG(amount) calculates the average using only the two non-NULL values. Candidates should explicitly define how the AOV calculation handles orders with NULL amounts before selecting an aggregation method.
The weak answer starts with AVG(amount) and assumes the database’s NULL policy matches the product definition. That answer is weak because the numerator and denominator are no longer stated separately. The stronger answer first says that the final grain is one row per segment, confirms that the source grain is one row per order_id, and asks whether an order with an unknown amount belongs in the denominator.
This diagram shows how grouping by segment produces one row per segment, while COUNT(amount) excludes rows where amount is NULL. A NULL amount does not indicate an order value of zero. An order with a NULL amount requires an explicit handling decision: exclude it from the calculation, correct the missing value in the source data when a verified amount is available, or report it separately.
An interviewer might ask, “Why does the denominator exclude some orders?” Compare COUNT(*) and COUNT(amount) for each segment to verify how missing values affect the denominator. If the counts differ, some orders have NULL amounts. When AOV is calculated using AVG(amount), the result includes only orders with non-NULL amounts. ...