1159 Market Analysis II

Description

Table: Users

• 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

• 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

• item_id is the primary key (column with unique values) of this table.

Problem Statement

Write a solution to find for each user whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as ’no’. It is guaranteed that no seller sells more than one item in a day.

Return the result table in any order. The result format is in the following example.

Example 1:

Input:

• Users table:

• Orders table:

• Items table:

Output:

Explanation:

• The answer for the user with id 1 is no because they sold nothing.
• The answer for the users with id 2 and 3 is yes because the brands of their second sold items are their favorite brands.
• The answer for the user with id 4 is no because the brand of their second sold item is not their favorite brand.

solution

The problem can be solved in step-by-step manner.

1. First, you need to find the second order for each seller. For this, you can use ROW_NUMBER() window function on the Orders table.

1WITH second_orders AS (
2    SELECT seller_id, item_id,
3        ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) row_num
4        FROM Orders
5) SELECT * FROM second_orders
6    WHERE row_num = 2;

2. Now, if you want to check whether it’s user’s favorite brand, you need to check with Items table. Because Users table does not have reference to item_id but it has reference of item_brand as favorite_brand, you need to fetch brand information from Items table by joining with it.

 1WITH second_orders AS (
 2    SELECT seller_id, item_id,
 3        ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) row_num
 4        FROM Orders
 5), second_order_with_brand AS (
 6    SELECT so.seller_id, i.item_brand
 7        FROM second_orders so
 8        JOIN Items i
 9        ON so.item_id = i.item_id
10        WHERE so.row_num = 2
11) SELECT * FROM second_order_with_brand;

3. Here, we have information for only 3 users, but in the output we need to report for each user. So, we need to join with Users table with Users LEFT OUTER JOIN second_order_with_brand. At this point, you can also check if the item_brand from second_order_with_brand matches favorite_brand from Users table. If yes, then you put yes as 2nd_item_fav_brand else no.

 1WITH second_orders AS (
 2    SELECT seller_id, item_id,
 3        ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) row_num
 4        FROM Orders
 5) , second_order_with_brand AS (
 6    SELECT so.seller_id, i.item_brand
 7        FROM second_orders so
 8        JOIN Items i
 9        ON so.item_id = i.item_id
10        WHERE so.row_num = 2
11) SELECT
12    u.user_id seller_id,
13    CASE WHEN u.favorite_brand = sb.item_brand THEN 'yes' ELSE 'no' END AS 2nd_item_fav_brand
14    FROM Users u
15    LEFT OUTER JOIN second_order_with_brand sb
16    ON u.user_id = sb.seller_id;