Market Analysis I - Problem
You have three tables: Users, Orders, and Items.
The Users table contains user information including their join date and favorite brand. The Orders table tracks all purchases with buyer and seller information. The Items table stores item details including brand information.
Write a SQL query to find for each user:
- Their join date
- The number of orders they made as a buyer in 2019
Return the result in any order with columns: buyer_id, join_date, and orders_in_2019.
Table Schema
Users
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | Primary key, unique user identifier |
join_date
|
date | Date when user joined the platform |
favorite_brand
|
varchar | User's preferred brand |
Primary Key: user_id
Orders
| Column Name | Type | Description |
|---|---|---|
order_id
PK
|
int | Primary key, unique order identifier |
order_date
|
date | Date when order was placed |
item_id
|
int | Foreign key to Items table |
buyer_id
|
int | Foreign key to Users table, user who bought |
seller_id
|
int | Foreign key to Users table, user who sold |
Primary Key: order_id
Items
| Column Name | Type | Description |
|---|---|---|
item_id
PK
|
int | Primary key, unique item identifier |
item_brand
|
varchar | Brand of the item |
Primary Key: item_id
Input & Output
Example 1 — Multiple Users with Different Order Counts
Input Tables:
Users
| user_id | join_date | favorite_brand |
|---|---|---|
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
Orders
| order_id | order_date | item_id | buyer_id | seller_id |
|---|---|---|---|---|
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2019-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-17 | 2 | 2 | 3 |
| 4 | 2018-08-17 | 2 | 1 | 2 |
Items
| item_id | item_brand |
|---|---|
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
Output:
| buyer_id | join_date | orders_in_2019 |
|---|---|---|
| 1 | 2018-01-01 | 2 |
| 2 | 2018-02-09 | 1 |
| 3 | 2018-01-19 | 0 |
💡 Note:
User 1 made 2 orders in 2019 (orders 1 and 2). User 2 made 1 order in 2019 (order 3). User 3 made no orders in 2019. The 2018 order (order 4) is not counted.
Example 2 — User with No Orders
Input Tables:
Users
| user_id | join_date | favorite_brand |
|---|---|---|
| 1 | 2018-01-01 | Lenovo |
Orders
| order_id | order_date | item_id | buyer_id | seller_id |
|---|
Items
| item_id | item_brand |
|---|---|
| 1 | Samsung |
Output:
| buyer_id | join_date | orders_in_2019 |
|---|---|---|
| 1 | 2018-01-01 | 0 |
💡 Note:
User 1 exists but made no orders at all, so their order count for 2019 is 0. LEFT JOIN ensures the user still appears in the result.
Constraints
-
1 ≤ Users.user_id ≤ 1000 -
join_dateis a valid date -
order_dateis a valid date - All foreign key relationships are valid
Visualization
Tap to expand
Understanding the Visualization
1
Users Table
All registered users with join dates
2
LEFT JOIN
Join with Orders on buyer_id
3
Result
Each user with their 2019 order count
Key Takeaway
🎯 Key Insight: LEFT JOIN preserves all users while conditional COUNT handles the year filter
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code