Analyze and Clean European Ecommerce Sales Data
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
sales
+----------+-------------+------------+---------+---------+----------------+
| order_id | customer_id | order_date | country | revenue | product_category|
+----------+-------------+------------+---------+---------+----------------+
| 1001 | 501 | 2023-08-01 | US | 250.00 | Accessories |
| 1002 | 502 | 2023-08-02 | UK | 180.00 | Apparel |
| 1003 | 503 | 2023-08-02 | DE | 310.00 | Electronics |
| 1004 | 501 | 2023-08-03 | US | 95.00 | Accessories |
| 1005 | 504 | 2023-08-04 | CA | 420.00 | Electronics |
+----------+-------------+------------+---------+---------+----------------+
##### Scenario
You receive a raw ecommerce sales table exported from multiple regional warehouses and must create a clean dataset for analysts.
##### Question
Use Pandas to retrieve all European orders placed in the last 30 days and compute their total revenue. 2. Identify and remove duplicated orders, then explain how you would impute any missing revenue values. 3. Produce summary statistics and two visual insights that highlight sales trends by product_category.
##### Hints
Pandas filtering, groupby-agg, isnull, fillna, duplicated, matplotlib/seaborn.
Overview: This question evaluates proficiency in data manipulation and analysis with Pandas, covering time-window filtering, regional subsetting, deduplication, missing-value handling, aggregation, and visual summarization.
Using SQL on the sales table, return a deduplicated set of European orders (defined as country IN ('UK','DE')) between '2025-05-03' and '2025-06-01', impute any missing revenue using customer-level average then category-level average then global average, and produce product_category-level summary statistics along with an overall total revenue.
Tables
sales(order_id INTEGER, customer_id INTEGER, order_date DATE, country VARCHAR(2), revenue DECIMAL(10,2), product_category VARCHAR(50))
Hints
- Filter to European orders with WHERE country IN ('UK','DE') and the date range order_date BETWEEN '2025-05-03' AND '2025-06-01'.
- Remove duplicate order_ids using ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date DESC, revenue DESC NULLS LAST) and keep only rn = 1.