Compressed Mean [Alibaba SQL Interview Question]

You are trying to find the mean number of items bought per order on Alibaba, rounded to 1 decimal place.

However, instead of doing analytics on all Alibaba orders, you have access to a summary table, which describes how many items were in an order (), and the number of orders that had that many items ().

Table:

Example Input:

There are 500 orders with 1 item in each order; 1000 orders with 2 items in each order; 800 orders with 3 items in each order.

Example Output:

Explanation

Let's calculate the arithmetic average:

Total items =

Total orders =

Mean =

The dataset you are querying against may have different input & output - this is just an example!

Here’s the steps to the solution:

1. Calculate the weighted average of items per order.
2. Round the result to 1 decimal.

Step 1

Let's follow the logic in the example output. First, we need to multiply each with the number of occurrences , calculate the sum using , and finally divide with the total number of orders using .

However, there is a trick we need to account for. By default, both metrics are of integer type (i.e. 3, 5, 6), meaning that division will return an integer. Remember that the question specifically asks for output rounded to 1 decimal place.

To tackle this, it is then necessary to cast either column as a decimal type (i.e. 3.1, 5.2, 6.7). This can be done by simply passing a or applying on either column.

Step 2

Use to round the result to 1 decimal place.