Back to questions

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 ().

Column Name | Type |
---|---|

item_count | integer |

order_occurrences | integer |

item_count | order_occurrences |
---|---|

1 | 500 |

2 | 1000 |

3 | 800 |

4 | 1000 |

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.

mean |
---|

2.7 |

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:

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

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.

Use to round the result to 1 decimal place.

PostgreSQL 14