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 | +-----------+------------+----------------+
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.
C
Python
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.
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.
C++
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 | 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. |
LeetCode was HARD until I Learned these 15 Patterns • Ashish Pratap Singh • 1,002,155 views views
Watch 9 more video solutions →Practice Market Analysis I with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor