Is the second item sold the seller's favourite brand?

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

An online marketplace has sellers(seller_id, favourite_brand), market_items(item_id, item_brand), and seller_orders(order_id, order_date, item_id, seller_id). For every seller, check whether the second item they sold (chronologically by order_date) belongs to their favourite brand. If so, return 'yes'. If not, or if the seller has sold fewer than 2 items, return 'no'. Return seller_id and second_item_fav_brand, ordered by seller_id.

Requirements

• Output columns: seller_id, second_item_fav_brand.
• Order by seller_id ascending.

Constraints

• No seller has two sales on the exact same date.
• seller_id is unique in sellers.
• second_item_fav_brand must be either 'yes' or 'no'.

Examples

Input: sellers seller_id,favourite_brand 1,Tata 2,Bajaj 3,Reliance 4,Hero market_items item_id,item_brand 101,Tata 102,Bajaj 103,Reliance 104,Hero seller_orders order_id,order_date,item_id,seller_id 1,2024-08-01,102,1 2,2024-08-04,101,1 3,2024-08-02,101,2 4,2024-08-05,103,2 5,2024-08-03,103,3 Output: seller_id | second_item_fav_brand 1 | yes 2 | no 3 | no 4 | no Why this passes: Seller 1's second sold item (by date) is item 101, a Tata, which is their favourite brand, so yes; seller 4 sold nothing, so no.

Input: sellers seller_id | favourite_brand 1 | Apple seller_orders order_id | order_date | item_id | seller_id 1 | 2024-01-01 | 10 | 1 2 | 2024-01-02 | 20 | 1 market_items item_id | item_brand 10 | Dell 20 | Apple Output: seller_id | second_item_fav_brand 1 | yes Why this passes: Seller 1 sold item 20 second, which is their favourite brand Apple.

Topics: lakebench, sql, nth-row, left-join.

More SQL interview questions · All interview problems · Learn data engineering