interview-anxiety

7 Must-Know Business Analyst SQL Interview Questions

Tim S

Tim S

Tim currently works at FAANG as a data analyst. He's been in the industry for 5+ years and writes for Big Tech Interviews on the weekends. His favorite language is SQL and calls Denver, CO home.

Want to land your dream job as a business analyst? You’re on the right path. The business analyst role is one of the more sought-after roles in tech, it averages up to $150,000 per year in the USA. However, receiving an offer is no easy thing. It requires both technical and business acumen expertise, one of the parts most candidates fail is the SQL interview round. SQL is used to manage and manipulate data in relational databases. It allows for the retrieval, insertion, deletion, and modification of data stored in tables. Business analysts often use SQL to extract insights from large datasets and make informed business decisions. 

So without further ado, try and solve the 7 SQL questions below without looking at the answers. After completing the test, review the answers and grade your results. This will give you an indicator of how well-prepared you are for your next SQL business analyst interview. 

Table of Contents

1. What is the difference between primary and foreign keys?

A primary key is a unique identifier for a specific record in a table, used to ensure data integrity and facilitate data retrieval. A foreign key is a column in one table that references the primary key of another table, creating a relationship between the two tables.

For example, say we have a scenario where we need to retrieve the total revenue generated by each product category, ordered by revenue in descending order. The result should only include categories with revenue greater than or equal to $200.00. 

Table: order_items
+--------------+-----------+
| Column Name  | Type      |
+--------------+-----------+    
|order_id      |int        |
|product_id    |int        |
|quantity      |int        |
|price         |dec        |
+--------------+-----------+
Table: products
+--------------+-----------+
| Column Name  | Type      |
+--------------+-----------+    
|product_id    |int        |
|product_name  |int        |
|category      |int        |
+--------------+-----------+

In this scenario, the product_id is the foreign key in the products table and the primary key is the order_id in the order_items table.

Want to practice solving this question yourself? Practice SQL Questions

2. How do you find duplicate records in a SQL query?

This is a classic SQL interview question for business analyst interviews and also a very common scenario you’ll run into working with data.

Suppose due to a technical glitch, some employees’ clock-in logs were accidentally duplicated in the clock_in_logs table. How would we identify these records?

Table: clock_in_logs
+--------+-------------+-------------+--------------------------+
| log_id | employee_id | location_id |       clock_in           |
+--------+-------------+-------------+--------------------------+
|   1    |      1      |      1      | 2024-03-19T08:00:00.000Z |
|   2    |      2      |      2      | 2024-03-19T08:15:00.000Z |
|   3    |      3      |      1      | 2024-03-19T07:45:00.000Z |
|   4    |      4      |      3      | 2024-03-19T08:30:00.000Z |
|   5    |      5      |      1      | 2024-03-19T08:05:00.000Z |
|   6    |      1      |      1      | 2024-03-19T08:00:00.000Z |
|   7    |      2      |      2      | 2024-03-19T08:15:00.000Z |
|   8    |      3      |      1      | 2024-03-19T07:45:00.000Z |
+--------+-------------+-------------+--------------------------+

Output

Want to practice this yourself before you see the solution? Practice SQL Questions.

SQL Solution

SELECT 
    employee_id,
    location_id,
    clock_in,
    COUNT(*) AS duplicate_count
FROM 
    clock_in_logs
GROUP BY 
    employee_id,
    location_id,
    clock_in
HAVING 
    COUNT(*) > 1;

Explanation

In this SQL query solution, we aim to identify duplicate clock-in logs for employees due to a technical glitch. Here’s how the query accomplishes this:

  • SELECT statement: We’re selecting specific columns from the clock_in_logs table:
  • employee_id: Identifies the employee associated with the clock-in log.
  • location_id: Indicates the location where the clock-in occurred.
  • clock_in: Represents the timestamp of the clock-in event.
  • COUNT(*) AS duplicate_count: Counts the occurrences of each combination of employee_id, location_id, and clock_in to identify duplicates.
  • FROM clause: Specifies the source table for our query, which is clock_in_logs.
  • GROUP BY clause: Groups the records based on employee_id, location_id, and clock_in. This grouping is essential for the COUNT(*) function to calculate the number of occurrences for each combination.
  • HAVING clause: Filters the grouped results to only include records where the count of occurrences (COUNT(*)) is greater than 1. This ensures that we’re only retrieving records that have been duplicated.

By executing this query, we’ll obtain a list of employee clock-in logs where multiple occurrences exist for the same employee, location, and clock-in timestamp, helping us identify and address the duplicated records resulting from the technical glitch

3. What are the four most common joins in SQL?

The four main types of joins in SQL are:

  • Inner Join: Returns records that have matching values in both tables.
  • Left Join: Returns all records from the left table and the matched records from the right table.
  • Right Join: Returns all records from the right table and the matched records from the left table.
  • Full Outer Join: Returns all records when there is a match in either the left or right table.
sql-join-examples

To illustrate the various JOIN types in SQL, consider a scenario where we want to compile the relationship between order figures and their corresponding client representatives across different cities.

For this purpose, we have two tables: orders and clients. They are linked by the client_id field, which serves as a foreign key in the orders table and a primary key in the clients table. Here’s what that looks like:

Table: Orders
+----------+---------+-----------+-------------+--------+
| order_id | sale_id | client_id | city        | amount |
+----------+---------+-----------+-------------+--------+
| 1        | 1       | 101       | Los Angeles | 1000.00|
| 2        | 2       | 102       | New York    | 1500.50|
| 3        | 3       | 103       | Chicago     | 2000.00|
| 4        | 4       | 104       | New York    | 2500.75|
| 5        | NULL    | NULL      | Los Angeles | 3000.00|
+----------+---------+-----------+-------------+--------+
Table: Clients
+-----------+--------------+-------------+
| client_id | client_name  | city        |
+-----------+--------------+-------------+
| 101       | John Doe     | New York    |
| 102       | Jane Smith   | New York    |
| 103       | Jim Beam     | Los Angeles |
| 104       | Jill Jackson | Los Angeles |
| 105       | Jack Johnson | Chicago     |
+-----------+--------------+-------------+

Left Joins

Suppose we need to extract all orders along with their corresponding client information. This includes clients who haven’t placed any orders yet, but we are only interested in orders with amounts exceeding $2000. To achieve this, we’ll utilize a LEFT JOIN with specific conditions:

SELECT o.order_id, c.client_name
FROM orders o
LEFT JOIN clients c ON o.client_id = c.client_id
WHERE o.amount > 2000 OR o.amount IS NULL;

Want to practice this yourself? Practice SQL Interview Questions

RIGHT JOIN

Imagine we want to gather a comprehensive list of clients alongside the total amount of orders they’ve placed. This should encompass clients who haven’t placed any orders yet. Employing a RIGHT JOIN combined with aggregation functions enables us to achieve this:

SELECT c.client_name, COALESCE(SUM(o.amount), 0) AS total_amount
FROM clients c
RIGHT JOIN orders o ON c.client_id = o.client_id
GROUP BY c.client_name;

Want to practice this yourself? Practice SQL Interview Questions

INNER JOIN

Consider a requirement to match orders with clients in cities where the total sales amount exceeds $4000. An INNER JOIN with multiple conditions can effectively fulfill this task:

SELECT o.order_id, c.client_name
FROM orders o
INNER JOIN clients c ON o.client_id = c.client_id
INNER JOIN (

    SELECT city, SUM(amount) AS total_sales
    FROM orders
    GROUP BY city
    HAVING SUM(amount) > 4000
) s ON c.city = s.city;

Want to practice this yourself? Practice SQL Interview Questions

FULL OUTER JOIN

Suppose we aim to retrieve all orders and clients, accounting for scenarios where there might not be a matching client or order. Furthermore, we want to handle null values appropriately. By employing a FULL JOIN with proper null handling, we can achieve this:

SELECT COALESCE(o.order_id, 'Unknown') AS order_id, COALESCE(c.client_name, 'Unknown') AS client_name, COALESCE(o.amount, 0) AS amount
FROM orders o
FULL JOIN clients c ON o.client_id = c.client_id;

Want to practice this yourself? Practice SQL Interview Questions

4. How do Subqueries and Common Table Expressions (CTEs) work? 

One of the most useful and powerful SQL concepts as a business analyst is mastering Subqueries and Common Table Expressions (CTEs). 

Subqueries allow you to create queries within another SQL statement. At the same time, CTEs enable you to generate temporary result sets that you can reference with a SELECT, INSERT, UPDATE, or DELETE statement.

Subqueries:

  • Scalar Subquery: A subquery that yields a solitary value.
  • Column Subquery: A subquery that yields one or more columns.
  • Table Subquery: A subquery that resembles a table (suitable for any operator anticipating a table).

CTEs:

  • Offer a more comprehensible and sustainable alternative to a derived table or subquery.
  • Can refer to themselves, particularly beneficial for recursive queries.

Subquery

Suppose you’re working as a business analyst at a telecommunications company, and you’ve been tasked with identifying all phone numbers in the all_numbers table that have not been confirmed yet, based on the confirmed_numbers table.

Table: all_numbers
+-------------------+
|   phone_number    |
+-------------------+
|   706-766-8523    |
|   555-239-6874    |
|   407-234-5041    |
|   (123)351-6123   |
|   251-874-3478    |
+-------------------+
Table: confirmed_numbers
+-----------------+
|  phone_number   |
+-----------------+
|   555-239-6874  |
|   407-234-5041  |
|  (123)351-6123  |
+-----------------+

Want to practice this yourself before you see the solution? Practice SQL Questions.

SQL Solution

SELECT phone_number
FROM all_numbers
WHERE phone_number NOT IN (
  SELECT phone_number
  FROM confirmed_numbers
);

Explanation

This query retrieves all phone numbers from the all_numbers table that are not present in the confirmed_numbers table.

  1. SELECT phone_number: This part of the query specifies that we are interested in retrieving the “phone_number” column from the “all_numbers” table.
  2. FROM all_numbers: Indicates that the data will be selected from the “all_numbers” table.
  3. WHERE phone_number NOT IN (SELECT phone_number FROM confirmed_numbers): This is the crucial part of the query. It filters the phone numbers from the “all_numbers” table based on whether they are not found in the list of confirmed phone numbers from the “confirmed_numbers” table. The subquery (SELECT phone_number FROM confirmed_numbers) retrieves all phone numbers that have been confirmed. The main query then selects phone numbers from “all_numbers” that are not present in this list, effectively identifying the unconfirmed phone numbers.

In summary, this SQL query efficiently identifies unconfirmed phone numbers by comparing the “all_numbers” table with the “confirmed_numbers” table and selecting those that do not appear in the latter.

CTE

Now, imagine you’re a data engineer at a call center, and you need to the caller and receiver details. You want to use a Common Table Expression (CTE) to make the query more efficient.

Table: all_numbers
+-------------------+
|   phone_number    |
+-------------------+
|   706-766-8523    |
|   555-239-6874    |
|   407-234-5041    |
|   (123)351-6123   |
|   251-874-3478    |
+-------------------+
Table: calls
+------------+-------------+---------------+---------------------+
| caller_id  | receiver_id | call_duration |   call_timestamp    |
+------------+-------------+---------------+---------------------+
|     1      |      2      |   00:15:23    | 2024-03-19 09:30:00 |
|     2      |      1      |   00:10:45    | 2024-03-19 10:45:00 |
|     3      |      1      |   00:20:00    | 2024-03-19 12:00:00 |
+------------+-------------+---------------+---------------------+
Table: users
+----------------+---------------------+
|      name      |      email          |
+----------------+---------------------+
| Alice Johnson  | alice@example.com   |
|   Bob Smith    |  bob@example.com    |
| Charlie Brown  | charlie@example.com |
+----------------+---------------------+

Want to practice this yourself before you see the solution? Practice SQL Questions.

SQL Solution

WITH CallDetails AS (
    SELECT
        c.call_duration,
        u_caller.name AS caller_name,
        u_receiver.name AS receiver_name
    FROM
        calls c
    INNER JOIN users u_caller ON c.caller_id = u_caller.user_id
    INNER JOIN users u_receiver ON c.receiver_id = u_receiver.user_id
    INNER JOIN all_numbers an_caller ON u_caller.name = an_caller.owner_name
    INNER JOIN all_numbers an_receiver ON u_receiver.name = an_receiver.owner_name
)
SELECT
    cd.caller_name,
    cd.receiver_name,
    cd.call_duration
FROM
    CallDetails cd
    ORDER BY call_duration ASC;

Explanation

This SQL query is designed to retrieve caller and receiver details, including call duration, from a call center database. It utilizes a Common Table Expression (CTE) named “CallDetails” to streamline the query and make it more efficient.

  1. WITH CallDetails AS (…): This marks the beginning of the Common Table Expression (CTE) named “CallDetails”. CTEs are temporary result sets that can be referenced within the scope of the main query.
  2. SELECT c.call_duration, u_caller.name AS caller_name, u_receiver.name AS receiver_name…: Inside the CTE, this SELECT statement retrieves the call duration along with the names of the caller and receiver. It joins the “calls” table (aliased as ‘c’) with the “users” table twice (aliased as ‘u_caller’ and ‘u_receiver’) based on the caller and receiver IDs. It also joins the “all_numbers” table twice (aliased as ‘an_caller’ and ‘an_receiver’) based on the owner names of the caller and receiver.
  3. FROM calls c INNER JOIN users u_caller ON c.caller_id = u_caller.user_id INNER JOIN users u_receiver ON c.receiver_id = u_receiver.user_id INNER JOIN all_numbers an_caller ON u_caller.name = an_caller.owner_name INNER JOIN all_numbers an_receiver ON u_receiver.name = an_receiver.owner_name: This part of the query specifies the tables involved and the conditions for joining them. It ensures that only relevant calls and corresponding caller/receiver details are included in the result set.
  4. SELECT cd.caller_name, cd.receiver_name, cd.call_duration FROM CallDetails cd ORDER BY call_duration ASC;: After defining the CTE, this SELECT statement outside the CTE selects the caller name, receiver name, and call duration from the “CallDetails” CTE. It orders the results by call duration in ascending order.

5. How do you use a CASE WHEN in SQL?

In SQL, the CASE statement is a powerful tool used to perform conditional logic within queries. It allows you to specify different conditions and return different values based on those conditions. Each WHEN clause checks a condition, and if the condition is true, it returns the specified result. If none of the conditions are true, the ELSE clause (optional) provides a default result.

In this example, we have a table named active_users with columns user_id, country, active, and date. Suppose, we want to add a new column status that indicates whether the user is active or inactive based on the value of the active column. We use a CASE statement to check the value of the active column. If it’s true, we label the user as ‘Active’, otherwise ‘Inactive’.

Table: actions
+-------+-------+-------------------------+
|user_id|action |date                     |
+-------+-------+-------------------------+
|1      |post   |current_timestamp::DATE-3|
|2      |edit   |current_timestamp::DATE-2|
|3      |post   |current_timestamp::DATE-1|
|4      |post   |current_timestamp::DATE-1|
|5      |edit   |current_timestamp::DATE-5|
|6      |cancel |current_timestamp::DATE-2|
|7      |post   |current_timestamp::DATE-2|
|8      |post   |current_timestamp::DATE-1|
|9      |post   |current_timestamp::DATE-1|
|10     |cancel |current_timestamp::DATE-3|
|11     |post   |current_timestamp::DATE-2|
|12     |post   |current_timestamp::DATE-2|
+-------+-------+-------------------------+
table: active_users
+-------+---------+-------+-------------------------+
|user_id|country  |active |date                     |
+-------+---------+-------+-------------------------+
|8      |USA      |true   |current_timestamp::DATE-1|
|9      |Spain    |true   |current_timestamp::DATE-1|
|3      |Colombia |true   |current_timestamp::DATE-1|
|4      |Colombia |true   |current_timestamp::DATE-1|
|7      |France   |false  |current_timestamp::DATE-2|
+-------+---------+-------+-------------------------+

Want to practice this yourself before you see the solution? Practice SQL Questions.

select round(1.0*
sum(case when action='post' then 1 else 0 end)
/
count(1)
,2) post_rate
from actions;

Explanation

In preparation for a meeting, a manager needs to present the current post rate from a dataset, possibly related to a social media platform or forum. To achieve this, a SQL query is devised to calculate the post rate by counting ‘post’ actions and dividing by the total actions recorded. The result is rounded to two decimal places for clarity.

  • SUM(CASE WHEN action=’post’ THEN 1 ELSE 0 END): This part of the query calculates the total number of posts by summing up the instances where the action is ‘post’. The CASE statement acts as a conditional count, incrementing the count by 1 if the action is ‘post’, and 0 otherwise.
  • COUNT(1): This counts the total number of records in the “actions” table.
  • 1.0 * … / …: Multiplying by 1.0 converts the result to a floating-point number to ensure accurate division. Then, it divides the total number of posts by the total number of records to calculate the post rate.
  • ROUND(…, 2): Finally, the result is rounded to two decimal places using the ROUND function, as per the requirement to showcase the post rate.

6. Can you tell me how Window Functions work?

Window functions are a versatile tool in SQL that enable you to perform calculations on a specific set of rows related to the current row, known as the window, without resorting to self-joins. This functionality encompasses tasks like calculating running totals, moving averages, and other analytical operations.

Common Window Functions:

  • ROW_NUMBER(): This function assigns a unique number to each row where a window function is utilized.
  • RANK(): Assigns a rank to each row in a result set, with identical ranks given to rows sharing the same ranking.
  • DENSE_RANK(): Functions similarly to RANK(), but assigns consecutive ranks without any gaps.

ROW_NUMBER()

Let’s start with learning how to use ROW_NUMBER(). Let’s say we want to list all employees along with their clock-in times, but we want to assign a unique number to each entry based on the clock-in time, regardless of the office location. Return the results with employee ID ASC.

Table: clock_in_logs
+--------+-------------+-------------+--------------------------+
| log_id | employee_id | location_id |       clock_in           |
+--------+-------------+-------------+--------------------------+
|   1    |      1      |      1      | 2024-03-19T08:00:00.000Z |
|   2    |      2      |      2      | 2024-03-19T08:15:00.000Z |
|   3    |      3      |      1      | 2024-03-19T07:45:00.000Z |
|   4    |      4      |      3      | 2024-03-19T08:30:00.000Z |
|   5    |      5      |      1      | 2024-03-19T08:05:00.000Z |
|   6    |      1      |      1      | 2024-03-19T08:00:00.000Z |
|   7    |      2      |      2      | 2024-03-19T08:15:00.000Z |
|   8    |      3      |      1      | 2024-03-19T07:45:00.000Z |
+--------+-------------+-------------+--------------------------+

You can practice this SQL question here.

SQL Solution

SELECT 
    employee_id,
    clock_in,
    ROW_NUMBER() OVER (ORDER BY clock_in) AS row_num
FROM 
    clock_in_logs
ORDER BY employee_id ASC; 

Explanation

This SQL query efficiently retrieves a list of employees along with their clock-in times, assigning a unique number to each entry based on the clock-in time, and sorts the results by employee ID in ascending order. This provides a clear and organized output for further analysis or reporting purposes.

  • SELECT employee_id, clock_in, ROW_NUMBER() OVER (ORDER BY clock_in) AS row_num: This SELECT statement retrieves the employee ID and clock-in time from the “clock_in_logs” table. Additionally, it utilizes the ROW_NUMBER() function combined with the OVER clause to assign a unique number (row_num) to each entry based on the clock-in time. The ORDER BY clause within the ROW_NUMBER() function ensures that the numbers are assigned in ascending order of clock-in time.
  • FROM clock_in_logs: Specifies that the data will be selected from the “clock_in_logs” table.
  • ORDER BY employee_id ASC: This part of the query orders the results by employee ID in ascending order, as specified by the ASC keyword.

RANK()

Suppose we want to rank employees based on their total number of clock-ins across all office locations. Order by employee ID ASC.

Table: Employees
+------------+------------+-----------+
| EmployeeID | FirstName  | LastName  |
+------------+------------+-----------+
| 1          | John       | Doe       |
| 2          | Jane       | Smith     |
| 3          | Michael    | Johnson   |
+------------+------------+-----------+
Table: Offices
+----------+--------------+
| OfficeID | OfficeName   |
+----------+--------------+
| 101      | New York     |
| 102      | Los Angeles  |
| 103      | Chicago      |
+----------+--------------+
Table: ClockIns
+------------+------------+----------+---------------------+
| ClockInID  | EmployeeID | OfficeID | ClockInTimestamp    |
+------------+------------+----------+---------------------+
| 1          | 1          | 101      | 2024-03-29 09:00:00 |
| 2          | 1          | 101      | 2024-03-29 12:00:00 |
| 3          | 1          | 102      | 2024-03-29 14:00:00 |
| 4          | 2          | 101      | 2024-03-29 08:30:00 |
| 5          | 2          | 101      | 2024-03-29 13:30:00 |
| 6          | 2          | 103      | 2024-03-29 10:00:00 |
| 7          | 3          | 103      | 2024-03-29 09:15:00 |
+------------+------------+----------+---------------------+

Want to practice this one yourself?

SQL Solution

SELECT 
    EmployeeID,
    FirstName,
    LastName,
    TotalClockIns,
    RANK() OVER (ORDER BY TotalClockIns DESC) AS EmployeeRank
FROM (
    SELECT 
        Employees.EmployeeID,
        Employees.FirstName,
        Employees.LastName,
        COUNT(ClockIns.ClockInID) AS TotalClockIns
    FROM Employees
    LEFT JOIN ClockIns ON Employees.EmployeeID = ClockIns.EmployeeID
    GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName
) AS ClockInsCount
ORDER BY EmployeeID ASC; 

Explanation

This SQL query is aimed at ranking employees based on their total number of clock-ins across all office locations. It utilizes a subquery to calculate the total number of clock-ins for each employee, and then applies the RANK() function to assign a rank to each employee based on their total number of clock-ins.

  • Subquery (ClockInsCount): This subquery calculates the total number of clock-ins for each employee. It selects the EmployeeID, FirstName, and LastName from the “Employees” table and counts the number of clock-ins for each employee by joining the “ClockIns” table on the EmployeeID. The result is grouped by EmployeeID, FirstName, and LastName to ensure accurate aggregation.
  • RANK() OVER (ORDER BY TotalClockIns DESC) AS EmployeeRank: This part of the query uses the RANK() window function to assign a rank to each employee based on their total number of clock-ins. The employees are ordered by TotalClockIns in descending order, meaning employees with the highest number of clock-ins will have the lowest ranks.
  • SELECT EmployeeID, FirstName, LastName, TotalClockIns, EmployeeRank: This main SELECT statement retrieves the EmployeeID, FirstName, LastName, TotalClockIns (total number of clock-ins), and the assigned rank (EmployeeRank) for each employee from the subquery (ClockInsCount).
  • ORDER BY EmployeeID ASC: Finally, the results are ordered by EmployeeID in ascending order as per the requirement.

DENSE_RANK()

Find the employees with the highest number of clock-ins in each office. Display the employee’s name, office name, and the total number of clock-ins. Order by employee ID ASC.

Want to practi

Want to practice this one yourself?

SQL Solution

WITH RankedClockIns AS (
    SELECT 
        EmployeeID,
        OfficeID,
        COUNT(*) AS NumClockIns,
        DENSE_RANK() OVER (PARTITION BY OfficeID ORDER BY COUNT(*) DESC) AS Rank
    FROM 
        ClockIns
    GROUP BY 
        EmployeeID, OfficeID
)

SELECT 
    E.FirstName,
    E.LastName,
    O.OfficeName,
    RC.NumClockIns
FROM 
    RankedClockIns RC
JOIN 
    Employees E ON RC.EmployeeID = E.EmployeeID
JOIN 
    Offices O ON RC.OfficeID = O.OfficeID
WHERE 
    RC.Rank = 1
ORDER BY 1 ASC;

Explanation

This SQL query is designed to find the employees with the highest number of clock-ins in each office. It retrieves the employee’s name, office name, and the total number of clock-ins, ordering the results by employee ID in ascending order.

  1. WITH RankedClockIns AS (…): This marks the beginning of a Common Table Expression (CTE) named “RankedClockIns”. The CTE calculates the ranking of employees based on the number of clock-ins they have in each office. It selects the EmployeeID, OfficeID, and calculates the count of clock-ins for each employee in each office. The DENSE_RANK() function is used to assign a rank to each employee within their respective office based on the count of clock-ins, ordered in descending order.
  2. SELECT E.FirstName, E.LastName, O.OfficeName, RC.NumClockIns…: This SELECT statement outside the CTE selects the employee’s first name, last name, office name, and the number of clock-ins from the “RankedClockIns” CTE. It joins the “RankedClockIns” CTE with the “Employees” and “Offices” tables to retrieve the corresponding employee and office names.
  3. JOIN Employees E ON RC.EmployeeID = E.EmployeeID: This JOIN clause connects the “RankedClockIns” CTE with the “Employees” table based on the EmployeeID.
  4. JOIN Offices O ON RC.OfficeID = O.OfficeID: This JOIN clause connects the “RankedClockIns” CTE with the “Offices” table based on the OfficeID.
  5. WHERE RC.Rank = 1: This WHERE clause filters the results to only include the employees with the highest number of clock-ins in each office, as identified by the rank calculated in the CTE.
  6. ORDER BY 1 ASC: This orders the final results by the first column (employee ID) in ascending order.

What is the difference between a UNION vs UNION ALL? 

Union and Union All:

  • UNION: Retrieves unique rows present in either result set.
  • UNION ALL: Retrieves all rows, even duplicates.

Conclusion

In conclusion, having a strong understanding of SQL is essential for any business analyst. These 7 must-know SQL interview questions cover a range of important concepts such as data manipulation, joining tables, and writing complex queries. By familiarizing yourself with these questions and practicing your SQL skills, you will be better equipped to handle any data-related tasks and excel in your role as a business analyst. Keep in mind that while these are some of the most common SQL interview questions, it is also important to continuously learn and improve your skills in this area as new technologies and techniques emerge. With a solid foundation in SQL, you will be well-positioned for success in the competitive field of business analysis.

Do you want to ace your SQL interview?

Practice free and paid real SQL interview questions with step-by-step answers.

Do you want to Ace your SQL interview?

Practice free and paid SQL interview questions with step-by-step video solutions!