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

0. Introduction

01 - Advancing your skills with SQL
02 - What you should know
03 - CoderPad introduction

1. Product Analytics in SQL with CTEs

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

2. Data Transformation in SQL with CASE

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

3. Combining Multiple Data Sources in SQL with UNION

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

4. Dealing with Hierarchical Data in SQL with Self Joins

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

5. Solving Advanced Problems in SQL with Window Functions

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

Conclusion

31 - Continuing on with SQL