SQL Interview Questions Series#1

Question: Write an SQL query to find duplicate records in a table named Employees. Consider a record to be duplicate if it has the same values in the FirstName, LastName, and Email columns. Your query should list the duplicated FirstName, LastName, and Email, along with the count of how many times each combination appears in the table.

Table Schema:

• employee_id (int)

• first_name (varchar)

• last_name (varchar)

• email (varchar)

Example Data:

CREATE OR REPLACE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com'),
    (3, 'John', 'Doe', 'john.doe@example.com'),
    (4, 'Emily', 'Davis', 'emily.davis@example.com'),
    (5, 'John', 'Doe', 'john.doe@example.com');

This query will insert the provided example data into the Employees table. If you have any more questions or need further assistance, feel free to ask!

Expected Output:

Solution:

SELECT 
    first_name, 
    last_name, 
    email, 
    COUNT(*) AS duplicate_count
FROM 
    employees
GROUP BY 
    first_name, 
    last_name, 
    email
HAVING 
    COUNT(*) > 1;

Github:
https://github.com/vipinputhanveetil/sql-interview-questions-series/blob/main/sql_interview_question_1.sql

Solution Details:
This query groups the rows by first_name, last_name, and email, and then counts the occurrences of each group. The HAVING clause filters out groups that do not have duplicates by ensuring that the count is greater than 1.

Stay tuned for more articles in this SQL Interview Questions Series as we delve deeper into a variety of questions and scenarios asked by top companies!