Compressed Mean [Alibaba SQL Interview Question]

You're trying to find the mean number of items per order on Alibaba, rounded to 1 decimal place using tables which includes information on the count of items in each order ( table) and the corresponding number of orders for each item count ( table).

Table:

Example Input:

There are a total of 500 orders with one item per order, 1000 orders with two items per order, and 800 orders with three items per 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!

Step 1: Calculate the weighted average of items per order

To calculate the weighted average of items per order, we multiply each with the corresponding number of occurrences , calculate the sum using , and finally divide it by the total number of orders using .

However, it's important to note that both and are of integer type by default, which means that division will return an integer result. To ensure that the output is rounded to 1 decimal place, we can cast either column to a decimal type using or .

Step 2: Round results to 1 decimal place

To round the result to 1 decimal place, we can use the function.