Data science roles at Facebook and Microsoft are highly competitive and difficult to land. Writing SQL on the job will cover 100% of the problems you’re going to handle in practice. In this article, we’ll walk through solving the data science SQL interview question from Microsoft and Facebook. We’ll also give you some tips on how to approach the solution in such data science interviews.

Data Science SQL Interview Question

New And Existing Users

Calculate the share of new and existing users. Output the month, share of new users, and share of existing users as a ratio.

New users are defined as users who started using services in the current month. Existing users are users who started using services in the current month and used services in any previous month.

Assume that the dates are all from the year 2020.

5-Step Framework to Solve this Interview Question

This framework can be used for any data science SQL interview question. Here’s a list of steps you should take when solving a question, whether on an interview or on the job.

1. Explore Data
2. **a) **Only when we’re on the job as most of the time you won’t have executable IDEList assumptions
3. List Assumptions
4. **a) **Listing assumptions helps you narrow down the solution space. It’ll help you identify edge cases and limit your solution to the bounds of your assumption.
5. Outline Approach
6. **a) **Outline the approach by writing it out in steps. Each step is one logical statement or business rule.
7. b) Confirm these steps with the interviewer. Often, if the interviewer spots something wrong, they’ll bring it up which is nice because you can fix the problem before even writing a line of code.
8. c) If you want, you can mention the functions you’d implement to solve the problem.
9. Code in Increments
10. **a) **Build the solution in logical parts and communicate each part so the interviewer knows what you’re doing.
11. b) Your code needs to be logical and well structured. The most important point is not to put more than 1 logical statement or business rule for each “block” of code. A block of code can be defined as a CTE or a subquery because it’s self-contained and separate from the rest of the code.
12. Optimize Code
13. **a) **This is usually just a conversation with the interviewer but on the job, you will usually refactor the code to run more efficiently if the code would be used repeatedly.

#data-science-interview #sql-interview-questions #sql-interview #data-science #facebook