SQL Projects

The Olist dataset arrives as relational CSVs with real data-quality problems: a hidden customer duplication issue, impossible order states, timestamps that contradict each other, missing product metadata, and heavily duplicated geolocation records. This project builds a repeatable pipeline that turns that raw data into a clean, query-ready warehouse.

Bronze layer: raw source tables loaded as-is from the CSVs, with no transformation.

Silver layer: cleaned and validated data, including:

• Deduplicated customers: customer_id was unique, but ~3,345 repeated customer_unique_id values meant the same person had several records. Kept one row per real customer using ROW_NUMBER() partitioned on customer_unique_id
• Checked NULL patterns against order_status before treating any as an error, since a canceled order is legitimately never approved. Removed 14 "delivered" orders still missing approval, carrier, and delivery timestamps, which is logically impossible
• Nulled order_approved_at on 1,359 orders where carrier pickup preceded approval. Time gaps started at 70+ seconds and climbed, which ruled out a simple logging quirk, so the less reliable field was nulled instead of dropping real orders. Applied the same logic to 23 rows of order_delivered_customer_date
• Defaulted 610 products with no category to 'unknown' instead of dropping them, because they carry real sales in order_items and dropping them would silently delete revenue
• Collapsed ~128,000 exact duplicate geolocation rows with DISTINCT, and deliberately left the multi-city ambiguity for the Gold layer
• Dropped 2 credit-card payments with 0 installments as a real inconsistency, and kept 494 order items with freight_value = 0 after confirming they were legitimate free-shipping cases

Gold layer: a star schema exposed as views — 4 dimensions (dim_customers, dim_sellers, dim_products, dim_geolocation) and 3 facts (fact_orders, fact_order_items, fact_payments). The zip-prefix grain problem is resolved here by picking the most frequent city/state per prefix and averaging lat/lng, since that is a business decision rather than a cleaning one. Fact joins use LEFT JOIN so valid rows are not dropped, and fact_orders resolves duplicate reviews with a ROW_NUMBER() CTE.

The Silver load runs through one stored procedure, silver.load_silver, with TRY/CATCH error handling and per-table load timing. Gold objects are views rather than physical tables, so Gold always reflects the latest Silver load with no separate refresh step. dim_date is intentionally left out of the SQL layer because Power BI generates its own date table with DAX's CALENDAR().

The repo is organized as seven numbered scripts: database and schemas, bronze import, bronze validation, silver schema, silver load procedure, gold layer, and gold validation (row counts, duplicate checks, and revenue reconciliation), plus an architecture diagram for each layer.

• Design the fact table grain and degenerate-dimension columns, such as order_purchase_timestamp needing to live on every fact table for date filtering, before building the Power BI model instead of discovering the need halfway through
• Write data-quality checks as a separate, reusable script from the start, rather than untangling them from the load logic
• Decide what NULL means for each column before writing any filtering logic. Most rework came from treating "missing" as one category when it was several different situations

The project uses four tables: Employees (role, department, start and exit dates, status, performance score, termination type), EmployeeEngagement (engagement, satisfaction, and work-life balance scores), EmployeeTraining (training type, date, and cost), and Recruitment (applicant job title, experience, desired salary, status).

• Attrition: identified exits from employee status, standardized termination types, and calculated the attrition rate and department-wise exits
• Performance: ranked employees on ratings and performance score, found top performers per department, and grouped employees into high, average, and low performers
• High-risk employees: flagged employees combining low engagement with poor performance ratings
• Training: calculated total training cost per employee, compared training count against performance, and identified high-investment areas
• Recruitment: counted applicants per role, found the most in-demand job titles, and calculated the applicant-to-employee conversion rate
• Satisfaction: compared satisfaction by department to find the lowest-scoring ones

Queries combine the four tables with INNER and LEFT JOINs, then use aggregates (COUNT, AVG, SUM), GROUP BY / HAVING, window functions (RANK, ROW_NUMBER), CTEs, and subqueries to answer each business question.

Data challenges handled:

• TerminationType held inconsistent values (for example "Voluntary" vs "Resignation"), which I standardized with CASE statements
• Some tables had only one record per employee, so I adjusted the aggregation logic instead of forcing per-employee rollups

The data comes from two tables, Students_Math and Students_Portuguese, with academic features (G1, G2, G3, study time, failures), behavioral features (absences, going out, alcohol consumption), and support features (family and school support). The analysis builds in three tiers:

• Basic: total students, average grades, gender distribution, absence and study-time analysis
• Intermediate: impact of study time, internet access, and support systems; behavioral impact of going out and alcohol; parent education and job effects
• Advanced: High / Medium / Low performance segmentation, risk scoring with CASE, ranking with RANK and PERCENT_RANK, and running aggregates

From these, the project identifies high-risk students and "hidden talent" (low study time but high performance), and evaluates how attendance and lifestyle relate to grades.

• Study time and past failures showed the strongest relationship with final grades in the aggregations
• Higher absences were associated with lower grades
• Students without support systems were at higher risk
• Some students performed well despite low study time

These are observations from SQL aggregations and a scoring model, not a statistical or predictive test, which is why a Python predictive model and a Power BI dashboard are the planned next steps.

Data cleaning: checked for NULLs in key columns, removed records where key fields were NULL, verified the total record count and unique customers, and explored the available categories.

Business questions answered:

• All sales made on a specific date
• Clothing transactions in November 2022 above a quantity threshold
• Total revenue and order count per category
• Average customer age in the Beauty category
• High-value transactions (total sale above 1,000)
• Transaction counts by gender within each category
• Best-selling month per year, using average monthly sales and ranking within each year
• Top 5 customers by total spend
• Unique customers per category
• Orders by shift (morning, afternoon, evening), classified in a CTE

Built in SQL Server in a dedicated database. Queries use GROUP BY with aggregates, date filtering, CASE for shift classification, a CTE for the shift counts, and window functions to rank months within each year.