Day 30 — Complete SQL Interview Mock Test

SELECT department,
       ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 3;

Classic GROUP BY + ORDER BY + LIMIT. If you got this in under 30 seconds, your basics are solid.

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) = 12;

COUNT(DISTINCT MONTH) = 12 means the customer has at least one order in all 12 months. Elegant one-query solution to what seems like a complex problem.

SELECT product_name,
       SUM(quantity) AS qty_sold,
       ROUND(100.0 * SUM(quantity) / SUM(SUM(quantity)) OVER (), 2) AS pct_of_total
FROM order_items
GROUP BY product_name
ORDER BY qty_sold DESC;

SUM(SUM(quantity)) OVER () is the grand total window pattern. Divide each product's total by the grand total for market share.

WITH ranks AS (
  SELECT employee_id, year,
         RANK() OVER (PARTITION BY year ORDER BY salary DESC) AS rnk
  FROM salary_history
  WHERE year IN (2023, 2024)
)
SELECT r24.employee_id,
       r23.rnk AS rank_2023,
       r24.rnk AS rank_2024
FROM ranks r24
JOIN ranks r23 ON r24.employee_id = r23.employee_id
               AND r23.year = 2023
WHERE r24.year = 2024
  AND r24.rnk != r23.rnk
ORDER BY ABS(r24.rnk - r23.rnk) DESC;

Self-join the CTE on employee_id and year to compare ranks across years. ABS() in ORDER BY sorts by magnitude of rank change (biggest movers first).

WITH stats AS (
  SELECT AVG(amount) AS mean_amt,
         STDDEV(amount) AS std_amt
  FROM transactions
)
SELECT t.*
FROM transactions t, stats
WHERE t.amount > stats.mean_amt + 3 * stats.std_amt;

Statistical outlier detection in SQL. STDDEV() is built into MySQL and PostgreSQL. This pattern is used in fraud detection, anomaly monitoring, and data quality checks.

-- Day 1 you might write:
SELECT * FROM employees WHERE department = 'sales';

-- Day 30 you write:
SELECT
  e.name,
  e.salary,
  d.department_name,
  ROUND(e.salary / AVG(e.salary) OVER (PARTITION BY e.department_id), 2)
    AS salary_vs_dept_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE LOWER(d.department_name) = 'sales'
ORDER BY e.salary DESC;

You now think about: proper JOINs, window functions for context, case-insensitive matching with LOWER(), meaningful column aliases, and adding analytical context to every query. That is what 30 days of practice does.