Question

An MMORPG game is being built. For the profile and inventory mechanics, it needs a query that calculates a list of game accounts whose inventory is overloaded with items.

The result should have the following columns: username/email/ items / total_weight

username-account username

⚫ email-account email address

items-total number of items in inventory

total weight-total weight of items in inventory

The result should be sorted in descending order by total weight, then in ascending order

by username.

Note:

• Each item in the inventory has its own weight

• Only accounts where the total weight of all items in the inventory exceeds the overload threshold should be included in the result.

The overload threshold is 20.

Answer 1

To achieve the desired query for the MMORPG game's profile and inventory mechanics, you can use SQL.

SELECT

username AS 'username-account',

email AS 'email-account',

COUNT(item_id) AS 'items',

SUM(item_weight) AS 'total_weight'

FROM

user_accounts

JOIN

inventory ON user_accounts.user_id = inventory.user_id

GROUP BY

user_accounts.user_id, username, email

HAVING

SUM(item_weight) > 20

ORDER BY

total_weight DESC, username ASC;