Drill 1 of 12 in E-commerce questions
0

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…