There are normally a number of ideas interviewers are testing for on knowledge science interviews however since they could solely have time to ask 1-2 questions, they’re going to attempt to pack the ideas into one query. So it is necessary to know what these ideas are so you possibly can look out for them in an interview.
So what are they actually testing for? Actually what an interviewer is in search of are interviewees with an in-depth understanding of metric design and implementation of a real-world situations that might be current within the knowledge. The important thing phrase right here is “real-world situation”, which signifies that there are in all probability going to be a number of edge circumstances and situations you will have to assume by to resolve the issue. There are 3 widespread ideas that they check for that check your understanding of learn how to implement code that solves real-world situations.
Since they solely have time to ask 1-2 questions in an interview earlier than their time is up, you will usually see all 3 ideas wrapped in a single query. I see this query, or a model of this query, ( platform.stratascratch.com/coding-question?id=10300&python= ) on nearly each interview I have been on or given. Observe together with me and see should you would have the ability to reply this query.
The three ideas it’s worthwhile to know are CASE statements, JOINs, and subqueries/CTEs. Let’s undergo an actual interview query that cowl these 3 ideas and speak about them in-depth. The hyperlink to the query is right here ((platform.stratascratch.com/coding-question?id=10300&python=) if you wish to observe alongside.
Aggregates from CASE STATEMENTs
You will possible get some form of categorization query the place it’s worthwhile to categorize knowledge based mostly on values you see within the desk. That is tremendous widespread in apply and you will possible at all times be categorizing and cleansing up knowledge. So a CASE assertion is the best approach to check for.
Add the addition of aggregates like sum() and depend() and so they’ll be testing to see should you truly know what’s being returned in a case when, not simply the implementation of it. Based mostly on the case statements, you possibly can at all times add an mixture capabilities like a depend or a sum.
Right here is an instance of a CASE assertion with a easy aggregation within the SELECT clause for the query.
You see within the CASe assertion under, we’re categorizing customers based mostly on if they’re paying clients or mot. We then apply a sum() as it is a fast solution to depend the variety of paying clients vs non-paying clients in a single easy question. If we didn’t have the CASE assertion, it will take us two queries to search out each numbers.
SELECT date, sum(CASE
WHEN paying_customer = ‘sure’ THEN downloads
END) AS paying,
sum(CASE
WHEN paying_customer = ‘no’ THEN downloads
END) AS non_paying
FROM ms_user_dimension a
JOINs
The 2nd idea is JOINing tables. Are you able to be part of tables? That is the bottom bar it’s worthwhile to soar over to be an analyst, a lot much less an information scientist. This bar is principally on the bottom so you possibly can actually simply step over it.
So on interviews — do they normally do a LEFT JOIN, CROSS JOIN, INNER JOIN? Most of your work shall be utilizing a LEFT JOIN so that they’re testing you based mostly on practicality. You will nearly by no means use a cross be part of. You will use an interior be part of fairly a bit however left be part of is barely extra difficult so that they’ll use that simply as an extra filter.
Self joins are widespread as a result of it is not at all times apparent you would be utilizing that. However they’re widespread in apply.
Within the under instance, we’re becoming a member of tables to the CASE assertion. We’re becoming a member of two tables to our fundamental desk utilizing a LEFT JOIN.
SELECT date, sum(CASE
WHEN paying_customer = ‘sure’ THEN downloads
END) AS paying,
sum(CASE
WHEN paying_customer = ‘no’ THEN downloads
END) AS non_paying
FROM ms_user_dimension a
LEFT JOIN ms_acc_dimension b ON a.acc_id = b.acc_id
LEFT JOIN ms_download_facts c ON a.user_id=c.user_id
GROUP BY date
ORDER BY date
Subquery/CTE
The final widespread idea is a subquery/CTE, principally some idea the place you are doing a little work after which have to do extra work on it. That is testing to see should you can break up your downside into logical steps. Some options take a couple of step to resolve so that they’re testing to see should you can write code that follows a logical stream. Not essentially difficult or advanced, however multi-step and pragmatic. That is particularly helpful in apply since you’ll 100% be writing code that is over a whole bunch of strains lengthy and also you want to have the ability to create options that observe stream.
Within the under instance, I am taking the question we wrote above and placing it in a subquery in order that we are able to question its knowledge. This manner we are able to apply an extra filter within the HAVING clause and preserve your entire resolution to at least one question.
SELECT date, non_paying,
paying
FROM
(SELECT date, sum(CASE
WHEN paying_customer = ‘sure’ THEN downloads
END) AS paying,
sum(CASE
WHEN paying_customer = ‘no’ THEN downloads
END) AS non_paying
FROM ms_user_dimension a
LEFT JOIN ms_acc_dimension b ON a.acc_id = b.acc_id
LEFT JOIN ms_download_facts c ON a.user_id=c.user_id
GROUP BY date
ORDER BY date) t
GROUP BY t.date,
t.paying,
t.non_paying
HAVING (non_paying – paying) >0
ORDER BY t.date ASC