Market analysis ii

# 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 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:
# +---------+------------+----------------+
# | user_id | join_date  | favorite_brand |
# +---------+------------+----------------+
# | 1       | 2019-01-01 | Lenovo         |
# | 2       | 2019-02-09 | Samsung        |
# | 3       | 2019-01-19 | LG             |
# | 4       | 2019-05-21 | HP             |
# +---------+------------+----------------+
# Orders table:
# +----------+------------+---------+----------+-----------+
# | order_id | order_date | item_id | buyer_id | seller_id |
# +----------+------------+---------+----------+-----------+
# | 1        | 2019-08-01 | 4       | 1        | 2         |
# | 2        | 2019-08-02 | 2       | 1        | 3         |
# | 3        | 2019-08-03 | 3       | 2        | 3         |
# | 4        | 2019-08-04 | 1       | 4        | 2         |
# | 5        | 2019-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:
# +-----------+--------------------+
# | seller_id | 2nd_item_fav_brand |
# +-----------+--------------------+
# | 1         | no                 |
# | 2         | yes                |
# | 3         | yes                |
# | 4         | no                 |
# +-----------+--------------------+
# 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.

import pandas as pd

def market_analysis(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:
  orders_sorted = orders.sort_values('order_date')
  orders_sorted['rank'] = orders_sorted.groupby('seller_id').cumcount() + 1
  second_items = orders_sorted[orders_sorted['rank'] == 2]
  second_with_brand = second_items.merge(items, on='item_id')
  users_with_second = users.merge(second_with_brand[['seller_id', 'item_brand']], left_on='user_id', right_on='seller_id', how='left')
  users_with_second['2nd_item_fav_brand'] = (users_with_second['item_brand'] == users_with_second['favorite_brand']).apply(lambda x: 'yes' if x else 'no')
  return users_with_second[['user_id', '2nd_item_fav_brand']].rename(columns={'user_id': 'seller_id'})