Task
Meta Marketplace wants to identify the user with the second-highest number of completed transactions. Write a SQL query to return that user.
Requirements
- Count only transactions where
status = 'completed'.
- Return the user or users tied for the second-highest completed transaction count.
- Include
user_id, user_name, and completed_transaction_count in the output.
- Order the final result by
user_id ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for the user |
| user_name | VARCHAR(100) | Meta Marketplace user name |
| region | VARCHAR(50) | User region |
| signup_date | DATE | Date the user signed up |
transactions
| column | type | description |
|---|
| transaction_id | INT | Primary key for the transaction |
| user_id | INT | User who initiated the transaction |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | Transaction status |
| transaction_date | DATE | Date of the transaction |
Sample Data
users
| user_id | user_name | region | signup_date |
|---|
| 104 | Priya Shah | APAC | 2024-01-11 |
| 101 | Ava Chen | NA | 2023-11-03 |
| 108 | Zoe Kim | APAC | 2024-04-09 |
transactions
| transaction_id | user_id | amount | status | transaction_date |
|---|
| 205 | 101 | 45.00 | completed | 2024-05-03 |
| 201 | 104 | 80.00 | completed | 2024-05-01 |
| 214 | 107 | 55.00 | failed | 2024-05-09 |
Expected Output
| user_id | user_name | completed_transaction_count |
|---|
| 101 | Ava Chen | 3 |
| 104 | Priya Shah | 3 |
