Cracking the Data Scientist SQL Interview Course 🏫

Over 20 video lectures for self guided, step by step learning to become a SQL Master.

To 🔥 celebrate the holiday season, Also check out our NEWLY launched Annual plan (Up to 50% off). Use coupon code THANKSGIVING15 and enjoy additional 15% off all paid plans. Coupon expires at 2020/11/30 .
Chapter 1: Introduction

Welcome to this course! 😃

Hi, thanks for visiting this course!

My name is Leon, I am currently a senior data science executive at a top tech company in Silicon Valley, and I can't emphasize enough how important SQL is for any daily data analytics tasks.

Before my current position, I worked as head of data science at the education technology company Chegg, so I understand the skills gap between the real job requirements vs. college eduction, and that is the major reason why I created data scientist school. 

Before i moved to Silicon Valley, I worked as a research scientist for Amazon, focusing on building large scale machine learning systems.

My goal is to help new college student master necessary technical skills to land their first job. Or help young professionals become more productive and excel at their full time job.

As a hiring manager or part of a hiring committee, I have interviewed thousands of candidates, conducted thousands of SQL interviews for data scientists, data analysts, data engineers, product managers, machine learning engineers.

In this course, I will teach you the common patterns in SQL interviews, and give you an edge to ace you next SQL interview. 

 

Why I created this course?

SQL is the #1 programming language for data management in the industry, but in my 14 years career as a data professional, most of the data scientists that I know including myself, have never mastered SQL during a formal college education.

That’s why I created this course to help you becomes better with SQL, and write beautiful, professional SQL queries to nail your next SQL Interview.

 

Let's get started!



What you will learn

  • Get tips and tricks of a Data Scientist SQL interview
  • Develop a good SQL coding style
  • Practice on real job interview questions
  • Focus and master important SQL operations
  • Get ready for your SQL interview

What makes this course unique?

Job search can be stressful, let me help you

Compared to the usual SQL courses that teach you how to do SELECT and INSERT, this one focuses on actionable information and advice for your job Interview.

You will get tips and tricks to improve your professional awareness, interview skills and significantly shorten the path to pursue and gain your dream job as a data analytics professional.

Real interview questions

Lots of practices

You will be working on real SQL interview questions that I manually collected over the internet on sqlpad.io, an online editor that allows you to submit your SQL solution and get evaluated in real time.

Course format

This course has 4 main sections: single table based operation, multiple table based operation, window function, and advanced topics.

Each section has different lectures, at the end of each lecture, you will be given a list of SQL problems to solve, and I will show you my solutions with a step by step video tutorial.

At the end of each section, you will also get a bunch of SQL questions and have a chance to really dive deep into those problems. I have prepared solution for each one of them in case you are stuck.

Who should take this course

Prerequisites: as long as you can write a SELECT statement and do an INNER JOIN.

Ideal for: fresh college graduates or young professionals who want to nail their next SQL interview as a data scientist, or similar job titles such as data analysts, business intelligence engineer, product managers.




Chapter 2: Single Table Operation

Complete the following 4 exercises on sqlpad.io

I highly recommend that you try your best before looking at my solution. You can do your own research or google the topics in case you are stuck. Good luck!




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Demos are done using the SQL playground tool on sqlpad.io, feel free to follow my coding and try it out there by yourself.

Tables that are used in this lecture:


payment
 col_name  |     col_type
-------------+--------------------------
 payment_id | integer
 customer_id | smallint
 staff_id  | smallint
 rental_id  | integer
 amount   | numeric
 payment_ts | timestamp with time zone

 


rental
  col_name  |     col_type
--------------+--------------------------
 rental_id  | integer
 rental_ts  | timestamp with time zone
 inventory_id | integer
 customer_id | smallint
 return_ts  | timestamp with time zone
 staff_id   | smallint
 last_update | timestamp with time zone


This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Complete the following 6 exercises on sqlpad.io

I highly recommend that you try your best before looking at my solution. You can do your own research or google the topics in case you are stuck. Good luck!




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan



This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan



This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Well done! Congratulations! 🚀🎆

In this lecture, we went through some of the most important SQL operators, 

  1. SELECT, WHERE, ORDER BY, LIMIT;
  2. COUNT, GROUP BY HAVING;
  3. IN, BETWEEN, LIKE, CASE WHEN

And if you have followed along, you have completed 35 exercises! How awesome is that!

And if you are in a good mood 😁 and you found this course helpful, can I ask you for a favor?

I need to collect some testimonials for this course, could you please take a minute and write about your experience with my course on Twitter?

Please make sure to add the #CrackSQLInterview hashtag and mention me @DataLeonWei so I will be notified.

Share on Twitter

 




Chapter 3: Multi-Table Operation

Complete the following 5 exercises on sqlpad.io

Time to complete: approximately 30-45 mins

I highly recommend that you try your best before looking at my solution. You can do your own research or google the topics in case you are stuck. Good luck!




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan



This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Complete the following 5 exercises

Time to complete: approximately 45-60 mins

I highly recommend that you try your best before looking at my solution. You can do your own research or google the topics in case you are stuck. Good luck!




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan



This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Well done! Congratulations! 🚀🎆

In this lecture, we went through some of the most important SQL operators, 

  1. INNER JOIN;
  2. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN;
  3. UNION, UNION ALL

And if you have followed along, you have completed 25 exercises!

And if you are in a good mood 😁 and you found this course helpful, can I ask you for a favor?

I need to collect some testimonials for this course, could you please take a minute and write about your experience with my course on Twitter?

Please make sure to add the #CrackSQLInterview hashtag and mention me @DataLeonWei so I will be notified.

Share on Twitter

 




Chapter 4: Window Functions

-- Example 1: 
SELECT 
title,
rating, 
replacement_cost,
AVG(replacement_cost) OVER(PARTITION BY rating)AS avg_cost
FROM film;


-- Example 2: 
SELECT 
  title, 
  name, 
  length, 
  MAX(length) OVER(PARTITION BY name) AS max_length
FROM (
    SELECT F.title, C.name, F.length 
    FROM film F
    INNER JOIN film_category FC
    ON FC.film_id = F.film_id
    INNER JOIN category C
    ON C.category_id = FC.category_id
) X;

-- Example 3:
SELECT  
  film_id,
  title, 
  length, 
  SUM(length) OVER(ORDER BY film_id) AS running_total,
  SUM(length) OVER() AS overall,
  SUM(length) OVER(ORDER BY film_id) * 100.0 /SUM(length) OVER() AS running_percentage
FROM film
ORDER BY film_id;




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


-- ROW_NUMBER example 1:
SELECT  
  F.film_id,
  F.title, 
  F.length,   
  ROW_NUMBER() OVER(ORDER BY length DESC) AS row_num
FROM film F
ORDER BY row_number;

-- ROW_NUMBER example 2:
SELECT  
  F.film_id,
  F.title, 
  F.length, 
  C.name AS category,
  ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length DESC) row_num
FROM film F
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
ORDER BY C.name, row_number;

-- RANK example 1:
SELECT  
  F.film_id,
  F.title, 
  F.length,   
  RANK() OVER(ORDER BY length DESC) AS ranking
FROM film F
ORDER BY ranking;

-- RANK example 2:
SELECT  
  F.film_id,
  F.title, 
  F.length, 
  C.name AS category,
  RANK() OVER(PARTITION BY C.name ORDER BY F.length DESC) ranking
FROM film F
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
ORDER BY C.name, ranking;


-- DENSE_RANK example 1:
SELECT  
  F.film_id,
  F.title, 
  F.length,   
  DENSE_RANK() OVER(ORDER BY length DESC) AS ranking
FROM film F
ORDER BY ranking;

-- DENSE_RANK example 2:
SELECT  
  F.film_id,
  F.title, 
  F.length, 
  C.name AS category,
  DENSE_RANK() OVER(PARTITION BY C.name ORDER BY F.length DESC) ranking
FROM film F
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
ORDER BY C.name, ranking;


 


This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Crunch time! 🏋️‍♂️

Time to complete: ~30 -45 mins.

 




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


-- Example 1: percentile for the entire movie catalog
SELECT  
  film_id,
  title, 
  length,   
  NTILE(100) OVER(ORDER BY length DESC) AS percentile
FROM film
ORDER BY percentile;

-- Example 2: percentile, deciel and quartile by MPAA rating by replacement cost of a movie dvd.
SELECT  
  title, 
  replacement_cost AS cost, 
  rating, 
  NTILE(100) OVER(PARTITION BY rating ORDER BY replacement_cost) AS percentile,
  NTILE(10) OVER(PARTITION BY rating ORDER BY replacement_cost) AS decile,
  NTILE(4) OVER(PARTITION BY rating ORDER BY replacement_cost) AS quartile
FROM film
ORDER BY rating, replacement_cost;


This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


-- Example 1: dod with LAG
WITH daily_revenue AS (
  SELECT 
    DATE(payment_ts) date,
    SUM(amount) revenue
  FROM payment
  WHERE DATE(payment_ts) >= '2020-05-24'
  AND   DATE(payment_ts) <= '2020-05-31'
  GROUP BY DATE(payment_ts)

SELECT
  date, 
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) prev_day_sales,
  revenue *1.0/LAG(revenue,1) OVER (ORDER BY date) AS dod
FROM
  daily_revenue
ORDER BY date; 


-- Example 2: day before yesterday's revenue
WITH daily_revenue AS (
  SELECT 
    DATE(payment_ts) date,
    SUM(amount) revenue
  FROM payment
  WHERE DATE(payment_ts) >= '2020-05-24'
  AND   DATE(payment_ts) <= '2020-05-31'
  GROUP BY DATE(payment_ts)

SELECT
  date, 
  revenue,
  LAG(revenue, 2) OVER (ORDER BY date) prev_day_sales,
  revenue *1.0/LAG(revenue,2) OVER (ORDER BY date) AS dod
FROM
  daily_revenue
ORDER BY date; 

-- Example 3: dod with LEAD
WITH daily_revenue AS (
  SELECT 
    DATE(payment_ts) date,
    SUM(amount) revenue
  FROM payment
  WHERE DATE(payment_ts) >= '2020-05-24'
  AND   DATE(payment_ts) <= '2020-05-31'
  GROUP BY DATE(payment_ts)

SELECT
  date, 
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) next_day_sales,
  LEAD(revenue,1) OVER (ORDER BY date) *1.0/revenue AS dod
FROM
  daily_revenue
ORDER BY date; 


This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Crunch time! 🏋️‍♂️

Time to complete: ~45 mins - 1 hour.

 

 




This lecture contains a video for our Premium members, to watch this video , please subscribe to our Premium plan


Well done! Congratulations! 🚀🎆

In this lecture, we went through some of the most important WINDOW functions

  1. AVG, MIN/MAX, COUNT, SUM;
  2. ROW_NUMBER, RANK, DENSE_RANK;
  3. NTILE;
  4. LAG, LEAD

And if you have followed along, you have completed 22 exercises!

And if you are in a good mood 😁 and you found this course helpful, can I ask you for a favor?

I need to collect some testimonials for this course, could you please take a minute and write about your experience with my course on Twitter?

Please make sure to add the #CrackSQLInterview hashtag and mention me @DataLeonWei so I will be notified.

Share on Twitter




Chapter 5: Wrapup

🚀🎆 You completed the course! Well done! Congratulations!

You've finished 21 videos, 10 lectures from Single table operations such as

  1. SELECT, WHERE, ORDER BY, LIMIT;
  2. COUNT, GROUP BY HAVING;
  3. IN, BETWEEN, LIKE, CASE WHEN

To multi-table operations including

  1. INNER JOIN;
  2. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN;
  3. UNION, UNION ALL

And advanced Window functions:

  1. AVG, MIN/MAX, COUNT, SUM;
  2. ROW_NUMBER, RANK, DENSE_RANK;
  3. NTILE;
  4. LAG, LEAD

But what's more important, you've finished more than 80 exercises!

I hope you enjoyed this course, and good luck on your work, your job hunting and your new adventure with SQL programming! If you have any questions, please feel free to drop me a line or leave a comment below!

 

And if you are in a good mood 😁 and you found this course helpful, can I ask you for a favor?

I need to collect some testimonials for this course, could you please take a minute and write about your experience with my course on Twitter?

Please make sure to add the #CrackSQLInterview hashtag and mention me @DataLeonWei so I will be notified.

Share on Twitter