Back to questions
You are trying to find the most common (aka the ) number of items bought per order on Alibaba.
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 ().
In case of multiple item counts, display the in ascending order.
Column Name | Type |
---|---|
item_count | integer |
order_occurrences | integer |
item_count | order_occurrences |
---|---|
1 | 500 |
2 | 1000 |
3 | 800 |
4 | 1000 |
mode |
---|
2 |
4 |
The most common number of is 1000. Both item counts of 2 and 4 fit this.
The dataset you are querying against may have different input & output - this is just an example!
It is easier to organize our thoughts and code if we break the task apart. To complete the task, we need to
There are two ways to obtain this: a simple or a built-in function. Read more about the here.
Run the following queries separately. Both queries will yield the same number of order occurrences which is 1000.
Why we're using ? Since the field contains the total number of occurrences by , simply taking the maximum value in this field is the same as taking the most common number of order occurrences.
Now, let's find out which corresponds to the . To do so, simply declare in the statement while filtering the results using either the or method.
Order the result by in ascending order.
PostgreSQL 14