by Jeffrey James | Released February 2018 | ISBN: 9781788470803

Extend your knowledge of SQL, databases, and BI by mastering complex topics such as materialized views, common table expressions, and advanced data groupingAbout This VideoDelve into Customer Value and Cohort Analysis.Understand the time between event analysis and Top N calculations, in the context of applied business situations.Analyze data using Excel and or Tableau.Solve the challenge and conduct a freeform database analysis.In DetailThis example-driven course provides thoughtful and interactive commentary throughout. We understand the common mistakes and misconceptions you might make and help you navigate tricky SQL concepts.Window Functions are used in detail throughout the course to solve problems dealing with finding the first order or the Nth instance of an event, computing the timing between events, and new and repeat purchase behaviors among customers. You'll run through the workflow from SQL to a localhost connection in Tableau and also analysis, all of which you'll need in your professional life. Concepts such as CASE statements, common table expressions, and subqueries will be explained via case studies. You'll generate web analytics acquisition source data using Python and then create tables to store your information.By the end of the course, you will have gone through all the examples and coded them out, and you'll be ready to confidently tackle non-trivial problems. Supercharge your data productivity today with this course and get 100x your time investment back in the next year or two! Show and hide more Publisher Resources Download Example Code

1. Chapter 1 : Installing Postgres and Our Initial Dataset
   • The Course Overview 00:03:34
   • Installation on Windows and Mac Via Postgres App 00:03:01
   • Installing pgAdmin 00:02:44
   • Downloading and Restoring the DVD Rental Database 00:02:53
2. Chapter 2 : SQL in the Real World and Customer Value Analysis
   • Finding First Orders 00:06:27
   • The Window Function ROW_NUMBER() and Using a CTE to Find First Orders 00:07:02
   • Analyzing New Versus Repeat Buyer Behavior 00:10:36
   • Customer Value Analysis/LTV Case Study Part One 00:06:30
   • Customer Value Analysis/LTV Case Study Part Two 00:10:15
3. Chapter 3 : Time between Events and Mastering Window Functions
   • The LAG Function 00:06:51
   • Time between Customer Orders 00:08:05
   • Analyzing Our Time Since Behavioral Data 00:10:05
   • NTILE Window Function 00:08:55
4. Chapter 4 : Freeform Analysis of the DVD Rental Database
   • First Orders 00:08:18
   • Top Five Highest Grossing Actors 00:07:05
   • Films by Most Gross Revenue Per Actor 00:13:17
   • Does First Rating Rented from Predict Lifetime Value? 00:11:03
5. Chapter 5 : Additional Advanced Analysis of the Database
   • Cross Shopping 00:07:51
   • Computing LTV Summary Metrics Using Correlate Subqueries 00:10:30
   • CPA and Profitability Analysis Project - Part One 00:11:10
   • CPA and Profitability Analysis Project - Part Two 00:13:34
Show and hide more