# 11 Nagarro SQL Interview Questions (Updated 2024)

Updated on

June 23, 2024

At Nagarro, SQL is used for analyzing large datasets for actionable insights and managing data schemas in the tech consulting industry. That's the reason behind why Nagarro almost always evaluates jobseekers on SQL query questions in interviews for Data Science, Data Engineering and Data Analytics jobs.

So, to help you study, we've curated 11 Nagarro SQL interview questions – can you solve them?

## 11 Nagarro SQL Interview Questions

### SQL Question 1: Rolling 3 Months Active Users per Product

Assume that Nagarro is an e-commerce company. Their product team wants to analyze user engagement on a rolling 3 months basis. Specifically, they want to calculate how many unique active users a product has on a rolling 3 months basis, sorted by date and product.

Use the schema and sample data from the reviews table provided below for this question.

##### Example Input:
review_iduser_idsubmit_dateproduct_idstars
61711232022-01-08500014
78022652022-02-10698524
52933622022-03-18500013
63521922022-04-26698523
45179812022-05-05698522

We define an active user as a user that has submitted at least one review in the timeframe. Note: use the to calculate the rolling 3 month window.

##### Example Output:
end_of_periodproduct_idactive_users
2022-03-31500012
2022-03-31698521
2022-04-30500011
2022-04-30698522
2022-05-31500010
2022-05-31698522

This query calculates the number of unique active users per product on a rolling 3 months basis. It uses a subquery to filter the relevant reviews for each rolling 3 month window, and then counts the distinct users. The results are ordered by the end of the rolling 3 month period in ascending order and product id in ascending order. Keep in mind that if a user has not submitted a review for a product in a given 3 month period, the product will not appear in the results for that period.

Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur

### SQL Question 2: 2nd Highest Salary

Assume you had a table of Nagarro employee salary data. Write a SQL query to find the 2nd highest salary at the company.

#### Nagarro Example Input:

employee_idsalary
12500
2800
31000
41200

#### Example Output:

second_highest_salary
1200

Try this question and run your code right in the browser:

You can find a detailed solution here: 2nd Highest Salary.

### SQL Question 3: In SQL, are values same the same as zero or a blank space?

A value represents a missing or unknown value. It is different from a zero or a blank space, which are actual values.

It's important to handle values properly in SQL because they can cause unexpected results if not treated correctly. For example, comparisons involving values always result in . Also values are not included in calculations. For example, will ignore values in the column.

### SQL Question 4: Employee Management System

Nagarro is a global software development and technology consultancy company. To manage its vast pool of employees and their projects efficiently, Nagarro is thinking of developing an Employee Management System. Design the database for this system. The database should be capable of storing details about employees, their departments, projects they are assigned to, and their roles in those projects. Also, write an SQL query to retrieve a list of all the employees who are working on more than one project.

Consider the following tables:

##### Example Input:
employee_idfirst_namelast_namedepartment_id
1JohnDoe100
2JaneSmith200
3JimBrown300
##### Example Input:
department_iddepartment_name
100Engineering
200Marketing
300Human Resources
##### Example Input:
project_idproject_namedepartment_id
50System Design100
60Brand Campaign200
##### Example Input:
employee_idproject_id
150
260
160
350

This query joins the and tables on the field. It groups the result by employee's first and last name, and then filters the groups to only include those where an employee is working on more than one project. This is achieved by using the clause to filter the result of the clause. The result is a list of employees who are working on multiple projects.

### SQL Question 5: How do the 'BETWEEN' and 'IN' commands differ?

While both the and operators are used to filter data based on some criteria, selects for values within a given range, whereas for it checks if the value is in a given list of values.

For example, say you had a table called , which had the salary of the employee, along with the country in which they reside.

To find all employees who made between $80k and$120k, you could use the operator:

To find all employees that reside in the US or Canada, you could use the operator:

### SQL Question 6: Filter Customers Based on Specific Conditions

You are a data analyst at Nagarro. The data management team has a table consisting of customer records that includes their contact information and the date they joined the company. Your goal is to design a SQL query that filters this table to return only the customers who joined the company after 1st January 2020 and are either from the US or have an email with domain '@nagarro.com'.

The following table presents an example of the table:

Example Input:

customer_idfirst_namelast_nameemailCountryjoin_date
001JohnDoejohn.doe@nagarro.comGermany2020-03-01
002JaneSmithjane.smith@yahoo.comUS2019-11-11
003JamesJohnsonj.johnson@hotmail.comUS2021-01-01
005RobertBrownrobert.brown@nagarro.comEngland2021-07-01

With this query, you will get a list of customers who joined the company after January 1, 2020, and are either from the United States or have an email account with '@nagarro.com' domain. The 'LIKE' operator is used to match text string patterns. In our case, any email that ends with '@nagarro.com'. The 'AND' and 'OR' operators used in the where clause filter the records based on multiple conditions.

### SQL Question 7: What does / SQL commands do?

Note: interviews at Nagarro often aren't trying to test you on a specific flavor of SQL. As such, you don't need to exactly know that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle – you just need to know the general concept!

Your answer should mention that the / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.

Here's a PostgreSQL example of using EXCEPT to find all of Nagarro's Facebook video ads with more than 50k views that aren't also being run on YouTube:

If you want to retain duplicates, you can use the EXCEPT ALL operator instead of EXCEPT. The EXCEPT ALL operator will return all rows, including duplicates.

### SQL Question 8: Average Project Duration

You are given a table 'projects' in Nagarro's database. Each row of the table represents a project carried out by the company. The table has the following Columns: project_id, start_date, end_date. Can you write a SQL query that gives the average duration (in days) of all projects undertaken by Nagarro in the year 2020?

##### Example Input:
project_idstart_dateend_date
12020-01-052020-03-10
22020-07-012020-09-30
32020-04-032020-05-12
42020-12-192021-01-10
52019-11-232019-12-02
##### Example Output:
avg_duration_days
78

In the above SQL query, we are extracting the project durations by finding the difference of and . We use the function to get the number of DAYS difference from these dates. We filter out projects only for the year 2020 using WHERE clause. Then, we calculate the average of these durations using the function. The output will be the average project duration for Nagarro in 2020. Please note, this query assumes that the and are of type .

To practice a very similar question try this interactive Facebook Average Post Hiatus (Part 1) Question which is similar for calculating duration between dates or this Facebook Active User Retention Question which is similar for date-based analysis.

### SQL Question 9: Find the Maximum Sold Product per Month

Nagarro, being a custom software development and services company, provides many software services and solutions to their clients. Let's assume that we want to find the product that has been sold the most for each month.

Consider a scenario where we have a sales table in the database that keeps track of all software products sold, the client who bought them, and the date of purchase.

##### Example Input:
sale_idclient_idpurchase_dateproduct_idquantity
10156706/05/2022A015
10267206/14/2022A0310
10389006/30/2022A017
10456707/11/2022A029
10589007/16/2022A016

The task is to write an SQL query to find the software product that has been sold the most for each month. The output should provide the month, product_id, and sum of quantity sold.

##### Example Output:
mthproduct_idmaximum_sold
6A0112
7A029

This SQL query makes use of the function to get the month from the date of the sales data. We then group by both the extracted month and product_id to get the sum of the quantity sold for each product in each month. The clause is used to sort the results in descending order of the quantity sold.

### SQL Question 10: Can you explain the distinction between a unique and a non-unique index?

While both types of indexes improve the performance of SQL queries by providing a faster way to lookup rows of data, a unique index enforces the uniqueness of the indexed columns while a non-unique index allows duplicate values in the indexed columns.

Suppose you had a table of Nagarro employees. Here's an example of a unique index on the column:

This index would ensure that no two Nagarro employees have the same , which could be used as a unique identifier for each employee.

Here's a non-unique index example example on the column:

This index would not enforce uniqueness, but it could be used to improve the performance of queries that filter or sort the data based on the column. For example, if you want to quicklly retreive all Data Scientists, the database can use the index to efficiently locate and retrieve the desired records without having to do a full table scan on all Nagarro employees.

### SQL Question 11: Fetch Customer Records With Specific Pattern

Given a table named , your task is to write a SQL query that will fetch all the records of customers whose email addresses end with "@nagarro.com". {#Question-11}

##### Example Input:
09876JohnDoejohn.doe@nagarro.com
87654SallyMaesally.mae@yahoo.com
56789SamSmithsam.smith@nagarro.com
45678AlexJohnsonalex.johnson@gmail.com
34567EmmaLeeemma.lee@nagarro.com

You can make use of the keyword in SQL to filter the records based on a specific pattern. To get the required results, you can use the following PostgreSQL query:

This query will return all the records where the email address column ends with "@nagarro.com". The percent sign (%) is a wildcard character that matches any sequence of characters.

##### Example Output:
09876JohnDoejohn.doe@nagarro.com
56789SamSmithsam.smith@nagarro.com
34567EmmaLeeemma.lee@nagarro.com

### Preparing For The Nagarro SQL Interview

The best way to prepare for a Nagarro SQL interview is to practice, practice, practice. Beyond just solving the earlier Nagarro SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Google, Facebook, Microsoft and Amazon.

Each SQL question has hints to guide you, step-by-step solutions and most importantly, there's an interactive SQL code editor so you can instantly run your SQL query answer and have it checked.

To prep for the Nagarro SQL interview it is also helpful to solve SQL questions from other tech companies like:

However, if your SQL coding skills are weak, forget about jumping right into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Analytics.

This tutorial covers things like UNION vs. joins and Self-Joins – both of which come up often in SQL interviews at Nagarro.

### Nagarro Data Science Interview Tips

#### What Do Nagarro Data Science Interviews Cover?

Beyond writing SQL queries, the other types of questions covered in the Nagarro Data Science Interview are:

#### How To Prepare for Nagarro Data Science Interviews?

I'm sorta biased, but I think the optimal way to prep for Nagarro Data Science interviews is to read my book Ace the Data Science Interview.

The book has 201 data interview questions taken from companies like Microsoft, Google & Amazon. It also has a refresher covering Stats, SQL & ML. And finally it's vouched for by the data community, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.