8 IAC Group SQL Interview Questions (Updated 2025)

At IAC Group, SQL is essential for analyzing customer behavior by tracking website interactions and purchase history to identify trends and preferences. It is also used for optimizing resource allocation in real-time production systems by dynamically adjusting server resources to match user demand, which is why SQL questions are part of IAC Group's interviews for Data Science, Analytics, and Data Engineering roles.

To help you ace the IAC Group SQL interview, here's 8 IAC Group SQL interview questions in this article.

8 IAC Group SQL Interview Questions

SQL Question 1: Calculate Average Product Review Per Month

IAC Group, being an international automotive components company, may have a website where customers can leave reviews on their products. These reviews can be used to gain insights into product quality and customer satisfaction over time.

Assume we have a reviews table where each record represents an individual product review given by a specific user at a certain date. The reviews table has the following columns:

• review_id (int) - The unique identifier of the user's review.
• user_id (int) - The unique identifier of the user.
• submit_date (date) - The date the review was submitted.
• product_id (int) - The unique identifier of the reviewed product.
• stars (int) - The star rating given by the user (1 being the lowest and 5 is the highest).

Write a SQL query to compute the average product rating (stars) for each product (product_id) on a monthly basis.

reviews Example Input:

Example Output:

Answer:

SELECT
  EXTRACT(MONTH FROM submit_date) AS mth,
  product_id AS product,
  AVG(stars) AS avg_stars
FROM
  reviews
GROUP BY
  mth,
  product
ORDER BY
  mth,
  product;

This query extracts the month from the submit_date using the EXTRACT function. Then we group by the month and the product_id to calculate the average stars for each combination of month and product. The result is sorted first by mth, then by product to make the output easier to understand.

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

SQL Question 2: 2nd Highest Salary

Given a table of IAC Group employee salary information, write a SQL query to find the 2nd highest salary at the company.

IAC Group employees Example Input:

Example Output:

Solve this question and run your code right in DataLemur's online SQL environment:

Answer:

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (
    SELECT MAX(salary)
    FROM employee
);

You can find a step-by-step solution with hints here: 2nd Highest Salary.

SQL Question 3: List a few of the ways you find duplicate records in a table in SQL.

One creative way is to use the window function ROW_NUMBER() and partition by whichever columns you are loooking for duplicates in. If any row has a row_number() more than 1, then it's a duplicate!

SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column1) as rn
FROM iac_group_table;

You could also use COUNT(DISTINCT col1) vs. COUNT(*) and see if they return different numbers, indicating the prescence of duplicates!

SQL Question 4: Database Design for Vehicle Production

The IAC Group is a leading global supplier of automotive components and systems, including interior and exterior trim. Let's imagine a scenario where they want a new system to track their vehicle production. The key entities in their daily production are Vehicles, Parts, Suppliers and Production Line. For simplicity, every Vehicle is assembled from different Parts, each provided by one Supplier, and assembled on a specific Production Line on a specific date. Please design the related database schema for this problem.

Vehicle Example Input:

Part Example Input:

Supplier Example Input:

Using this schema, write a PostgreSQL query to fetch the number of parts supplied by each supplier for a given vehicle.

Answer:

SELECT S.SupplierName, COUNT(P.PartId) as NumberOfParts
FROM Supplier S
JOIN Part P ON S.SupplierId = P.SupplierId
WHERE P.VehicleId = :VehicleId 
GROUP BY S.SupplierName

This query joins the Part table with the Supplier table on SupplierId, filters by a given VehicleId, then groups by SupplierName to count the number of parts each supplier provided for the vehicle. Replace :VehicleId with the actual VehicleId.

SQL Question 5: Can you describe the meaning of database normalization in layman's terms?

To normalize a database, tables are divided into smaller, more specialized ones and relationships between them are defined via primary and foreign keys. This minimizes redundancy, making the database more flexible, scalable, and easier to maintain. Normalization also helps to ensure the accuracy of the data by reducing the likelihood of inconsistencies and errors.

SQL Question 6: Average Number of Employees per Department

IAC Group has several offices globally with various departments. Each department employs a certain amount of employees. You are asked to find the average number of employees per department for the entire company using the database.

Here's some example data:

employees Example Input:

departments Example Input:

Answer:

Here's the SQL query that would find the average number of employees per department:

SELECT
    departments.department_id,
    departments.department_name,
    AVG(cnt) AS avg_emp_per_department
FROM
    departments
JOIN 
    (SELECT
         employee.department_id, 
         COUNT(*) AS cnt
     FROM 
         employee
     GROUP BY 
         employee.department_id) AS subquery
ON
    departments.department_id = subquery.department_id
GROUP BY 
    departments.department_id,
    departments.department_name;

This Postgres SQL query first groups by department_id in the subquery and computes the number of employees per department (cnt). It then joins this subquery with the departments table on department_id, to get the department names. Finally, it computes the average number of employees per department by taking the average of cnt.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating average per category or this Microsoft Supercloud Customer Question which is similar for counting group of entities.

SQL Question 7: What is the difference between a correlated subquery and non-correlated subquery?

A correlated sub-query is one that relies on the outer query and cannot be run independently. It uses the outer query to filter or transform data by referencing a column from it, while the outer query uses the results of the inner query. On the other hand, a non-correlated sub-query is not linked to the outer query and can be run on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query.

An example correlated sub-query:

SELECT name, salary
FROM iac_group_employees e1
WHERE salary > (SELECT AVG(salary) FROM  iac_group_e2 WHERE e1.department = e2.department);

This correlated subquery retrieves the names and salaries of IAC Group employees who make more than the average salary for their department. The subquery references the department column in the main query's FROM clause (e1.department) and uses it to filter the rows of the subquery's FROM clause (e2.department).

An example non-correlated sub-query:

SELECT name, salary
FROM iac_group_employees
WHERE salary > (SELECT AVG(salary) FROM iac_group_employees WHERE department = 'Data Analytics');

This non-correlated subquery retrieves the names and salaries of IAC Group employees who make more than the average salary for the Data Analytics department (which honestly should be very few people since Data Analysts are awesome).

Anways, the subquery is independent of the main query and can be considered a standalone query. Its output (the average salary for the Data Analytics department) is then used in the main query to filter the rows of the iac_group_employees table.

SQL Question 8: Find Customers with Email Providers

IAC Group is interested in analyzing customer records to understand which email providers their customers are mostly using.

They would like to know how many of their current customers are using Gmail, Yahoo, and Hotmail specifically. The email information is stored in the email_address column in the customers table.

Write a SQL query that counts the number of customers with email addresses ending in "@gmail.com", "@yahoo.com" and "@hotmail.com".

customers Example Input:

Example Output:

Answer:

SELECT CASE 
    WHEN email_address LIKE '%@gmail.com' THEN 'gmail.com'
    WHEN email_address LIKE '%@yahoo.com' THEN 'yahoo.com'
    WHEN email_address LIKE '%@hotmail.com' THEN 'hotmail.com' 
    ELSE 'Others'
    END AS email_provider,
    COUNT(*) AS customer_count
FROM customers
WHERE email_address LIKE '%@gmail.com' OR email_address LIKE '%@yahoo.com' OR email_address LIKE '%@hotmail.com'
GROUP BY email_provider;

• This query uses the CASE keyword to create a new email_provider column, where we determine what provider a customer is using based on their email address.
• After that, it counts the number of customers by email_provider, grouping by the email_provider.
• The WHERE clause filters out any customers not using Gmail, Yahoo or Hotmail.

How To Prepare for the IAC Group SQL Interview

The best way to prepare for a IAC Group SQL interview is to practice, practice, practice. In addition to solving the above IAC Group SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Google, Facebook, Microsoft and Amazon.

Each problem on DataLemur has hints to guide you, step-by-step solutions and best of all, there is an online SQL code editor so you can right online code up your SQL query and have it checked.

To prep for the IAC Group SQL interview you can also be helpful to practice interview questions from other tech companies like:

• Facebook SQL questions
• Snap SQL questions
• DoorDash SQL questions
• Adobe SQL questions

Learn about IAC Group's commitment to innovationand staying ahead of the tech curve!

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

This tutorial covers things like window functions like RANK() and ROW_NUMBER() and filtering strings based on patterns – both of these pop up often during SQL interviews at IAC Group.

IAC Group Data Science Interview Tips

What Do IAC Group Data Science Interviews Cover?

Besides SQL interview questions, the other question categories to prepare for the IAC Group Data Science Interview include:

• Probability & Stats Questions
• Python Pandas or R Coding Questions
• Product Analytics Questions
• Machine Learning Questions
• Behavioral Questions centered on IAC Group values & principles

How To Prepare for IAC Group Data Science Interviews?

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

• 201 interview questions taken from Facebook, Google, & Amazon
• a crash course on SQL, AB Testing & ML
• over 1000+ reviews on Amazon & 4.5-star rating

Don't forget about the behavioral interview – prep for it using this list of common Data Scientist behavioral interview questions.