SQL for Data Science

SQL is a language used by data scientists to extract, transform, and load data. It's often used for Feature Engineering. In this presentation, we show how you can use SQL + R + dplyr to perform Feature Engineering with a 7 Table database of Home Loan Applications. We build a model to predict the likelihood of loan default.

Transcript

1. Analyzing Home Loans using SQL, R, & dplyr Matt Dancho & David Curry Business Science Learning Lab Difficulty: Beginner
2. Learning Lab Structure • Presentation (20 min) • Demo’s (30 min) • Pro-Tips (15 mins)
3. Diego Usai - Past Year, began studying Data Science - Took Business Science Courses - Created own website with Project Portfolio “Your courses were a godsend.” #Business Science Success diegousai.io
4. • Business Case Study ◦ Loan Applications ◦ Loan Defaults cost BILLIONS • Relational Database ◦ Key Concepts • Feature Engineering ◦ Core Concepts • SQL For Data Science ◦ Databases are fast ◦ SQL is Painful ◦ Better Way? • 30-Min Demo ◦ Home Loans ◦ SQL in R ◦ Feature Engineering • Pro-Tips & Learning Guide ◦ Recap + Pro-Tips ◦ Learning Plan
5. Every 2-Weeks 1-Hour Course Recordings + Code + Slack $19/month university.business-science.io Lab 20 Explainable Machine Learning Lab 19 Using Customer Credit Card History for Networks Analysis Lab 18 Time Series Anomaly Detection with anomalize Lab 17 Anomaly Detection with H2O Machine Learning Lab 16 R’s Optimization Toolchain, Part 2 - Nonlinear Programming Lab 15 R’s Optimization Toolchain, Part 1 - Linear Programming Continuous Learning Jet Fuel for your Brain
7. Home Loans 1. Banks lend Billions 2. Approval process - Want strict, but not too strict 3. If we can better predict defaults, we can save billions
8. SQL Database 1. Data Scientists interact with data stored in SQL for their Analysis 99.9% of time 2. This complex Network is SUPER Useful if we know how to use it… Feature Engineering!
10. SQL Structured (Most BI Databases) NoSQL Unstructured Data (Apps) 1 2 MS SQL MySQL PostGreSQL MongoDB AWS DynamoDB
11. SQL Structured (Most BI Databases) NoSQL Unstructured Data (Apps) 1 2 MS SQL MySQL PostGreSQL MongoDB AWS DynamoDB
12. SQL Database 1. Data stored in SQL Tables 2. Relationships between tables linked with common field called an ID (Primary Key) 3. Data Scientists can use tables to generate features & model business problem Key Point - Can use relationships between tables to generate critical features to higher level data model
13. SQL Database 1. Data stored in SQL Tables 2. Relationships between tables linked with common field called an ID (Primary Key) 3. Data Scientists can use tables to generate features & model business problem Key Point - Can use relationships between tables to generate critical features to higher level data model Main Analysis Table Supporting Tables
15. Data from Credit Bureau Multiple records (previous applications) for each SK_ID_CURR Data for Current Application One record for each SK_ID_CURR
16. Days Credit for Previous Loan Applications Multiple Prior applications Question: Does “Days Credit” for previous loan applications influence Loan Default?
17. Compute Average Mean Days Credit & Join with Application_Train
18. Correlation Compute Average Mean Days Credit & Join with Application_Train
20. SQL & NoSQL are optimized to handle data & perform aggregations, filtering, etc. Data takes a long time to transfer between machines.
21. Home Loans Database (SQL Database) Matt’s Computer (MacBook Pro)
22. Home Loans Database (SQL Database) Matt’s Computer (MacBook Pro) Minimize Data Transfer via Aggregation Maximize Simple but Expensive Operations Perform Complex Data Science
23. It costs you time to write SQL, it’s prone to errors & difficult to learn
25. Wise owl says, “Hoot. Hoot. Rewrite your previous statement with dplyr.”
27. If you know dplyr, you know SQL (You just may not know this yet)
28. Correlation Compute Average Mean Days Credit & Join with Application_Train
29. Database Table Connection dplyr operations
30. dplyr translates to SQL Database Table Connection dplyr operations
31. Not Impressed? We still didn’t do the join
32. SQL LEFT JOIN Using the application_train Table & the newly created mean_days_credit
33. Dplyr SQL Translation Using the application_train Table & the newly created mean_days_credit
34. Home Loans Database (SQL Database) Matt’s Computer (MacBook Pro) Minimize Data Transfer via Aggregation Maximize Simple but Expensive Operations Perform Complex Data Science
37. #1. Don’t Memorize SQL This will drive you MAD #2. Learn dplyr, then translate to SQL Using show_query() # 3. Fill in the blanks by Googling SQL Commands Use sql() to insert into your dplyr code
39. Start Finish 1 2 3 dplyr Feature Engineering SQL Query Aggregations & Joins Machine Learning, Visualization & Apps Predict & Explain Loan Default Risk
40. 101 & 201
41. 101 & 201
42. Advanced Machine Learning 201
43. Advanced Machine Learning 102 & 202A
44. Business Science University Data Science for Business Transformation in 6-Months
45. The program that will deliver YOUR Transformation
46. Advanced Visualization Advanced Data Wrangling Advanced Functional Programming & Modeling Advanced Data Science Visualization Data Cleaning & Manipulation Functional Programming & Modeling Business Reporting Business Analysis with R (DS4B 101-R) Data Science For Business with R (DS4B 201-R) Web Apps & Shiny Developer (DS4B 102-R + DS4B 202A-R) Web Apps Data Science Foundations 7 Weeks Machine Learning & Business Consulting 10 Weeks Web Application Development 12 Weeks -TRACK Project-Based Courses with Business Application Business Science University R-Track 4-Course R-Track System
47. Key Benefits - Fundamentals - Weeks 1-5 (25 hours of Video Lessons) - Data Manipulation (dplyr) - Time series (lubridate) - Text (stringr) - Categorical (forcats) - Visualization (ggplot2) - Programming & Iteration (purrr) - 3 Challenges - Machine Learning - Week 6 (8 hours of Video Lessons) - Clustering (3 hours) - Regression (5 hours) - 2 Challenges - Learn Business Reporting - Week 7 - RMarkdown & plotly - 2 Project Reports: 1. Product Pricing Algo 2. Customer Segmentation Visualization Data Cleaning & Manipulation Functional Programming & Modeling Business Reporting Business Analysis with R (DS4B 101-R) Data Science Foundations 7 Weeks
48. Key Benefits Understanding the Problem & Preparing Data - Weeks 1-4 - Project Setup & Framework - Business Understanding / Sizing Problem - Tidy Evaluation - rlang - EDA - Exploring Data -GGally, skimr - Data Preparation - recipes - Correlation Analysis - 3 Challenges Machine Learning - Weeks 5, 6, 7 - H2O AutoML - Modeling Churn - ML Performance - LIME Feature Explanation Return-On-Investment - Weeks 7, 8, 9 - Expected Value Framework - Threshold Optimization - Sensitivity Analysis - Recommendation Algorithm Data Science For Business (DS4B 201-R) Machine Learning & Business Consulting 10 Weeks Advanced Visualization Advanced Data Wrangling Advanced Functional Programming & Modeling Advanced Data Science End-to-End Churn Project
49. Key Benefits Learn Shiny & Flexdashboard - Build Applications - Learn Reactive Programming - Integrate Machine Learning App #1: Predictive Pricing App - Model Product Portfolio - XGBoost Pricing Prediction - Generate new products instantly App #2: Sales Dashboard with Demand Forecasting - Model Demand History - Segment Forecasts by Product & Customer - XGBoost Time Series Forecast - Generate new forecasts instantly Shiny Apps for Business (DS4B 102-R) Web Application Development 4 Weeks Web Apps Machine Learning
50. Key Benefits Frontend + Backend + Production Deployment Frontend for Shiny - Bootstrap Backend for Shiny - MongoDB - Dynamic UI - User Authentication - Store & Write User Data Production Deployment - AWS - EC2 Server - VPC Connection - URL Routing Shiny Apps for Business (DS4B 202A-R) Web Application Development 6 Weeks
51. -TRACK BUNDLE 20% OFF PROMO Code: SHINYDEVLAUNCH $119/mo Expires Nov 1
52. Begin Learning Today university.business-science.io