Analyze and Clean European Ecommerce Sales Data

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.

European Orders Cleaning and Revenue Summary

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.

Table sales

column	type	constraints
order_id	INTEGER	NOT NULL
customer_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL
revenue	DECIMAL(10,2)
product_category	VARCHAR(50)

Sample data:

country	customer_id	order_date	order_id	product_category	revenue
US	501	2025-05-10	1001	Accessories	250
UK	502	2025-05-15	1002	Apparel	180
DE	503	2025-05-16	1003	Electronics	310
US	501	2025-05-20	1004	Accessories	95
CA	504	2025-05-25	1005	Electronics	420

Expected output

Using the explicit date range from '2025-05-03' to '2025-06-01', the European orders (country IN ('UK','DE')) are order_id 1002 (UK, Apparel) and 1003 (DE, Electronics). In the sample data there are no duplicate order_ids and no missing revenue, so deduplication and imputation do not change values. The query should output summary statistics by product_category for these orders, plus an overall total row with NULL product_category.

avg_revenue	order_count	product_category	total_revenue
310	1	Electronics	310
180	1	Apparel	180
245	2	NULL	490

Hints

1. Filter to European orders with WHERE country IN ('UK','DE') and the date range order_date BETWEEN '2025-05-03' AND '2025-06-01'.
2. 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.
3. Impute revenue with COALESCE(revenue, AVG(revenue) OVER (PARTITION BY customer_id), AVG(revenue) OVER (PARTITION BY product_category), AVG(revenue) OVER ()).
4. Compute summary statistics by product_category and an overall total using GROUP BY GROUPING SETS or ROLLUP.