Business Analyst Interview Questions 2023

In the 21st century, Business Analyst is the hottest job on the market. And being prepared for the technical questions is quite imperative to get the job. If one has to prepare for the job role, SQL is an important tool that is widely and majorly used in the field.

So whet your SQL query writing skills to maximize your chances of becoming a Business Analyst. As being an analyst will require you to draw insights from raw data and communicate the results effectively to stakeholders for making data-driven decisions.

The method of solving a SQL query has 4 steps:

1. Understanding the question.
2. Analyze the data mentioned in the question.
3. Plan your approach.
4. Write code.

#1. Amazon Business Analyst Interview Question: Finding User Purchases

link to the question: https://platform.stratascratch.com/coding/10322-finding-user-purchases?tabname=question

One of the Business Analyst’s day-to-day problems includes understanding customer behavior. In the question, one might be focusing on customer retention methods or determining how to exhort the customers on buying more.

Having a look at table details. In this, we have user_id as unique integer values to identify each record in the table.

The data in the table looks like this:

In this problem, we need to identify the active users. So, we need to check if the difference between their first purchase date and other purchasing dates for a particular customer is less than or equal to 7.

To perform this subtraction between two purchasing dates, we need to create a self-join for the amazon_transactions table. And use distinct user_id in the result to avoid any redundancy.

SELECT 
DISTINCT(user_id)
FROM
amazon_transactions a
JOIN
amazon_transactions t
ON
a.user_id = t.user_id
AND
a.id <> t.id

In the table self-join conditions, we are establishing the join based on the user_id in both tables. Additionally, the other criteria used to establish the self-join is that the id in both tables shouldn’t match. This is done to avoid including the same record for each customer, otherwise, the joined table would contain the same records from both tables. As we need data based on different transaction dates.

SELECT 
DISTINCT(user_id)
FROM
amazon_transactions a
JOIN
amazon_transactions t
ON
a.user_id = t.user_id
AND
a.id <> t.id
WHERE (a.created_at)-(t.created_at) BETWEEN 0 AND 7
ORDER BY 1

In the where clause we perform the subtraction to only include the active user’s criteria. The difference in purchase date from the two tables has to be more than 0 and less than or equal to 7 days.

It is always a good practice to display the result chronologically. So, we used order by clause to sort the result in increasing order.

#2. Forbes Business Analyst Interview Question: Most Profitable Companies

link to the question: https://platform.stratascratch.com/coding/10354-most-profitable-companies?tabname=question

The question includes making a report of the top 3 profitable companies around the world. This problem includes the aggregation of profit based on the companies and the ranking of them based on the highest profits.

Let’s have a look at the details of the data. Here the company names are unique and can be used to identify and aggregate the sum of profits and collapse the individual records under the same name.

This is what the data table looks like.

To solve this problem we will need the help of a sub-queries. Because the innermost query will calculate the sum of the profit per company using Group By. This will output the company name and the total_profit.

SELECT
company,
SUM(profits) as total_profit
FROM
forbes_global_2010_2014
GROUP BY 1

The outer query will use this as input and rank the results using the window function, RANK( ) to sort the profits in descending order with the OVER clause. We will store the ranks in a new column called top_companies in the data.

SELECT
company,
RANK() OVER(ORDER BY total_profit) as top_companies,
total_profit
FROM
  (
   SELECT
   company,
   SUM(profits) as total_profit
   FROM
   forbes_global_2010_2014
   GROUP BY 1 
  )sq1

Now, figuring out the crunch part is done i.e., ranking and calculating profit per company. We have to print the company name and the profit of the top 3 companies. This can be done easily by using the outermost query, with a WHERE clause to filter the top 3 results.

SELECT
company,
total_profit
FROM
(
SELECT
company,
RANK() OVER(ORDER BY total_profit) as top_companies,
total_profit
FROM
    (
     SELECT
     company,
     SUM(profits) as total_profit
     FROM
     forbes_global_2010_2014
     GROUP BY 1 
    )sq1
)sq2
WHERE top_companies <=3;

#3. Google Business Analyst Interview Question: Counting Instances in Text

link to the question: https://platform.stratascratch.com/coding/9814-counting-instances-in-text?tabname=question

This problem requires the need of using text-matching pattern techniques to find the number of occurrences of a word in the given text.

The detail of the given table is as follows, it has only two fields with both having text data.

Let’s have a look at the data table.

To achieve the desired output we need to count the number of records that has the word “Bull” or “Bear” in them. And, this can be done using the ILIKE operator to perform the string pattern matching.

Additionally, an advantage of the operator is that it is case-insensitive. We will also use a % wildcard character to find the character in any sequence.

SELECT
'BULL' AS WORD,
COUNT(*)
FROM
google_file_store
WHERE contents ILIKE "%bull%"

And, we are going to use UNION over here in order to append the records for the word count of both ‘Bull’ as well as ‘Bear’.

SELECT
'BULL' AS A WORD,
COUNT(*)
FROM
google_file_store
WHERE contents ILIKE "%bull%"

UNION

SELECT
'BEAR' AS WORD,
COUNT(*)
FROM
google_file_store
WHERE contents ILIKE "%bear%"

This is our desired output.

Conclusion

The role of a Business Analyst requires expertise in both data and business. And to deal with data, they use SQL quite often used, and gaining expertise in it can increase your chances of landing a job.

In this article, 3 SQL interview questions were explained from Amazon, Forbes, and Google. We used aggregation, grouping, ordering of data, self and inner join, window function, nested queries, and string matching operator. These are not the only questions and concepts, you can check out more questions with different difficulty levels on the internet because there are plenty!