Table: employees

Solution:

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

Table: employees

Solution:

SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Table: employees

Solution:

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

WITH CTE AS (
  SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY name, salary ORDER BY id) AS row_num
  FROM employees
)
DELETE FROM employees
WHERE id IN (
  SELECT id
  FROM CTE
  WHERE row_num > 1
);

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2; -- Replace 2 with (N-1) for the Nth highest

Table: employees

Solution:

SELECT name, hire_date
FROM employees
WHERE hire_date >= DATE_ADD(CURRENT_DATE, INTERVAL -6 MONTH);

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
) ranked
WHERE rank <= 3;

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Table: employees

Solution:

SELECT manager_id, COUNT(*) AS employee_count
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING COUNT(*) >= 3;

Table: transactions

Solution:

WITH all_dates AS (
  SELECT DATE_ADD('2024-10-01', INTERVAL n DAY) AS transaction_date
  FROM generate_series(0, DATEDIFF(CURRENT_DATE, '2024-10-01')) n
)
SELECT d.transaction_date
FROM all_dates d
LEFT JOIN transactions t ON d.transaction_date = t.transaction_date
WHERE t.transaction_date IS NULL;

Table: purchases

Solution:

SELECT customer_id, MAX(purchase_date) AS last_purchase_date
FROM purchases
GROUP BY customer_id;

Tables:

• products
  | id | name |
  |----|---------|
  | 1 | Laptop |
  | 2 | Phone |

• sales
  | product_id | quantity |
  |------------|----------|
  | 1 | 5 |

Solution:

SELECT p.name
FROM products p
LEFT JOIN sales s ON p.id = s.product_id
WHERE s.product_id IS NULL;

Table: sales

Solution:

SELECT id, amount,
       SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

Tables:

• employees
  | id | name |
  |----|------|
  | 1 | Bob |

• employee_departments
  | employee_id | department_id |
  |-------------|---------------|
  | 1 | 1 |
  | 1 | 2 |

• departments
  | id | name |
  |----|------|
  | 1 | HR |
  | 2 | IT |

Solution:

SELECT e.name
FROM employees e
JOIN employee_departments ed ON e.id = ed.employee_id
GROUP BY e.id, e.name
HAVING COUNT(DISTINCT ed.department_id) = (SELECT COUNT(*) FROM departments);

Tables:

• table_a
  | id | name |
  |----|---------|
  | 1 | Alice |
  | 2 | Bob |

• table_b
  | id | name |
  |----|---------|
  | 2 | Bob |
  | 3 | Charlie |

Solution:

SELECT a.id, a.name
FROM table_a a
INNER JOIN table_b b ON a.id = b.id AND a.name = b.name;

SELECT a.id, a.name
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND a.name = b.name
WHERE b.id IS NULL;

Table: purchases

Solution:

SELECT customer_id,
       MIN(purchase_date) AS first_purchase,
       MAX(purchase_date) AS last_purchase
FROM purchases
GROUP BY customer_id;

Table: transactions

Solution:

WITH ranked_dates AS (
  SELECT transaction_date,
         ROW_NUMBER() OVER (ORDER BY transaction_date) - DATEDIFF(transaction_date, '2024-01-01') AS grp
  FROM transactions
)
SELECT MIN(transaction_date) AS start_date, MAX(transaction_date) AS end_date
FROM ranked_dates
GROUP BY grp
ORDER BY (DATEDIFF(MAX(transaction_date), MIN(transaction_date))) DESC
LIMIT 1;

Table: sales

Solution:

SELECT product_id, quantity
FROM sales
ORDER BY quantity DESC
LIMIT 3;

SELECT product_id, quantity,
       RANK() OVER (ORDER BY quantity DESC) AS rank
FROM sales;