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.

Table:

Column Name

Type

item_count

integer

order_occurrences

integer

Example Input:

item_count

order_occurrences

1

500

2

1000

3

800

4

1000

Example Output:

mode

2

4

Explanation

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!

Solution

It is easier to organize our thoughts and code if we break the task apart. To complete the task, we need to

Find the most common number of order occurrences.

Match the order occurrences to the corresponding item count.

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.