Interview Question Bank · Order-level average · COUNT(DISTINCT) · to_char
1. Average order value by month
EasyE-commerceQuestion #85
Concept: Order-level average · COUNT(DISTINCT) · to_charshow
Average order value (AOV) is revenue divided by the number of orders. When revenue lives on line items, AVG(line_total) averages lines, not orders, and an order with three lines counts three times. Divide the summed revenue by COUNT(DISTINCT order_id), or total each order in a subquery first and average those totals.
Example (different data):
SELECT to_char(b.booked_on, 'YYYY-MM') AS month, ROUND(SUM(l.nights * l.rate) / COUNT(DISTINCT b.id), 2) AS avg_booking_value FROM bookings b JOIN booking_lines l ON l.booking_id = b.id GROUP BY 1 ORDER BY 1;
Your task
Quillmere, an online homeware and gadgets store, tracks average order value every month to judge whether its bundles are working.
Using completed orders only, return month (YYYY-MM of ordered_on), orders (number of completed orders), revenue (sum of quantity * unit_price) and avg_order_value (revenue / orders, rounded to 2 decimals). Sort by month.
Starting Postgres in your browser…
Checking your access…