Advanced Applied SQL for Business Intelligence and Analytics

Name: Advanced Applied SQL for Business Intelligence and Analytics
Rating: 4.0 (55 reviews)

Develop your knowledge of SQL to grow your business and advance your career

Created byPackt Publishing

Last updated 3/2018

English

What you'll learn

Put SQL to work for Business Intelligence
Develop your SQL skills to advance your career
Master advanced topics such as materialized views, common table expressions, advanced data grouping, and more!
Bring data into Excel, Tableau or other business software for further analysis and visualization
Uncover key insights about your customers, suppliers, and business performance
Use your database to make crucial business decisions and grow the bottom line
Avoid common mistakes that cost you credibility and time.

Course content

5 sections • 21 lectures • 2h 49m total length

The Course Overview3:34
This video gives an overview of the entire course.
Installation on Windows and Mac Via Postgres App3:00
The aim of the video is to install Postgres on Mac and Windows operating systems.
Installing pgAdmin2:43
The aim of this video is to install pgAdmin, our SQL query interface and learn its general usage.
Downloading and Restoring the DVD Rental Database2:52
The aim of this video is to import our first dataset, the DVD rental database.

Finding First Orders6:26
The goal of this video is to find first orders done by revisiting the Self JOIN.
The Window Function ROW_NUMBER() and Using a CTE to Find First Orders7:02
The aim of the video is to delve more into first orders using the window function and the CTEs.
Analyzing New Versus Repeat Buyer Behavior10:36
The aim of this video is to analyze new and repeated purchases per customer.
Customer Value Analysis/LTV Case Study Part One6:30
The goal of this video is to work on the given case study based on customer value analysis/LTV.
Customer Value Analysis/LTV Case Study Part Two10:15
The goal of this video is to work on the given case study based on customer value analysis/LTV.

The LAG Function6:51
The goal of this video is to work with LAG function and the basics of “time since” calculations.
Time between Customer Orders8:04
The aim of the video is to obtain the time it takes for customers to reorder.
Analyzing Our Time Since Behavioral Data10:04
The aim of this video is to analyze our data by augmenting the SQL and using Tableau.
NTILE Window Function8:54
In this video, we will see how to tackle when someone says “I want the top 10% of movies by rental amount”.

First Orders8:18
The goal of this video is to find out whether the new customers prefer any specific rating.
Does First Rating Rented from Predict Lifetime Value?7:05
The aim of the video is to join in total customer spend after
piggybacking on our initial work getting first ratings rented from.
Top Five Highest Grossing Actors13:17
The aim of this video is to find the top five highest grossing
actors and see what percentage of customers rented from one of their
films.
Films by Most Gross Revenue Per Actor11:02
The aim of this video is to get films by highest gross revenue per actor.

Cross Shopping7:51
The goal of this video is to find out if the customers cross shop
between stores and also the connection between the store, time of day
and another insights.
Computing LTV Summary Metrics Using Correlate Subqueries10:30
The aim of the video is to create summary statistics for the customers.
CPA and Profitability Analysis Project – Part One11:09
In this video, we will integrate spend and source table data to compute customer and campaign profitability.
CPA and Profitability Analysis Project – Part Two13:34
In this video, we will integrate spend and source table data to compute customer and campaign profitability.

Requirements

Basic understanding of SQL and interact with data and databases; it will help them understand query complexity with ease.

Description

This 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!

About the Author

Jeffrey James has been working in the analytics and data space since 2006. With roots in digital marketing and web analytics, he's applied analytical techniques to problems including customer value analysis, financial forecasting, machine learning, and process automation. He's made his share of mistakes on the way to mastery and understands the mindset of a beginner/learner.

Who this course is for:

The course is for analysts and developers.

Advanced Applied SQL for Business Intelligence and Analytics

What you'll learn

Explore related topics

Course content

Installing Postgres and Our Initial Dataset4 lectures • 12min

SQL in the Real World and Customer Value Analysis5 lectures • 41min

Time between Events and Mastering Window Functions4 lectures • 34min

Freeform Analysis of the DVD Rental Database4 lectures • 40min

Additional Advanced Analysis of the Database4 lectures • 43min

Requirements

Description

Who this course is for: