SQL Practice: 10 Mixed Questions

SELECT a.name AS emp1, b.name AS emp2, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
  AND a.id < b.id
ORDER BY a.department;

Self JOIN with a.id < b.id ensures each pair is listed once (not A-B and B-A separately). Useful for pairing, team-building, and network analysis.

SELECT department
FROM employees
GROUP BY department
HAVING MIN(salary) > 40000;

If the MIN salary in a department is > 40000, then ALL employees in that department earn above 40000. Elegant use of MIN in HAVING.

SELECT category,
       SUM(revenue) AS cat_revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS pct
FROM sales
GROUP BY category
ORDER BY cat_revenue DESC;

SUM(SUM(revenue)) OVER () — the outer window SUM gives the grand total across all groups. Divide each group by grand total for percentage.

WITH latest AS (
  SELECT department, MAX(hire_date) AS latest_hire
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.hire_date
FROM employees e
JOIN latest l
  ON e.department = l.department
  AND e.hire_date = l.latest_hire;

CTE finds the latest hire date per department. Join back to employees to get the full employee record. Handles ties (multiple same-date hires) by returning all of them.

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order,
       DATEDIFF(MAX(order_date), MIN(order_date)) AS customer_lifespan_days
FROM orders
GROUP BY customer_id;

Customer lifespan = days between first and last order. Useful for cohort analysis and calculating Customer Lifetime Value (CLV).