11 Endava SQL Interview Questions (Updated 2024)

At Endava, SQL is crucial for extracting and manipulating data from databases across the multitude of digital transformation and software consulting clients Endava has. Unsurprisingly this is why Endava almost always evaluates jobseekers on SQL problems during interviews for Data Analytics, Data Science, and Data Engineering jobs.

So, to help you prepare for the Endava SQL interview, we'll cover 11 Endava SQL interview questions – able to answer them all?

11 Endava SQL Interview Questions

SQL Question 1: Identifying 'Whale' Customers at Endava

Endava has an IT solutions shop where customers can purchase software licenses and other digital solutions. You are provided with two tables: 'purchase_records' and 'customers'. The 'purchase_records' table contains the record of all the purchases made by customers and the 'customers' table contains details about each customer.

The 'purchase_records' table has the following columns:

1. purchase_id
2. customer_id
3. purchase_date
4. product_id
5. amount_paid

The 'customers' table has the following columns:

1. customer_id
2. customer_name
3. sign_up_date

A 'whale' customer is defined as a customer whose total purchases exceed a certain threshold. For this case, the threshold is fixed at $5000.

Write a SQL query to find the 'whale' customers i.e., customers whose total purchases are more than $5000.

'purchase_records' Example Input:

'customers' Example Input:

Answer:

This query joins the 'customers' and 'purchase_records' tables on the common 'customer_id' field. It then groups the records by 'customer_name' and calculates the total amount paid by each customer. The HAVING clause then filters out only the customers who have paid more than $5000, thus identifying the 'whale' customers.

To solve a similar VIP customer analysis question on DataLemur's free interactive SQL code editor, try this Microsoft Azure Cloud SQL Interview Question:

SQL Question 2: Monthly active users per product

Endava maintains a database for product usage by its customers. You are asked to analyze the number of active users for each product on a monthly basis.

You have the below dataset :

Example Input:

Expected Output:

The "active users" means unique users who have interacted with a product within a specific month.

Answer:

You can solve this problem by using SQL window function to split the dataset by month and product, and then count distinct users:

This query will return the active users in each month for each product. The COUNT(DISTINCT user_id) OVER(PARTITION BY DATE_PART('month', event_date), product_id) will give us the unique count of users who interacted with a product per month.

Pro Tip: Window functions are a frequent SQL interview topic, so practice every window function problem on DataLemur

SQL Question 3: What's the difference between and ?

The clause works similarly to the clause, but it is used to filter the groups of rows created by the clause rather than the rows of the table themselves.

For example, say you were analyzing Endava sales data:

This query retrieves the total sales for all products in each region, and uses the clause to only sales made after January 1, 2023. The rows are then grouped by region and the clause filters the groups to include only those with total sales greater than $500k.

SQL Question 4: Project Timesheet Analysis

As a global IT consultancy, Endava manages numerous projects at a time. Each employee logs their hours spent per project in a timesheet system. The management wants to analyze this data to understand project resource usage. They are interested in two specific queries:

1. Total hours logged per project for a given month.
2. Top 3 employees who've spent the most time on a particular project in a given month.

Design the necessary tables for this scenario (You can assume relevant employee and project details) and write the SQL queries for the above-mentioned requests.

Assume the following tables:

Example Input:

Example Input:

Example Input:

Answer:

1. Total hours logged per project for a given month.

2. Top 3 employees who've spent the most time on a particular project in a given month.

These queries help in analyzing the project resource usage by giving insights into the total hours logged per project and individuals contributing the most hours to a specific project.

SQL Question 5: What do the / operators do, and can you give an example?

The EXCEPT operator is used to return all rows from the first SELECT statement that are not returned by the second SELECT statement. Note that is available in PostgreSQL and SQL Server, and it's equivalent operator is called and is available in MySQL and Oracle.

For a tangible example, suppose you were doing an HR Analytics project for Endava, and had access to Endava's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all employees who never were a contractor using this query:

SQL Question 6: Filtering customer records based on multiple conditions

Endava is an IT services company specializing in application development, independent testing, cloud services, and IT infrastructure services. They want to understand better their service delivery performance in different countries and sectors.

Assume that you have a table named with the following structure:

Example Input:

In the table, write an SQL query that filters the data based on the following conditions:

1. Customers who are from UK and USA.
2. Customers who are in the Banking and Telecom sectors.
3. Customers whose billing cycle is either Quarterly or Annually.
4. Customers who have a payment due date in the year 2022.

The result should be ordered by amount_due in descending order.

Answer:

This query uses the WHERE clause combined with IN and AND operations to filter the records based on multiple conditions. The EXTRACT function is used to filter records based on the year of the payment due date. The ORDER BY clause is used to order the result in descending order by amount due.

SQL Question 7: Can you describe the difference 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, meaning that no duplicate values are allowed in the indexed columns. On the other hand, a non-unique index allows duplicate values in the indexed columns.

Here is an example of a unique index on the column of a table of Endava employees:

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

Here is an example of a non-unique index on the column of the same table:

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 retrieve all employees who were Data Analysts, the database can use the index to quickly locate and retrieve the desired records without having to search through all the employees.

SQL Question 8: Find All Customers From Specific Location

As an SQL developer at Endava, often you will be tasked with fetching specific information from the company's database. Assume that we have a table where we store information about all of our customers. Your task is to write a SQL query that fetches all records of customers from London. You should make use of the SQL command.

Example Input:

Answer:

Here is how you would structure your SQL query in PostgreSQL:

This query will return all the columns for the customers located in London. The operator is used in the clause to search for a specified pattern in a column.

Example Output:

SQL Question 9: Most Tasked Employees

Endava has a system where employees log their tasks on various projects running in the company. As a data analyst in Endava, you are tasked with writing a SQL query that will identify the employees with the most number of tasks in each project. You have access to two tables: table which holds employee details and table which logs the tasks.

Table:

Table:

You should return a table with the project and the name of the employee who has the most tasks in that project.

In case of tie, return the employee that appears first alphabetically (by first name).

Answer:

This query will join the and tables on the and fields respectively. It then groups the results by project and uses the clause to filter out the groups that have the maximum count of tasks (which indicates that these are the employees with the most tasks). The results are then ordered alphabetically by employee name. Note that this query is simplified and assumes every task is unique and assigned to only one employee, for more complex scenarios the query may need adjustments.

Since joins come up frequently during SQL interviews, try this Spotify JOIN SQL question:

SQL Question 10: What does adding 'DISTINCT' to a SQL query do?

The clause in SQL allows you to select records that are unique, eliminating duplicates.

For example, if you had a table of Endava employees, and wanted to see what are all the unique job titles that currently worked at the company, you could write the following query:

SQL Question 11: Calculate Employee Performance

Endava is a software development company, and at the end of each project, clients assess the performance of each team member on different aspects, each aspect being scored out of 10. Endava now wants to calculate the average performance score of each employee, their highest and lowest score, and the range of these scores (highest - lowest). The final result should be rounded off to 2 decimal places.

Example Input:

Example Output:

Answer:

This query calculates the average, highest, lowest and range of the employees' scores across all reviews. Each row in the output represents a single employee and their respective performance information. The ROUND function is used to reduce the decimal places in the average to 2. The GREATEST and LEAST functions are used to find the highest and lowest scores respectively, across all aspects. The range is calculated as the difference between the highest and lowest scores.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages and rounding results, or this JPMorgan Chase Cards Issued Difference Question which is similar for finding highest and lowest values and calculating their difference.

Preparing For The Endava SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Endava SQL interview is to solve as many practice SQL interview questions as you can! In addition to solving the earlier Endava SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like FAANG and tech startups.

Each DataLemur SQL question has multiple hints, full answers and most importantly, there's an online SQL coding environment so you can right online code up your query and have it executed.

To prep for the Endava SQL interview it is also a great idea to solve interview questions from other tech companies like:

• Globant SQL questions

• EPAM Systems SQL questions

• Accenture SQL questions

• Cognizant SQL questions

However, if your SQL query skills are weak, forget about going right into solving questions – improve your SQL foundations with this free SQL tutorial.

This tutorial covers topics including window functions and using LIKE – both of these pop up often in Endava SQL assessments.

Endava Data Science Interview Tips

What Do Endava Data Science Interviews Cover?

Besides SQL interview questions, the other topics covered in the Endava Data Science Interview are:

• Statistics and AB Testing Questions
• Coding Questions in Python or R
• Product-Sense Questions
• ML Modelling Questions
• Behavioral & Resume-Based Questions

How To Prepare for Endava Data Science Interviews?

To prepare for Endava Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from Google, Microsoft & tech startups
• a refresher on Product Analytics, SQL & ML
• over 900+ 5-star reviews on Amazon