Amazon Easy Behavioral + SQL Mix Classic

Most
Active
User

Find the user with the highest number of transactions.

Table Schema

Inspect
Table

interactive

Column	Type	Description
transaction_id	integer	Unique transaction identifier.
user_id	integer	User who made the transaction.

Sample Data

Sample Input: transactions

Expected Output

user_id	transaction_count
101	3

SQL Editor

Waiting for query

user_id	transaction_count
101	3

Hints

Hint 01: Count transactions per user.

Hint 02: Sort by the transaction count in descending order.

Hint 03: Return the top user with the highest count.

Solution

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT
  user_id,
  COUNT(*) AS transaction_count
FROM transactions
GROUP BY user_id
ORDER BY transaction_count DESC, user_id ASC
LIMIT 1;

Explanation

Count how many transactions each user has made.

Sort users by that count from highest to lowest.

Return the top user and their transaction count.