SQL Tips That Get You Hired

-- Bad — unclear output:
SELECT d.name, COUNT(*), AVG(e.salary)
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY d.name;

-- Good — self-documenting:
SELECT d.name AS department,
       COUNT(*) AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
ORDER BY avg_salary DESC;

Aliases make output readable for business users. Interviewers notice when you alias well — it shows professionalism and real-world experience.

-- Instead of one massive nested query, break it down:
WITH
active_customers AS (
  SELECT id FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, SUM(amount) AS spend
  FROM orders
  WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  GROUP BY customer_id
)
SELECT c.id, ro.spend
FROM active_customers c
JOIN recent_orders ro ON c.id = ro.customer_id
ORDER BY ro.spend DESC;

Named CTEs = self-documenting SQL. Interviewers can follow your logic. Nested subqueries work but are hard to read and debug. Always prefer CTEs for multi-step logic.

-- Ask yourself:
-- Do I need ALL rows from left table? → LEFT JOIN
-- Only matching rows from both? → INNER JOIN
-- All rows regardless of match? → FULL OUTER JOIN

-- Common mistake: using INNER JOIN when LEFT JOIN is needed:
-- "Show all customers and their order count"
-- WRONG (loses customers with 0 orders):
SELECT c.name, COUNT(o.id)
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- CORRECT:
SELECT c.name, COUNT(o.id)
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

The most common SQL mistake in interviews is choosing the wrong JOIN type. Pause and think: do I want to lose rows that don't match? If no, use LEFT JOIN.

-- Step 1: Repeat the question to confirm understanding
-- Step 2: Think about what the output should look like
-- Step 3: Identify the tables you need
-- Step 4: Decide the JOIN type and aggregations
-- Step 5: Write the query in logical order:
--   FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
-- Step 6: Walk through with sample data mentally
-- Step 7: Check edge cases (NULLs, empty groups, ties)

A structured approach matters more than instantly knowing the answer. Interviewers at MNCs in Delhi NCR value problem-solving process — talking through your logic shows seniority.

-- Checklist before submitting a query:
-- ✓ Does SELECT alias all computed columns?
-- ✓ Are all non-aggregate SELECT columns in GROUP BY?
-- ✓ Is the JOIN type correct (INNER vs LEFT)?
-- ✓ Does WHERE filter before or after grouping? (Use HAVING for aggregates)
-- ✓ Are NULLs handled? (Use COALESCE, IS NULL)
-- ✓ Does LIMIT make sense? (Are you showing enough rows?)
-- ✓ Would the query work on an empty table?
-- ✓ Is ORDER BY specified where output order matters?

A mental checklist before submitting prevents 90% of SQL errors. Running through this in interviews also buys you thinking time and demonstrates thoroughness.