Cracking the SQL Interview for Data Scientists Nervous about your SQL Interview Anxiety ends here. Learn, refresh and master… (Leon Wei) (Z-Library)

📄 File Format: PDF

💾 File Size: 7.2 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

1 DATA SCIENTISTS Cracking the SQL Interview for 90 SQL CODING QUESTIONS & SOLUTIONS Nervous about your SQL Interview? Anxiety ends here. "SQLPad Helped me gain mastery of all the core SQL concepts in a structured manner with a thoughtfully designed business schema" - Nisheeth, Data Scientist LEON WEI

📄 Page 2

INTRODUCTION 3 .............................................................................................. ABOUT THE AUTHOR 4 ............................................................................................... SELECTED CUSTOMER REVIEWS 5 ............................................................................... WHAT A SQL INTERVIEW MAY LOOK LIKE AT A TOP TECH COMPANY 7 ..................... WINDOW FUNCTIONS 12 .......................................................................................... QUESTIONS 28 .................................................................................................. SINGLE TABLE OPERATIONS 28 .................................................................................. MULTI-TABLE OPERATIONS 53 ..................................................................................... WINDOW FUNCTIONS 73 .......................................................................................... SOLUTIONS 100 ................................................................................................. SINGLE TABLE OPERATIONS 100 ................................................................................ MULTI-TABLE OPERATIONS 115 .................................................................................... WINDOW FUNCTIONS 124 ........................................................................................ RECAP 142 ......................................................................................................... CONGRATULATIONS! 142.......................................................................................... 2

📄 Page 3

I N T R O D U C T I O N Hi, thanks for purchasing this SQL ebook! SQL is a must-know programming language for any data analytics professionals. However, many college graduates or young professionals start their job searches without solid SQL coding skills, - which ultimately costs them their dream jobs. This ebook includes 90 SQL interview questions and solutions, which will help data candidates learn or refresh their SQL coding skills and get ready for a SQL interview.  This book is also accompanied by the Cracking the SQL Interview for Data Scientists Course (sold separately), which includes  21 video lectures with tips and techniques to ace a SQL interview. If there are any questions, feedback, please feel free to send me an email at hello@sqlpad.io.   
 Leon Wei,  March 2021. 3

📄 Page 4

A B O U T T H E A U T H O R Leon Wei is the founder of sqlpad.io and instamentor.com, two interview preparation sites for data professionals. Most recently, he was a senior manager of machine learning at Apple, where he lead a team of data scientists and engineers building large-scale machine learning systems for Apple's billion dollars businesses. Before that, he ran the machine learning and data science team at Chegg, a leading education technology company. He also worked as a research scientist at Amazon developing its real-time pricing engine using machine learning. He has interviewed thousands of candidates as a hiring manager or part of a hiring committee. If you need any help with your job search or career, you can hire him as your coach/mentor on instamentor.com Leon Ice Climbing at Matanuska Glacier, Alaska 2019 4

📄 Page 5

S E L E C T E D C U S T O M E R R E V I E W S “Worked through all the problems at sqlpad.io - Great teaching / refresher tool and highly recommended!” — Mike Metzger “I have been relearning SQL and sqlpad.io has been a great resource. There is a good ratio of a new concept to practice questions! Highly recommended.” — Anup Kalburgi “I signed up on SQLPad and was pleasantly surprised when Leon helped me out personally on the website. In two mock interviews with him from instamentor.com, he was meticulously prepared and very professional.” — Rahul Nayak “SQLPad is the best website I have used for practicing SQL.The databases and practice problems resemble real-world data and daily tasks in a data scientist / data analyst role.” — William “SQLPad helped me gain mastery of all the core SQL concepts in a structured manner with a thoughtfully designed business schema. ” — Nisheeth “This course helped me get into a final round of a data scientist interview at Facebook. ” — Justin “I feel so confident with SQL after practicing here. ” — Juhi 5

📄 Page 6

“I am currently in question 30, I really like some of the questions you posted here. Also thank you for your excellent customer service !!!! ” — Mamath “Enjoying it so far, love the mix with practical exercises and the focus to land a job, that's really important because normal courses do not prepare you for interviews. Also, Leon is really kind and helpful.” — Jose “This site is a great resource for SQL interview practice questions. The interface is excellent! And even as someone who currently uses SQL for their day job, I have definitely improved my skills by working through these problems.” — Kyle 6

📄 Page 7

W H AT A S Q L I N T E R V I E W M AY L O O K L I K E AT A T O P T E C H C O M PA N Y Summary Let me walk you through a typical SQL interview at one of the top tech firms, so you can get a sense of how a SQL interview is conducted and what the main criteria are to determine your performance. I will also share a few tips and techniques to give you an edge for your next interview.  Introduction SQL and its related database management system, such as Hive, BigQuery, Spark SQL, are the #1 toolset in managing and processing data in the industry. If you are interviewing for a data-related job, you need to prove your SQL proficiency. Here is a list of typical roles that will require at least one round of SQL interviews: 1. Data analyst 2. Data scientist 3. Data engineer 4. Business intelligence engineer 5. Product analyst 6. Decision scientist 7. Research scientist 8. Software engineer (especially those on the backend side) Why is SQL interview important for a candidate? SQL Interview can sometimes bear other names such as Technical Data Interview or Data Processing Interview. The primary purpose is to make sure a candidate is hands-on with data and can contribute immediately after joining the company. You will be asked to perform a series of SQL coding exercises to extract facts or insights from the given data and answer follow-up questions based on different scenarios. I have been using SQL for 10+ years, and it is still my #1 choice in preparing data. However, I have interviewed so many candidates who started their job searches without a solid coding skill in SQL. In the end, the SQL skills gap cost their dream jobs. How is a SQL interview conducted? Before COVID-19, a typical interview process included 2 or 3 rounds of phone screens and a final round of onsite interviews. Nowadays, most of the interviews are online, often assisted by video conferencing tools such as Zoom, Webex, or Google Hangout. In the old days, during a final interview. a candidate would be asked to write their SQL code on a whiteboard without any help from a modern IDE for syntax highlighting or auto-completion. 7

📄 Page 8

Nowadays, the whiteboard has been replaced by interactive online coding tools such as coderpad or google docs. Tips: Before your interview, make sure you familiarize yourself with those online coding environments. If you don't know what tool they use, ask your recruiter. Right before the interview, you will receive a link that points to an online coding environment, where you will code up solutions in SQL. An interviewer will be able to see everything you type in real-time. A typical SQL interview process Let's assume you are interviewing for a data scientist role at a major music streaming company (e.g., Spotify). You are given 3 (simplified) tables: TABLE 1: song -- id: BIGINT -- name: VARCHAR(255) -- artist_id: BIGINT The first table is a list of songs and their metadata, song names, and associated artists.   TABLE 2: artist  -- id: BIGINT -- name: VARCHAR(255) The second table is simply an artist table with only 2 columns: artist id and the artist name.   TABLE 3:  daily_plays -- date: DATE -- country: VARCHAR(2) ('uk','us','in','jp','cn') -- song_id BIGINT -- plays BIGINT The third table is a daily aggregate table that counts the number of times a song is played in different countries.   And you are asked the following questions: Question 1:  Write a query to return the top 5 songs in the UK yesterday. Tips: a common mistake a beginner usually makes is diving into coding right away without fully understanding the question. 8

📄 Page 9

An interviewer often start with a vague question to see if a candidate is good at communicating. It is crucial to clarify the following key points before diving into coding. 1. What does it mean 'top'? What is top's definition? Do you mean number of plays, number of unique customers, or perhaps other metrics? 2. What columns should I return, a song id, or include a song's name? 3. What if there are ties? Should I return only 1 row or all of them? (optionally) 4. Should we care about the timezone? After clarifying this question, you can go ahead and start coding, and here is a sample solution: SELECT S.song_id, S.name FROM daily_plays P INNER JOIN song S ON P.song_id = S.id WHERE P.country = 'UK' AND P.date = CURRENT_DATE - 1 ORDER BY daily_plays DESC LIMIT 5; Question 2:  Write a query to return the top 5 artists in the US and UK yesterday. This question tries to evaluate whether a candidate knows window functions. Window functions are asked a lot, especially if the hiring company has a global business. To identify top artists/songs/albums across different countries, you need to know how to use ROW_NUMBER and RANK. We will dive into Window functions in our next section.
 Again, before start coding,  try to clarify a few things with the interviewer: 1. The definition of 'top.' 2. Columns to return. 3. Situations when there are ties. 4. This is probably subtle, but because multiple artists can perform a song, you need to talk to the interviewer how to 'count' the plays properly. For simplicity, let's assume a song only has one artist. And here is a sample solution. WITH artist_ranking AS (  SELECT      A.artist_id,     MAX(A.artist_name) AS artist_name,     MAX(P.country) AS country 9

📄 Page 10

    ROW_NUMBER() OVER(PARTITION BY country ORDER BY SUM(plays) DESC) AS ranking  FROM daily_plays P  INNER JOIN song S  ON P.song_id = S.id  INNER JOIN artist A ON   A.artist_id = S.artist_id   WHERE P.country IN ('UK', 'US')   AND P.date = CURRENT_DATE - 1   GROUP BY A.artist_id )  SELECT artist_id, artist_name, country, ranking FROM artist_ranking WHERE ranking <= 5 LIMIT 5; At this step, if you completed the above 2 questions, the interview would have a good sense that you are very good with SQL coding.  He would likely pause SQL coding and transition to follow-up analytics questions. Sample analytics question #1: Taylor Swift is a popular artist, but her songs' plays dropped yesterday. How would you analyze this, what data would you use, and what would be your process? For this kind of question (data change: drop/increase), I have created a framework that you can borrow: 1. Clarify the question. 1. What do you mean dropped, comparing today’s data with yesterday's or same day last week? 2. Did it drop a lot? If it only dropped a little, maybe we don't have to worry about it? 3. And you were told by the interviewer, it's comparing yesterday to the day before, and yes, it indeed dropped a lot significantly. 2. Rule out technical issues first. In my 15 years of career, the #1 cause of poor data quality often comes from a technical issue, e.g., power outage, a bug in our logging code, someone checked in a code without testing, forgot to pay a license fee, and the third-party software stopped working.  3. Whether the drop is a big deal. Even it is a significant drop. We still don’t know whether we should treat it as a big problem, e.g., it could be because of seasonality, the day before yesterday was New Years' Day, so of course the number will drop, and all other artists' songs are also dropped significantly as well. And you were told by the interviewer, yes, it is a big problem, and it is not due to seasonality, and it only happened to Taylor. 4. Dive into data After ruling out those potential issues, now it's time to dive into data. 1 0

📄 Page 11

4.1 Start with internal data (data that you already have full access to). We can slice Taylor's song plays by different countries, genres, devices, iOS vs. Android, Web vs. Mobile, etc.  And it could be because of a particular country, we just lost a popular song's license in that country to a competitor, for example. 4.2 external data (might take extra time/resource to obtain) Finally, you can also mention external data impact, e.g., a competitor such as Amazon Music just had a new product launch. They are giving out free listening to all Taylor's songs. Therefore, we lost a bunch of users. Maybe it is because this artist dropped out of a top chart on Billboard or it could also because the artist lost a lot of followers after posting something controversial on social media.  A few follow-up questions for your reference. This round of interviews is finished at this step, and the next interviewer is waiting to join the conversation, good job! Here are a few more questions that could be asked during interview, in case you want to continue practicing. 1. Write a query to find the top trending artist. 2. How would you design a system to identify trending artists? 3. We want to expand our music streaming services to a few more countries. Can you help find which new countries we should add our services to? What data will you use, and why? 4. How would you design a HOT 100 artist chart? First, clarify the interviewer's requirements and the goals, why do we need to build this, what problems are we trying to solve, who are our customers we are serving, why are you not happy with the existing solution? Always start small, define a handful of critical product features first, and only launch it in one or two countries to test it out. And don't forget to mention you will always keep iterating and fine-tuning the algorithms/product features to expand its scope and bring it to more markets/countries. Tips: Many tech companies really care about how quickly they can launch the first version for system design questions. Bias towards action, and as Reid Hoffman famously put it: if you are not embarrassed about your first product, you launched too late. Conclusion I have shown you what a typical SQL interview (sometimes called technical data interview or data processing interview) process may look like. I also gave you some tips and techniques and a framework to answer that standard interview questions. I hope you learned something today. If you have any questions or want some help with your job search, please feel free to reach out to me at instamentor. 1 1

📄 Page 12

W I N D O W F U N C T I O N S Introduction WINDOW functions are a family of SQL functions that are frequently asked during a data scientist job interview. However, writing a bug-free WINDOW function-based SQL query could be quite challenging for any job candidates, especially those who just get started with SQL. In this article, I will share some typical WINDOW functions specific interview questions, their patterns, and step-by-step solutions. Outline I am going to break down this article into 4 sections: 1. In the first section, I will go through a few WINDOW functions based on regular aggregate functions, such as AVG, MIN/MAX, COUNT, SUM.  2. In section 2, I will focus on rank-related functions, such as ROW_NUMBER, RANK, and RANK_DENSE. Those functions are handy when generating ranking indexes, and you need to be fluent in those functions before entering a data scientist SQL interview. 3. In the third section, I will talk about generating statistics (e.g., percentiles, quartiles, median, etc .) with the NTILE function, a common task for a data scientist. 4. In the last section, let’s focus on LAG and LEAD, two functions that are super important if you are interviewing for a role that requires dealing with time-series data. Section 1. WINDOW functions based on regular aggregate functions: AVG, MIN/MAX, SUM, COUNT Window functions are functions that perform calculations across a set of rows related to the current row. It is comparable to the type of calculation done with an aggregate function, but unlike regular aggregate functions, window functions do not group several rows into a single output row — the rows retain their own identities. Behind the scenes, the window functions process more than just the query results' current row.   1 2

📄 Page 13

All examples in this article are based on movie DVD rental business data. In this first example, our goal is to compare each movie DVDs replacement cost to the average cost of movies sharing the same MPAA ratings. SELECT title, rating, replacement_cost, AVG(replacement_cost) OVER(PARTITION BY rating) AS avg_cost FROM film; For those of you not based in the United States, an MPAA rating is a film rating system that decides a film’s suitability for specific audiences, based on a film’s content. For example, G means it’s appropriate for all ages, while PG-13 contains materials that could be inappropriate for children under 13. There is no GROUP BY clause for the AVG function, but how does the SQL engine know which rows to use to compute the average? The answer is the PARTITION BY clause inside the OVER() utility, and we are calculating the average based on a unique value of rating. In the final output, every row has the average cost from the same rating. You can perform other analyses such as dividing a movie’s cost to avg_cost and find out its expense relative to similar movies.   1 3

📄 Page 14

https://sqlpad.io/playground/ All tables in this article are available on SQLPad’s online SQL playground. If you want to follow along and submit queries against those tables, please feel free to go to sqlpad.io/playground and have some fun.   1 4

📄 Page 15

Let’s take a look at another example. In this example, I want to compare every movie’s length (in minutes) to the maximum length of movies from the same category. 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; It’s very similar to the first example. Still, I combined a MAX function with OVER and PARTITION BY to create a window function, which returned the maximum movie length inside the same movie category. For the first film: story side, its length is 163 minutes, and the maximum length of an action film (same category) is 185. If I compare every action movie’s length to 185, I can get a sense of how long this specific movie is, relative to its category, as films from different genres tend to have different durations.   1 5

📄 Page 16

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; Let’s take a look at a more complicated example, where we calculated a running sum with a window function. Assuming it’s the holiday season, I want to binge-watch all 1000 movies, starting from movie id=1. After finishing each film, I want to know what my overall progress is. I can use SUM and OVER to calculate a running total of time to get my progress. Notice that there is no PARTITION BY clause because I am not grouping those movies into any sub-categories. I want to compute my overall progress but not based on any subgroups or categories. Another thing to notice is that if I don’t add anything inside the OVER() function, I get the total number of minutes from the entire movie catalog. As you can see from the second last column: they all have the same value of 115267, but after I add the ORDER BY clause, I get the running total of the minutes up to that specific row (running_total column). Again, please feel free to go to sqlpad’s playground and play with this film table until you become comfortable with the syntax. 1 6

📄 Page 17

If you are interested in practicing a few more WINDOW functions that we just covered, here are 4 exercises for you to reinforce your learning. Time to complete: ~ 30 -45 mins. • #58: Percentage of revenue per movie • #59: Relative percentage of a movie’s revenue per category • #60: Compare each film rentals with the average rental per category • #61: Customer spend vs. average customer spend in the same store Section 2: ROW_NUMBER, RANK, DENSE_RANK Let’s go through some of the essential WINDOW functions: ROW_NUMBER and RANK. 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; In this example, our goal is to create a ranking index based on the movie's length for the entire movie catalog. As you can see, the ROW_NUMBER function generates a sequence of integers, starting from 1, for each row. But movies with the same lengths were given a DIFFERENT row number, as the database randomly assigned a unique number when there was a tie. 1 7

📄 Page 18

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; Let’s take a look at another example. Instead of comparing a movie’s length to all other films from the entire catalog, we can rank them within each movie category using PARTITION BY. ROW_NUMBER with OVER and PARTITION BY is a regular pattern that is frequently used in advanced SQL. Mastering this pattern will make your life much easier. For example, imagine you are working at an e-commerce company, and it has a global business. Your boss asks you to send her a list of best sellers for each country. You can use ROW_NUMBER and PARTITION BY to generate this list quickly.   1 8

📄 Page 19

SELECT F.film_id, F.title, F.length, RANK() OVER(ORDER BY length DESC) AS ranking FROM film F ORDER BY ranking; Let’s take a look at the RANK function, which is very similar to ROW_NUMBER. The difference between RANK and ROW_NUMBER is that RANK assigns the same unique values if there is a tie and restarts the next value with the total number of rows up to that row. Notice how it jumps from 1 to 11.   1 9

📄 Page 20

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; Similarly, we can also generate rankings within a subgroup with the help of PARTITION BY.   2 0