Day 2: Advanced SQL Preparation Guide

The objective is to prepare for advanced SQL concepts like CTEs and window functions by setting up a PostgreSQL environment and practicing basic queries. The recommended primary dataset is E-commerce Sales Data from Kaggle, due to its multiple tables, time-series nature, and manageable size. A secondary option is an HR Analytics Dataset, useful for practicing recursive CTEs. Installation instructions are provided for macOS, Ubuntu/Debian, and Windows, along with initial configuration steps. Data loading can be performed using the fast COPY command for CSVs, the pgAdmin GUI, or Python scripts. Basic SQL practice includes SELECT statements, filtering, aggregations like SUM and AVG, and JOIN operations to prepare for more complex queries. Subqueries in WHERE, FROM, and correlated subqueries are covered as a foundation for CTEs. A teaser of window functions demonstrates running totals and ranking products within categories. CTEs are previewed with a monthly sales trend example. The document includes practice exercises for data exploration, business questions, complex queries, and data quality checks. Performance tips focus on creating indexes on frequently queried columns and using EXPLAIN ANALYZE to understand query execution. Finally, a checklist for next steps and useful resources are provided.