📚 100 DAYS SQL

CODING CHALLENGE

DAY 12

Master SQL One Problem at a Time

✨ Daily SQL Problems
✨ Real-World Scenarios
✨ Complete Solutions
✨ Interview Ready

🔧 E-Commerce Data Cleaning Pipeline

Database Schema - RAW DATA (Messy!)

Table 1: raw_transactions - Sample data (10 rows total)

trans_id	customer_email	product_sku	amount	trans_date	status
TX001	JOHN@EMAIL.COM	SKU-123-A	$125.50	2024-01-15 14:30:00	completed
TX002	sarah.j@invalid	sku-456-b	45	15/01/2024	PENDING
TX003	mike@shop.com	SKU-789-C	$0	2024-01-20	Cancelled
TX004	emma@store.io	sku-123-a	125.50	2024/01/25 10:15	COMPLETED

...and 6 more messy rows! Download the full dataset above.

Table 2: product_catalog - Clean reference data

sku	product_name	category	price
SKU-123-A	Wireless Mouse	Electronics	125.50
SKU-456-B	USB Cable	Accessories	45.00
SKU-789-C	Laptop Stand	Furniture	299.99

Table 3: customer_profiles - Also messy data!

email	full_name	join_date	country
john@email.com	john smith	2023-05-10	USA
SARAH.J@INVALID	Sarah Jones	05/10/2023	uk
mike@shop.com	MIKE WILSON	2023-06-15	Canada

📋 Problem Requirements

Build a complete data cleaning pipeline using SQL to transform messy e-commerce data into clean, analytics-ready format.

🧹 Data Cleaning Tasks:

Standardize emails: trim spaces, lowercase
Standardize SKU: uppercase format
Clean amount: remove $, handle NULL, negative values
Standardize dates: convert all to YYYY-MM-DD format
Standardize status: lowercase, consistent values
Clean names: proper case (Title Case), trim spaces
Standardize country: uppercase

🔗 JOIN & Enrichment:

JOIN with product_catalog to get product details
JOIN with customer_profiles to get customer info
LEFT JOIN to keep all transactions (even without matches)
Flag orphan records (transactions with no product/customer match)

✅ Validation & Quality Checks:

Validate email format using REGEXP pattern
Flag amount mismatches with catalog price
Identify potential duplicate transactions
Calculate data quality score for each record (0-100)
Categorize quality: Excellent (90+), Good (70-89), Poor (<70)

💡 SQL Skills Required: TRIM, UPPER, LOWER, REPLACE, CASE, CAST, STR_TO_DATE, REGEXP_LIKE, JOINs, CTEs, COALESCE, NULLIF

This is exactly what Data Engineers do daily - cleaning messy data from various sources!

🎉 Day 12 Complete!

Congratulations!

You've learned complete data cleaning using all SQL concepts! This is exactly what Data Engineers work with in real-world scenarios - messy data from multiple sources that needs standardization, validation, and quality scoring.

🔗 Connect & Follow

Join me on the 100 Days SQL journey!

LinkedIn: linkedin.com/in/rahulkanche
GitHub: github.com/rahulkanche/100-days-sql-data

💪 Keep Practicing!

✓ Practice on messy real-world data
✓ Build complete cleaning pipelines
✓ Master data quality validation
✓ Prepare for Data Engineering interviews

Follow the 100 Days SQL Challenge
Master SQL one problem at a time! 🚀

Rahul Kanche — exploring data, decoding clarity!