Intermediate SQL for Data Scientists

Introduction

• The need for SQL in data science
• What you should know

1. Foundations of SQL for Data Science

• Overview of data science operations
• Data manipulation commands
• Data definition commands
• SQL standards
• Installing PostgreSQL

2. Basic Statistics with SQL

• Loading data
• Basic aggregate functions
• Statistical aggregate functions
• Grouping and filtering data
• Joining and filtering data
• Challenge: Write a Query with a Filter
• Solution: Write a Query with a Filter

3. Data Munging with SQL

• Reformat character data
• Extract strings from character data
• Filter with regular expressions
• Reformat numeric data
• Use SOUNDEX with misspelled text
• Challenge: Prepare a data set for analysis
• Solution: Prepare a data set for analysis

4. Filtering and Aggregation

• Use the HAVING clause to find subgroups
• Subqueries for column values
• Subqueries in FROM clauses
• Subqueries in WHERE clauses
• Use ROLLUP to create subtotals
• Use CUBE to total across dimensions
• Use Top-N queries to find top results
• Challenge: Filter and aggregate a data set
• Solution: Filter and aggregate a data set

5. Window Functions and Ordered Data

• Introduction to window functions
• NTH_VALUE and NTILE
• RANK, LEAD, and LAG
• WIDTH_BUCKET and CUME_DIST
• Challenge: Segment a data set using Window functions
• Solution: Segment a data set using Window functions

6. Common Table Expressions

• Introduction to common table expressions (CTEs)
• Multiple table common table expressions
• Hierarchical tables
• Recursive common table expressions
• Challenge: Rewrite a complex query to use CTEs
• Solution: Rewrite a complex query to use CTEs

Conclusion

• Next steps