1158 Market Analysis I

Description

Table: Users

• user_id is the primary key 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

• order_id is the primary key of this table.
• item_id is a foreign key to the Items table.
• buyer_id and seller_id are foreign keys to the Users table.

Table: Items

• item_id is the primary key of this table.

Problem Statement

Write an SQL query 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 query result format is in the following example.

Example 1:

Input:

• Users table:

• Orders table:

• Items table:

Output:

Solution

The problem has several parts.

1. You need to find orders from the year 2019. For this, you can use order_date from Orders table to filter orders in 2019. You can use WHERE order_date > '2018-12-31' AND order_date < '2020-01-01' clause. Alternatively, you could also use YEAR() function to convert dates into year using WHERE YEAR(order_date) = 2019.
2. The problem needs to find number of orders per buyer. For this you need buyer_id, join_date from Users table and you need to find the number of records from Orders table. Hence, you need to join these two tables.

1SELECT 
2    o.buyer_id, u.join_date
3    FROM Users u
4    JOIN Orders o
5    ON u.user_id = o.buyer_id

3. The next task is to find the number of orders for each buyer_id. For this, you can use COUNT(order_id) from the Orders table.

1SELECT 
2    o.buyer_id, u.join_date, COUNT(order_id) orders_in_2019
3    FROM Users u
4    JOIN Orders o
5    ON u.user_id = o.buyer_id
6    WHERE o.order_date > '2018-12-31' AND o.order_date < '2020-01-01'
7    GROUP BY o.buyer_id;

This would take us closer to the result we want. This produces below result.

This produces correct result but is missing user_id 3 and 4. This is because they have had no order in 2019. Now, let’s suppose you perform LEFT OUTER JOIN, even then you don’t get the output for the other two users. This means below query will not produce the other two rows.

1SELECT 
2    u.user_id, u.join_date, COUNT(order_id) orders_in_2019
3    FROM Users u
4    LEFT OUTER JOIN Orders o
5    ON u.user_id = o.buyer_id
6    WHERE o.order_date > '2018-12-31' AND o.order_date < '2020-01-01'
7    GROUP BY u.user_id;

This is because you’re filtering for orders in 2019. However, if you include that filter in the RIGHT view, then you may be able to retrieve the other two users. You should use this as a JOIN clause.

1    Users u
2    LEFT OUTER JOIN (SELECT buyer_id, order_id FROM Orders WHERE YEAR(order_date) = 2019) o

You also need to remove the WHERE clause from the outer SELECT statement. The final solution looks like this.

1SELECT 
2    u.user_id buyer_id, u.join_date, COUNT(order_id) orders_in_2019
3    FROM Users u
4    LEFT OUTER JOIN (
5        SELECT buyer_id, order_id, order_date FROM Orders WHERE YEAR(order_date) = 2019
6    ) o
7    ON u.user_id = o.buyer_id
8    GROUP BY u.user_id;