Average Order Value Per Customer | Dataford Interview Questions

The Zappos Family wants to compare average purchase sizes across customers using order transaction data. Write a PostgreSQL query that calculates the average order value for each customer.

Requirements

Group orders by customer_id.
Calculate the average of order_total, rounded to two decimal places.
Return one row per customer, ordered by average order value from highest to lowest. Place customers whose average is NULL last.

PostgreSQL's AVG ignores NULL order totals. If a customer has no non-NULL order totals, their average should remain NULL.

Schema

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	Identifier of the customer who placed the order
order_total	NUMERIC(10,2)	Total monetary value of the order
order_date	DATE	Date the order was placed

Your question is Average Order Value Per Customer. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem