One skill common across all data professions is SQL. You can never go wrong in learning it. Whether you handle structured data or unstructured data, you would need to SQL at some point in the process.
In my recent interviews at Faang, SQL was one of the first skills to be tested. You can read about the entire process in this article.
How I cracked Business Intelligence Engineer Interviews at FAANG
Here are the top-five SQL concepts to prepare if you are appearing for a data analyst Interview at FAANG (or any company).
1. Common Table Expressions –Common Table Expressions also called as CTEs, are one of the most under-rated concepts in SQL. Joins are important but CTEs can make your and your successors’ lives extremely easy. A CTE is basically a temporarily named dataset that can be used in further CTE/select/insert/update statements.
It was introduced to simplify Sql queries. You can think of them as a variable in your query. You declare it using With <CTE Name> as(Select ...)
This CTE can then be called anywhere in subsequent SQL code just like you would use a pre-declared variable. To use the CTE, you join it with other tables/views/CTEs using join , on keywords.
Please note that CTEs are not meant just for interviews. They are extensively used in my day-to-day work as well.
2. Row_number() –The row_number function is very similar to rank() and dense_rank(). The basic difference lies in handling the cases when there are ties in the partition. The rank function assigns the same sequence number to all ties and skips the next sequence number. Dense_rank() replicates the sequence number for all tied values without skipping the next number in the sequence. Whereas Row_number() just assigns an incrementing serial number arbitrarily to all tied rows. For questions like – Find top-10 employees with the highest salary, row_number works well.
3. Self-Join –The one question that has been common across all SQL interviews I have ever given is based on self-join. Self-joins are most widely used to find the child-parent linking. I have personally used this concept at work several times to show Account managers and their supervisors, all employees reporting directly under a Group Manager, and so on.
4. Case when Statements –The Case When statements are mainly used for categorizing/binning a column based on different criteria. Like if you want to show student divisions based on their examination scores, you would write a case statement like this –
case
when marks >= 60 then 'First'
when marks >= 45 then 'Second'
when marks >= 33 then 'Third'
else 'Fail'
end as DivisionThis makes case statements extremely useful. It is generally used at work too in creating different types of flags.
5. Window Functions /Aggregations –Aggregations like sum, min, max, and average are an integral part of an analyst’s toolkit. Window functions make it possible to show the calculated result for every row. For instance, showing cumulative sum, showing the previous or the next value. These are just a few things you can do by using window functions. I highly suggest you understand the concept well. This will save you some time and very lengthy code while on the job.
For more SQL concepts, please refer to this article on date functions.
In my opinion, you can clear all SQL interviews with these five concepts along with the built-in date functions. And you would be a strong candidate as far as SQL skills are concerned.
I wish you all the best in your journey.
Thanks for reading!
Until next time…
P.S. – A lot of you are reaching out on Linkedin for the actual questions asked. I am under NDA and cannot share the actual question paper. I am also not able to respond to all of you individually. But I promise that the concepts elaborated above cover all questions that were asked.
