Interview Query

Average Unique Counts

Start Timer

0:00:00

Upvote
4
Downvote
Save question
Mark as completed
View comments (10)
Next question

You have been provided with two tables: user_orders and ordered_items. A user can have multiple orders, and within each order, there may be multiple items with either the same or different categories. Find a user that has the highest average number of unique item categories per order.

Note: You may assume that there is only one user with the highest average number of unique item categories per order.

Example:

user_id user_name order_id
111 DUSAN 1
111 DUSAN 2
222 AHMED 3
order_id item_id item_category
1 1111 11
1 1112 11
1 2222 22
1 2223 22
2 3333 33
3 1114 11
3 1115 11
3 1116 11

Result:

user_name avg_unique_item_categories_per_order
DUSAN 1.5

Input:

user_orders table

Column Type
user_id INTEGER
user_name TEXT
order_id INTEGER

ordered_items table

Column Type
order_id INTEGER
item_id INTEGER
item_category INTEGER

Output:

Column Type
user_name INTEGER
avg_unique_item_categories_per_order FLOAT
.
.
.
.
.


Comments

Loading comments.