Solve Real-World Data Problems with SQL

2h 8mIntermediate2023-09-14

Authors

Jess Ramos

Senior Data Analyst, Data Scientist, Team Leader

Course details

Join instructor Jess Ramos in this intermediate, project-based course to practice solving real business problems and advance your skills in SQL. Using a tech company as a case study, Jess helps you boost your skills as a SQL programmer with eleven specific, business-focused coding challenges. Practice analyzing products with CTEs, transforming data with CASE, combining multiple data sources with UNION, dealing with hierarchical data with self joins, solving advanced problems with window functions, and other practical SQL use cases. This course includes Code Challenges powered by CoderPad. Code Challenges are interactive coding exercises with real-time feedback, so you can get hands-on coding practice to advance your coding skills.

Skills covered

SQLDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware DevelopmentOne-Off

Concepts

Introduction

Advancing your skills with SQL
What you should know
CoderPad introduction

Product Analytics in SQL with CTEs

Summarizing customer data for leadership
Combining aggregate functions with CTEs
Introduction to CTEs
Solution - Pull descriptive statistics with a CTE
Solution - Explore variable distributions with CTEs
Solution - Payment funnel analysis with CTEs

Data Transformation in SQL with CASE

Translating status codes into business acumen
Recoding and bucketing values using CASE
Introduction to CASE
Solution - Creating binary columns with CASE
Solution - Create aggregated columns with CASE

Combining Multiple Data Sources in SQL with UNION

Combining multiple email lists
Combining multiple data sources with UNION
Introduction to UNION
Solution - Combine product tables with UNION
Solution - Unpivoting columns into rows using UNION

Dealing with Hierarchical Data in SQL with Self Joins

Dealing with hierarchical data
Relating users and admins using a self join
Introduction to self joins
Solution - Employee-manager data with a self join
Solution - Comparing rows within the same table

Solving Advanced Problems in SQL with Window Functions

Pulling the most recent sales data
Rank(), row number(), and dense rank()
Pulling the most recent sale with row number()
Introduction to window functions
Solution - Get running totals with window functions
Solution - Timestamp differences with LEAD()

Conclusion

Continuing on with SQL