Data EngineeringPlatform: 1_Data_Science_Omkar

Database Design & SQL

1. Context & Objective

A deep dive into relational database architecture, focusing on normalization, writing complex queries, window functions, and designing schemas for e-commerce logic.

2. Methodology

1. Designed ER diagrams converting business logic to schema. 2. Normalized tables up to 3NF to reduce data redundancy. 3. Practiced complex recursive CTEs and performance tuning with indexes. 4. Created analytical views spanning multiple fact and dimension tables.

In [1]:

WITH RankedSales AS (
  SELECT 
    employee_id,
    amount,
    date,
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY amount DESC) as rnk
  FROM sales
)
SELECT employee_id, amount
FROM RankedSales
WHERE rnk <= 3;

3. Final Learnings

Understanding execution plans is critical for optimizing queries on millions of rows. Proper indexing on foreign keys drastically reduced JOIN latency.

Dataset details

Language

SQL

Size

Libraries Used

MySQLPostgreSQL