Problem

Table: Orders

1
2
3
4
5
6
7
8

+-------------------+------+
| Column Name       | Type |
+-------------------+------+
| order_id          | int  |
| item_count        | int  |
| order_occurrences | int  |
+-------------------+------+ order_id is column of unique values for this table.
This table contains order_id, item_count, and order_occurrences.

Write a solution to calculate the average number of items per order, rounded to 2 decimal places.

Return the result table inany order .

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

    Input: 
    Orders table:
    +----------+------------+-------------------+
    | order_id | item_count | order_occurrences | 
    +----------+------------+-------------------+
    | 10       | 1          | 500               | 
    | 11       | 2          | 1000              |     
    | 12       | 3          | 800               |  
    | 13       | 4          | 1000              | 
    +----------+------------+-------------------+
    **Output**
    +-------------------------+
    | average_items_per_order | 
    +-------------------------+
    | 2.70                    |
    +-------------------------+
    **Explanation**
    The calculation is as follows:
     - Total items: (1 * 500) + (2 * 1000) + (3 * 800) + (4 * 1000) = 8900 
     - Total orders: 500 + 1000 + 800 + 1000 = 3300 
     - Therefore, the average items per order is 8900 / 3300 = 2.70

Solution

Method 1 – Weighted Average Using SUM and ROUND

Intuition

To find the average number of items per order, we need the total number of items (item_count × order_occurrences for each row) divided by the total number of orders (sum of order_occurrences). This is a weighted mean, and SQL’s SUM and ROUND functions make it straightforward.

Approach

  1. Use SUM to calculate the total items: sum of (item_count × order_occurrences).
  2. Use SUM to calculate the total orders: sum of order_occurrences.
  3. Divide total items by total orders to get the mean.
  4. Use ROUND to round the result to 2 decimal places.
  5. Return the result as average_items_per_order.

Code

1
2

SELECT ROUND(SUM(item_count * order_occurrences) / SUM(order_occurrences), 2) AS average_items_per_order
FROM Orders;

Complexity

  • ⏰ Time complexity: O(n) — n is the number of rows in Orders (single scan for SUM).
  • 🧺 Space complexity: O(1) — Only accumulators are used.