Posts

ASSIGNMENT 1: SQL MASTERY - The E-Commerce Analytics Challenge

 ASSIGNMENT 1: SQL MASTERY - The E-Commerce Analytics Challenge Topics Covered/focused: SQL Manipulation, Retrieval, Aggregation, Joins, Subqueries, CTEs, Window Functions, Stored Procedures, Indexes Duration: 7-8 hours | Questions**: 30 Problem Statement You're hired as a Data Engineer at "ShopHub” - a fast-growing e-commerce platform. The company has raw transactional data scattered across multiple systems. Your job is to design, optimize, and create a complete analytics database. Dataset Brazilian E-Commerce Public Dataset by Olist -> Link**: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce - > Size**: 100K orders, 8 CSV files (~45 MB) - > Files**: customers, orders, order_items, products, sellers, order_payments, order_reviews, geolocation - > Features**: Real data with nulls, duplicates, multi-table relationships - > Alternative**: https://github.com/olist/brazilian-ecommerce (same dataset) Hints/ insights about dataset : a) Real production-quali...
Post a Comment
Read more

Solution : SQL Mastery – The E-Commerce Analytics Challenge

  SQL Mastery – The E-Commerce Analytics Challenge Project Overview Dataset: Brazilian E-Commerce Public Dataset by Olist Kaggle: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce Alternative GitHub: https://github.com/olist/brazilian-ecommerce Recommended Database: PostgreSQL 15+ Folder Structure: project/ │ ├── 01_schema/ │ ├── create_tables.sql │ ├── constraints.sql │ ├── indexes.sql │ └── erd.png │ ├── 02_data_cleaning/ │ ├── staging_tables.sql │ ├── data_quality_checks.sql │ ├── duplicate_removal.sql │ └── transactions_demo.sql │ ├── 03_queries/ │ ├── aggregations.sql │ ├── joins.sql │ ├── subqueries_ctes.sql │ └── window_functions.sql │ ├── 04_optimization/ │ ├── explain_analyze.sql │ └── optimization_report.md │ ├── reports/ │ ├── data_dictionary.md │ ├── data_quality_report.md │ └── final_summary.md │ └── video_walkthrough.mp4 PART A – Database Design & Data Quality 1. Normalized Database Schema (3NF) Tables and Relationships ...
Post a Comment
Read more