Compute and Rank Store Revenue by Region Using Pandas
Company: Apple
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Sales
+------------+---------+------------+--------+---------+
| date | store_id| product_id | units | revenue |
+------------+---------+------------+--------+---------+
|2023-01-01 |1 |101 |3 |30.00 |
|2023-01-01 |1 |102 |2 |40.00 |
|2023-01-02 |2 |101 |5 |50.00 |
|2023-01-02 |2 |103 |1 |20.00 |
|2023-01-02 |3 |101 |4 |40.00 |
+------------+---------+------------+--------+---------+
Stores
+---------+---------+
| store_id| region |
+---------+---------+
|1 | West |
|2 | East |
|3 | Central |
+---------+---------+
##### Scenario
Pandas data-wrangling coding task on sales data
##### Question
Using pandas, compute total revenue per store per day from the sales table. Merge the sales table with the stores table on store_id, then list the top three regions by total revenue.
##### Hints
Use groupby, agg, merge, sort_values, and reset_index appropriately.
Overview: This question evaluates proficiency in pandas-based data manipulation, specifically competencies in aggregating revenue, merging store metadata, and producing ranked regional summaries.
Using the Sales and Stores tables, first compute total revenue per store per day from the Sales table. Then join this daily store revenue to the Stores table on store_id, aggregate total revenue by region across all days, and return the top three regions by total revenue in descending order.
Tables
Sales(date DATE, store_id INTEGER, product_id INTEGER, units INTEGER, revenue DECIMAL(10,2))
Stores(store_id INTEGER, region VARCHAR(20))
Hints
- Group sales by date and store_id and sum revenue.
- Join the aggregated results to Stores on store_id to get region.
Community answers
Answer by SS
import pandas as pd daily_revenue = sales.groupby(['store_id','date']).agg(daily_rev=('revenue','sum')).reset_index()final = stores.merge(daily_revenue, on = 'store_id', how = 'inner')final1 = final.groupby(['region']).agg(total_revenue = ('daily_rev','sum')).reset_index()final1.sort_values(by = 'total_revenue' , ascending = False).head(3)