Compute and Rank Store Revenue by Region Using Pandas

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Apple.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Apple during technical interviews.

Question

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.

PracHub · Accepted Answer

This question evaluates proficiency in pandas-based data manipulation, specifically competencies in aggregating revenue, merging store metadata, and producing ranked regional summaries.

Quick Overview

Quick Overview