Unsold Products
EasyPremium
Given the schema shown below, write a solution to fetch the product name and stock of products that have never been purchased before (if any), ordered by product id.
users products +---------------+---------+ +-----------------+---------+ +--| id | int | +-----| id | int | | | first_name | varchar | | | name | varchar | | | last_name | varchar | | +->| product_line_id | date | | | user_city | int | | | | stock | int | | | email | int | | | +-----------------+---------+ | +---------------+---------+ | | | | | | transactions | | product_lines | +---------------+---------+ | | +--------+--------+ | | id | int |<----+ +--| id | int | +---->| customer_id | int | | name | varchar| | product_id | int | +--------+--------+ | amount | int | | currency_code | varchar | | date | date | +---------------+---------+ exchange_rate +----------------------+---------+ | id | int | | source_currency_code | varchar | | target_currency_code | varchar | | rate | numeric | +----------------------+---------+
Your solution should return a result in the following format:
id | name | stock ----+---------+------- int | varchar | int
To fetch the products that have never been purchased, we shall follow the following steps:
- Extract Purchased Product IDs: From
transactions, extract all uniqueproduct_ids that have been purchased. - Filter Unsold Products: Use
productsto filter out products whose id is not in the list of purchased product IDs. - Select Desired Columns: Select the
id,name, andstockcolumns for the resulting unsold products. - Order the results: Sort by product id
Pythondef find_unsold_products(transactions: pd.DataFrame, products: pd.DataFrame) -> pd.DataFrame:
# Extract purchased product IDs
purchased_product_ids = transactions['product_id'].unique()
# Filter products that have never been purchased
unsold_products = products[~products['id'].isin(purchased_product_ids)]
# Select the desired columns
result = unsold_products[['id', 'name', 'stock']]
# Sort the result by product ID in ascending order
result = result.sort_values(by='id')
return result
products_sold = set(transactions['product_id'])unsold_products = products.loc[~products['id'].isin(products_sold)]return unsold_products[["id", "name", "stock"]]