Visualize and Clean SKU Sales Data for Outliers

sales_data

+------------+--------+-----------+----------+------------+---------+
| date       | sku_id | unit_sold | revenue  | promo_flag | store_id|
+------------+--------+-----------+----------+------------+---------+
| 2023-01-01 | A123   | 120       | 2400.00  | 1          | S01     |
| 2023-01-02 | A123   | 80        | 1600.00  | 0          | S01     |
| 2023-01-01 | B456   | 200       | 3000.00  | 1          | S02     |
| 2023-01-02 | B456   | 50        | 750.00   | 0          | S02     |

Scenario

Codesignal live-coding: analyst receives raw daily SKU sales data and must explore it visually while cleaning extreme values.

Question

Using Python (pandas, matplotlib/seaborn), draw a histogram of daily revenue per SKU and identify outliers with the IQR or Z-score method. Remove the detected outliers and re-plot the cleaned distribution.

Hints

Focus on reproducible pandas pipeline: load → aggregate → detect outliers → filter → visualize before/after.

Flag Daily Revenue Outliers Per SKU Using IQR

You are given raw daily SKU sales data at the transaction level. Write a SQL query that:

1. Aggregates revenue to the level of (date, sku_id) as daily_revenue.
2. For each sku_id, computes the 25th percentile (Q1) and 75th percentile (Q3) of daily_revenue using percentile_cont.
3. Derives the IQR-based outlier bounds: lower_bound = Q1 - 1.5 * (Q3 - Q1) and upper_bound = Q3 + 1.5 * (Q3 - Q1).
4. Flags each (date, sku_id) daily_revenue as an outlier if it falls outside these bounds. Return one row per (date, sku_id) with the daily_revenue, Q1, Q3, lower_bound, upper_bound, and a boolean is_outlier flag.

Table sales_data

column	type	constraints
date	DATE	NOT NULL
sku_id	VARCHAR(10)	NOT NULL
unit_sold	INTEGER	NOT NULL, CHECK (unit_sold >= 0)
revenue	DECIMAL(12,2)	NOT NULL, CHECK (revenue >= 0)
promo_flag	INTEGER	NOT NULL, CHECK (promo_flag IN (0,1))
store_id	VARCHAR(10)	NOT NULL

Sample data:

date	promo_flag	revenue	sku_id	store_id	unit_sold
2023-01-01	1	2400	A123	S01	120
2023-01-02	0	1600	A123	S01	80
2023-01-01	1	3000	B456	S02	200
2023-01-02	0	750	B456	S02	50

Expected output

A per-date, per-SKU aggregation of revenue with per-SKU IQR-based bounds and a boolean flag indicating whether each daily_revenue value is an outlier. For the provided sample data, no outliers are detected; the cleaned distribution equals the original.

daily_revenue	date	is_outlier	lower_bound	q1	q3	sku_id	upper_bound
2400	2023-01-01	false	1200	1800	2200	A123	2800
1600	2023-01-02	false	1200	1800	2200	A123	2800
3000	2023-01-01	false	-375	1312.5	2437.5	B456	4125
750	2023-01-02	false	-375	1312.5	2437.5	B456	4125

Hints

1. First aggregate to daily revenue per SKU: SELECT date, sku_id, SUM(revenue) AS daily_revenue FROM sales_data GROUP BY date, sku_id.
2. Use percentile_cont(0.25) and percentile_cont(0.75) WITHIN GROUP (ORDER BY daily_revenue) over each sku_id to compute Q1 and Q3.
3. Compute IQR = Q3 - Q1 and define lower_bound = Q1 - 1.5 * IQR, upper_bound = Q3 + 1.5 * IQR.
4. Flag outliers as daily_revenue values outside [lower_bound, upper_bound]; this flagged dataset can then be exported to Python for before/after histograms.