How to clear the Data analyst Interview: Round 1

I call myself a data analyst and I do data analysis of real-life data mostly related to geopolitics, I’m also an expert in machine learning NLP and computer vision. Yet I have failed in the 5 data analyst interviews. Although I am a little disappointed, on the brighter side I have 5 experiences to analyze what went wrong for me and how others can clear data analyst interview learning from my experience.

Most students are oblivious of the fact that companies don’t care how many machine learning projects you have mentioned in your resume (let’s be honest everyone knows that you copied it from somewhere) or how many tools like tableau, PowerBI, Excel you are trained at. Most companies searching for a data analyst wants only one thing and that is your command on SQL.

Most companies have 2 rounds of SQL interviews, round 1 is easier and most questions evaluate your knowledge of joins, and aggregate functions. Round 2 will have window function questions. I will post round 1 questions here and round 2 questions in the next article.

Interview Starts

Question 1:

We heard about this question in our college, we had no clue about rank functions then. Surprisingly it is still asked in every single interview I have appeared.

Write a SQL Query to find the employee name with the second highest salary of Employee (without the use of rank, row_number function)

Answer: Finding the highest salary is pretty straightforward you can just order by Descending and select Top. However to find the second highest you will need to either rank function or nested query.

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM Employee ORDER BY salary DESC )ORDER BY salary

Answer Method 2:Another method we can use is by using the rank function, which is also pretty straightforward.

select salary from(
select salary, dense_rank()
over(order by salary desc)rank from Employee)
where rank =2;

optional question: What is the difference between rank and dense rank?

Question 2:

what is the Order of execution in SQL, how are the SQL commands executed?

Answer: One of the most important questions in SQL is the order of execution and we all should be knowing this if we want to become a data analyst.

Query execution starts with From and mostly ends with select.

1. FROM, including JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. WINDOW functions
6. SELECT
7. DISTINCT
8. UNION
9. ORDER BY
10. LIMIT and OFFSET

Table 1

Table 2

All the questions below will be based on these 2 tables.

Question 3:

How many rows would be there in case of an inner join, left join, and right join?

Answer: Left Join

Because table 1 has all the numbers(1–6) hence all the data will be displayed which is in table 2. 1 row with User ID 4 will be displayed with null values since it is not present in table 2.

Inner join:

Look at this carefully, user id 4 is missing. in an inner join, we take the common data of both tables. Since 4 is not present in table 2 hence it is not present.

Right Join:

If you look clearly the right and inner join have the same number of rows.Because in right join we take all the data from right table and common data from left table hence user id 4 is left behind in both the joins.

Question 4:

Write a query to get user Id, count of products purchased in Oct'19 and Nov'19

Answer: The query is

select  count(*), user id
from order
where order date between '10/01/2019' and '11/01/2019'
group by user id;

Question 5:

Write a query to get User IDs who purchased in Nov’19 and are from Haryana?

Answer: Because the state details are table 1 while order details are n table 2 we will need to join the tables first and then use the where condition.

select distinct(a.users id)
from users a
JOIN employees b
on a.user id = b.user id
where  state="Haryana" and dte_birth between '11/01/2019' and '11/31/2019';

Question 6:

Write a query to get a User ID, his/her latest order information

select  user id, max(order date)  as max
from  orders
group by user id;

Question 7:

what is the difference between where and having?

Answer: WHERE Clause is used to filter the records from the table based on the specified condition. HAVING Clause is used to filter records from the groups based on the specified condition.

Having is used with a group by clause.

Conclusion

If you can answer all these questions you will clear round 1, Congrats! Round 2 will be a bit harder and questions will be mostly related to window functions. I will post those in the second part. in case you find any of my queries incorrect please comment, all of the solutions were written by myself. I have not used google or any other source.