Market Analysis I - Solution & Explanation
Problem Statement
Table: Users
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id is the primary key (column with unique values) of this table. This table has the info of the users of an online shopping website where users can sell and buy items.
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ order_id is the primary key (column with unique values) of this table. item_id is a foreign key (reference column) to the Items table. buyer_id and seller_id are foreign keys to the Users table.
Table: Items
+---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ item_id is the primary key (column with unique values) of this table.
Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Users table: +---------+------------+----------------+ | user_id | join_date | favorite_brand | +---------+------------+----------------+ | 1 | 2018-01-01 | Lenovo | | 2 | 2018-02-09 | Samsung | | 3 | 2018-01-19 | LG | | 4 | 2018-05-21 | HP | +---------+------------+----------------+ Orders table: +----------+------------+---------+----------+-----------+ | order_id | order_date | item_id | buyer_id | seller_id | +----------+------------+---------+----------+-----------+ | 1 | 2019-08-01 | 4 | 1 | 2 | | 2 | 2018-08-02 | 2 | 1 | 3 | | 3 | 2019-08-03 | 3 | 2 | 3 | | 4 | 2018-08-04 | 1 | 4 | 2 | | 5 | 2018-08-04 | 1 | 3 | 4 | | 6 | 2019-08-05 | 2 | 2 | 4 | +----------+------------+---------+----------+-----------+ Items table: +---------+------------+ | item_id | item_brand | +---------+------------+ | 1 | Samsung | | 2 | Lenovo | | 3 | LG | | 4 | HP | +---------+------------+ Output: +-----------+------------+----------------+ | buyer_id | join_date | orders_in_2019 | +-----------+------------+----------------+ | 1 | 2018-01-01 | 1 | | 2 | 2018-02-09 | 2 | | 3 | 2018-01-19 | 0 | | 4 | 2018-05-21 | 0 | +-----------+------------+----------------+
Approach Overview
Problem Overview: You are given three tables: Users, Orders, and Items. For every user, return their user_id, join_date, and the number of orders they placed in the year 2019. Users with no orders in 2019 must still appear in the result with a count of 0. The core challenge is correctly joining user records with filtered order data while preserving users who have no matching rows.
Approach 1: SQL Join with Subquery (O(U + O) time, O(1) extra space)
This approach filters orders from 2019 first, then aggregates them per buyer before joining with the Users table. A subquery scans the Orders table, applies a YEAR(order_date) = 2019 condition, and groups results by buyer_id using COUNT(*). The outer query performs a LEFT JOIN between Users and the aggregated result so that users without orders still appear. Missing counts are replaced with 0 using COALESCE. This pattern keeps aggregation isolated and simplifies the final join, which is common in SQL interview problems involving reporting queries.
Approach 2: Iterative Filtering and Counting (O(U + O) time, O(U) space)
In languages like Java or C++, you can simulate the database logic using collections. First iterate through the orders list and filter entries where order_date belongs to 2019. Maintain a hash map that maps buyer_id → order_count, incrementing the value each time a qualifying order appears. Next iterate through the users list and construct the result by reading counts from the map, defaulting to 0 if the user never appears. This mirrors a manual hash map aggregation followed by a lookup join. The method is useful when solving database-style questions in general programming interviews or when implementing analytics logic outside a database.
Both strategies rely on the same insight: filter orders to the relevant year first, then aggregate by buyer. The SQL solution relies on relational operations like LEFT JOIN and GROUP BY, while the iterative solution uses hash-based counting and lookup logic often seen in database-style problems implemented in application code.
Recommended for interviews: The SQL aggregation with LEFT JOIN is the expected answer for database-focused interviews. It shows that you understand join semantics, grouping, and how to preserve unmatched rows. Implementing the iterative counting approach demonstrates deeper understanding of how database joins and aggregations work under the hood.
Approach 1: SQL Join with Subquery
This approach involves using SQL JOIN operations to merge relevant data from the Users and Orders tables. We focus on selecting only those orders where order_date falls in the year 2019, and then count the occurrences for each user.
We're leveraging an SQL LEFT JOIN to ensure that all users appear in the result, even if they haven't made any orders. We join Users with a filtered subquery of Orders (where the year is 2019), count the number of orders for each user, and group the results by user ID.
Complexity
The time complexity is approximately O(n + m), where n is the number of users and m is the number of orders in 2019. The space complexity is O(n) for storing the result set.
Approach 2: Iterative Filtering and Counting
This approach is more general-purpose and involves looping through the datasets of Users and Orders, filtering appropriate records, and calculating the desired order count for each user manually without relying on direct SQL tools.
This Java implementation traverses the list of orders, filtering by date to the year 2019 and populating a map with counts of orders per buyer. It then iterates over users, matching and appending their order count data.
Complexity
The time complexity is approximately O(n + m), given that we iterate through each collection separately. The process involves two main passes: one for collating the order data and another for joining this data with user data. The space complexity is O(m), where we preserve order counts in a map.
Approach 3: Default Approach
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| SQL Join with Subquery | The time complexity is approximately O(n + m), where n is the number of users and m is the number of orders in 2019. The space complexity is O(n) for storing the result set. |
| Iterative Filtering and Counting | The time complexity is approximately O(n + m), given that we iterate through each collection separately. The process involves two main passes: one for collating the order data and another for joining this data with user data. The space complexity is O(m), where we preserve order counts in a map. |
| Default Approach | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join with Subquery | O(U + O) | O(1) | Best for SQL interviews and relational database queries where aggregation and joins are required |
| Iterative Filtering and Counting | O(U + O) | O(U) | Useful when implementing database-style aggregation in application code using maps |
Video Solution
LeetCode Medium 1158 Interview SQL Question with Detailed Explanation • Everyday Data Science • 4,615 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Market Analysis I easy or hard?
Market Analysis I Python/Java solution
How to solve Market Analysis I in O(n)?
What is the best approach for Market Analysis I?
Is Market Analysis I asked at Google/Amazon/Meta?
What data structure is used in Market Analysis I?
What is the time complexity of Market Analysis I?
Ready to solve this problem?
Practice Market Analysis I with our built-in code editor and test cases.
Practice on FleetCode