8 Genpact SQL Interview Questions (Updated 2024)

At Genpact, SQL is often used for analyzing vast customer-service datasets for insights, and througout their data & analytics consulting practice. That's why Genpact asks SQL questions during interviews for Data Science and Data Engineering positions.

Thus, to help you practice for the Genpact SQL interview, we've curated 8 Genpact SQL interview questions – able to answer them all?

8 Genpact SQL Interview Questions

SQL Question 1: Identifying Genpact's Power Users

Genpact is a company that provides digital transformation services. Assume, they have a database which tracks each customer interaction with their service tools and defines power users as those who have interacted with their service tools for more than 1000 times in a month. Also, interactions are considered only when a new ticket is created by a customer. As a data analyst, write a SQL query to identify the power users for the month of June, 2022.

Example Input:

Answer:

This PostgreSQL query filters out interactions for the month of June, 2022, then groups them by and counts distinct under each . The HAVING clause is then used to filter out those users who have more than 1000 unique tickets, thus identifying the power users.

To work on a similar customer analytics SQL question where you can solve it right in the browser and have your SQL code instantly graded, try this Walmart Labs SQL Interview Question:

SQL Question 2: Compute Average Monthly Ratings

Genpact is an organization that produces several unique products. The company collects reviews from clients and uses these reviews to identify the performance of each product. Genpact is then able to measure the user satisfaction rate of their products. You are given the reviews table containing reviews from users for different products. The table contains the following columns:

• review_id: A unique identifier for each review.
• user_id: The unique identifier of the user who reviewed the product.
• submit_date: The date when the review was submitted.
• product_id: The unique identifier of the product which was reviewed.
• stars: The rating out of 5 given to the product by the user.

Write a SQL query that computes the average rating (stars) for each product for each month.

Example Input:

Answer:

Here is the SQL query which solves the problem.

Writeup:

This query utilises the EXTRACT function to obtain the month portion of the submit date. It groups by the extracted month and product_id to compute the aggregated values. The AVG function allows us to calculate the average rating for each product for each month. Using the ROUND function, we limit the average value to two decimal places. The query then orders the result by month (mth) and product_id.

This query helps us to easily identify the average satisfaction level for each product every month.

To practice another window function question on DataLemur's free online SQL code editor, solve this Amazon SQL question asked in a BI Engineer interview:

SQL Question 3: Can you explain the difference between and ?

The clause serves as a filter for the groups created by the clause, similar to how the clause filters rows. However, is applied to groups rather than individual rows.

For example, say you were a data analyst at Genpact trying to understand how sales differed by region:

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 $400,000.

SQL Question 4: Determine the average processing time of tasks

As a Data Analyst at Genpact, a global professional services company specializing in transforming business processes, your task is to find out the average processing time of tasks carried out by each employee in the last year. You have a table named in PostgreSQL that has the following data:

Example Input:

From the above data, calculate the average processing time(in hours) for each employee.

Answer:

The above PostgreSql query makes use of the AVG function to calculate the average time difference between the End_time and Start_time in hours. The GROUP BY clause is used to group these records on the basis of the Employee_ID. This gives us the average processing time per employee.

Expected Output:

To practice a very similar question try this interactive Amazon Server Utilization Time Question which is similar for calculating total time frames or this Alibaba Compressed Mean Question which is similar for finding average values.

SQL Question 5: What's a foreign key?

A foreign key is a column or group of columns in a table that refers to the primary key in another table. The foreign key constraint helps maintain referential integrity between the two tables.

Let's examine employee data from Genpact's HR database:

:

+------------+------------+------------+------------+ | employee_id| first_name | last_name | manager_id | +------------+------------+------------+------------+ | 1 | Aubrey | Graham | 3 | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter | | +------------+------------+------------+------------+

In this table, serves as the primary key and functions as a foreign key because it links to the of the employee's manager. This establishes a relationship between Genpact employees and their managers, allowing for easy querying to find an employee's manager or see which employees report to a specific manager.

The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to connect each employee to their respective department and location.

SQL Question 6: Calculate Statistics of Employee Sales

Genpact executive board wants to find details about their employees' performance, specifically their total sales, average sales, and square root of the total sales to identify any interesting patterns. They also want to round the average sales value to the nearest integer. Assume that all sales values are in USD.

Please create a SQL query to pull this information. Below are the tables for and .

Example Input:

Example Input:

Answer:

Write-up:

This query joins the employee and sales tables on the employee ID, and then for each employee, it calculates the total sales, the average sales rounded to the nearest dollar, and the square root of their total sales. For the average and square root, it makes use of the AVG and SQRT functions. By using these functions, we can gain a summary of each employee's sales history and potential insights on their performance. The ROUND function serves to make the output more readable.

To practice a very similar question try this interactive Wayfair Y-on-Y Growth Rate Question which is similar for "Using sales data to calculate growth rates" or this Amazon Highest-Grossing Items Question which is similar for "Calculating sales related metrics".

SQL Question 7: What does / SQL commands do?

The / operator is used to remove 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, while MINUS is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at Genpact should be lenient!).

Here's a PostgreSQL example of using EXCEPT to find all of Genpact's Facebook video ads with more than 10k 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 Handling Time of Tasks per Agent

You are a data scientist at Genpact, a professional services firm that provides a broad range of business process management and consulting services. Each agent handles multiple tasks daily that are logged in a tasks table. Write a SQL query to calculate the average handling time of tasks for each agent for the month of September, 2022.

Example Input:

Example Output:

Answer:

Here is a PostgreSQL query to solve this:

This query extracts the month and year from the start_timestamp to filter for tasks started in September 2022. Then ,it calculates the time difference between the end_timestamp and the start_timestamp in minutes for each task. The EXTRACT function is used to convert the time difference into epoch seconds and then divided by 60 to convert into minutes. Finally, it groups by agent_id and calculates the average handling time.

Preparing For The Genpact SQL Interview

The best way to prepare for a Genpact SQL interview is to practice, practice, practice. Beyond just solving the above Genpact SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).

Each problem on DataLemur has hints to guide you, full answers and crucially, there's an interactive SQL code editor so you can right online code up your SQL query and have it executed.

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

• Cognizant SQL questions

• Accenture SQL questions

• Infosys SQL questions

• Tata Consultancy Services SQL questions

In case your SQL query skills are weak, don't worry about diving straight into solving questions – improve your SQL foundations with this free SQL for Data Analytics course.

This tutorial covers things like different types of joins and filtering strings based on patterns – both of which show up routinely in SQL job interviews at Genpact.

Genpact Data Science Interview Tips

What Do Genpact Data Science Interviews Cover?

In addition to SQL interview questions, the other question categories to practice for the Genpact Data Science Interview are:

• Probability & Stats Questions
• Python or R Coding Questions
• Product Data Science Interview Questions
• ML Interview Questions
• Behavioral & Resume-Based Questions

How To Prepare for Genpact Data Science Interviews?

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

• 201 interview questions sourced from FAANG (FB, Apple, Amazon, Netflix, Google)
• a crash course on Product Analytics, SQL & ML
• over 900+ 5-star reviews on Amazon