Master SQL Interview Questions: 2nd Highest Salary, Login Gaps & Channel Analysis

Problem 1: Find the employee with the second‑highest salary in each department

The employee table contains employee_id, employee_name, employee_salary, and department. The department table maps department IDs to names.

Data import:

DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
    employee_id VARCHAR(8),
    employee_name VARCHAR(8),
    employee_salary INT(8),
    department VARCHAR(8)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO employee (employee_id,employee_name,employee_salary,department) VALUES
('a001','Bob',7000,'b1'),
('a002','Jack',9000,'b1'),
('a003','Alice',8000,'b2'),
('a004','Ben',5000,'b2'),
('a005','Candy',4000,'b2'),
('a006','Allen',5000,'b2'),
('a007','Linda',10000,'b3');

DROP TABLE IF EXISTS department;
CREATE TABLE department(
    department_id VARCHAR(8),
    department_name VARCHAR(8)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO department (department_id,department_name) VALUES
('b1','Sales'),
('b2','IT'),
('b3','Product');

Solution query using a window function and a join:

SELECT a.employee_id,
       a.employee_name,
       a.employee_salary,
       b.department_id
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY department ORDER BY employee_salary DESC) AS ranking
    FROM employee
) AS a
INNER JOIN department AS b
ON a.department = b.department_id
WHERE a.ranking = 2;

Key points: window functions, subqueries, multi‑table joins.

Problem 2: Count login intervals shorter than 5 days per user

The login_info table records user_id and login_time.

Data import:

DROP TABLE IF EXISTS login_info;
CREATE TABLE login_info(
    user_id VARCHAR(8),
    login_time DATE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO login_info (user_id,login_time) VALUES
('a001','2021-01-01'),
('b001','2021-01-01'),
('a001','2021-01-03'),
('a001','2021-01-06'),
('a001','2021-01-07'),
('b001','2021-01-07'),
('a001','2021-01-08'),
('a001','2021-01-09'),
('b001','2021-01-09'),
('b001','2021-01-10'),
('b001','2021-01-15'),
('a001','2021-01-16'),
('a001','2021-01-18'),
('a001','2021-01-19'),
('b001','2021-01-20'),
('a001','2021-01-23');

Solution query using LEAD() to compute the next login date and TIMESTAMPDIFF to filter intervals:

SELECT a.user_id,
       COUNT(*) AS num
FROM (
    SELECT user_id,
           login_time,
           LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_time
    FROM login_info
) AS a
WHERE TIMESTAMPDIFF(DAY, login_time, next_login_time) < 5
GROUP BY user_id;

Key points: window functions, time functions, grouping aggregation.

Problem 3: Daily purchase channel analysis

The purchase_channel table records user purchases on app and web channels.

Data import:

DROP TABLE IF EXISTS purchase_channel;
CREATE TABLE purchase_channel(
    user_id VARCHAR(8),
    channel VARCHAR(8),
    purchase_date DATE,
    purchase_amount INT(8)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO purchase_channel (user_id,channel,purchase_date,purchase_amount) VALUES
('a001','app','2021-03-14',200),
('a001','web','2021-03-14',100),
('a002','app','2021-03-14',400),
('a001','web','2021-03-15',3000),
('a002','app','2021-03-15',900),
('a003','app','2021-03-15',1000);

Goal: For each day, report the number of distinct users and total purchase amount for three channel categories – app, web, and both (users who bought on both channels). Even if a day‑channel combination has no data, it should appear.

Solution (first version using UNION):

SELECT purchase_date,
       channel,
       SUM(sum_amount) sum_amount,
       SUM(total_users) total_users
FROM (
    SELECT purchase_date,
           MIN(channel) channel,
           SUM(purchase_amount) sum_amount,
           COUNT(DISTINCT user_id) total_users
    FROM purchase_channel
    GROUP BY purchase_date, user_id
    HAVING COUNT(DISTINCT channel) = 1
    UNION
    SELECT purchase_date,
           'both' channel,
           SUM(purchase_amount) sum_amount,
           COUNT(DISTINCT user_id) total_users
    FROM purchase_channel
    GROUP BY purchase_date, user_id
    HAVING COUNT(DISTINCT channel) > 1
) c
GROUP BY purchase_date, channel;

Solution (second version with LEFT JOIN to ensure all date‑channel rows):

SELECT t1.purchase_date,
       t1.channel,
       t2.sum_amount,
       t2.total_users
FROM (
    SELECT DISTINCT a.purchase_date,
           b.channel
    FROM purchase_channel a,
         (SELECT 'app' AS channel UNION SELECT 'web' UNION SELECT 'both') b
) t1
LEFT JOIN (
    SELECT purchase_date,
           channel,
           SUM(sum_amount) sum_amount,
           SUM(total_users) total_users
    FROM (
        SELECT purchase_date,
               MIN(channel) channel,
               SUM(purchase_amount) sum_amount,
               COUNT(DISTINCT user_id) total_users
        FROM purchase_channel
        GROUP BY purchase_date, user_id
        HAVING COUNT(DISTINCT channel) = 1
        UNION
        SELECT purchase_date,
               'both' channel,
               SUM(purchase_amount) sum_amount,
               COUNT(DISTINCT user_id) total_users
        FROM purchase_channel
        GROUP BY purchase_date, user_id
        HAVING COUNT(DISTINCT channel) > 1
    ) c
    GROUP BY purchase_date, channel
) t2
ON t1.purchase_date = t2.purchase_date AND t1.channel = t2.channel;

Key points: UNION, grouping aggregation, data deduplication.