Given a table containing the item count for each order and the frequency of orders with that item count, write a query to determine the mode of the number of items purchased per order on Alibaba. If there are several item counts with the same frequency, you should sort them in ascending order.

Effective April 22nd, 2023, the problem statement and solution have been revised for enhanced clarity.

Table:

Example Input:

Example Output:

Explanation

Based on the example output, the value of 1000 corresponds to the highest frequency among all item counts. Specifically, both item counts of 2 and 4 have occurred 1000 times, making them tied for the most common number of occurrences.

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

1. Find the most common number of order occurrences.
2. Match the order occurrences to the corresponding item count.
3. Order the results by item count.

Step 1

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.

Step 2

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.

Step 3

Order the result by in ascending order.