Objective

Write a SQL query to calculate the average number of items per order.

Table Schema:

• items_per_order

Task Requirements:

• Calculate the weighted mean of items per order
• Account for the frequency of each item count
• Round the result to 1 decimal place
  
  

Output columns: mean_items

SELECT 
    ROUND(
        SUM(item_count * order_occurrences)::DECIMAL / 
        SUM(order_occurrences), 
        1
    ) AS mean_items
FROM items_per_order;

Explanation

Step 1: Understanding Compressed Data

The data is stored in a compressed format where each row represents a group of orders with the same item count. To calculate the true mean, we need to account for how many orders fall into each group.

Step 2: Calculating the Weighted Sum

SUM(item_count * order_occurrences)

Multiplies each item count by how many orders had that item count, then sums all groups to get the total items across all orders.

Step 3: Calculating the Total Order Count

SUM(order_occurrences)

Sums all order occurrences to get the total number of orders.

Step 4: Computing the Weighted Average

SUM(item_count * order_occurrences)::DECIMAL / SUM(order_occurrences)

Divides weighted total items by total orders. The ::DECIMAL cast ensures floating-point division.

Step 5: Rounding the Result

ROUND(..., 1) AS mean_items

Rounds the mean to 1 decimal place as required.