👩‍💼 Business Analyst (BA)

(Beginner-friendly, example-driven Q&A for Top Tech Company Interviews)

❓ Q1: Retrieve all customer records located in a specific city.

🧩 Problem Statement

Return every customer row where the customer lives in a given city (e.g., Mumbai). This is basic selection + filtering.

💾 Step 1: Assume Example Tables

customers — list of customers.

Columns:

• customer_id: unique id.
• city: used to filter.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Tested concept: SELECT + WHERE filtering. We simply select rows where city = 'Mumbai'.

🏗️ SQL Query

SELECT customer_id, name, email, city, joined_date
FROM customers
WHERE city = 'Mumbai';

🔍 Explanation

• SELECT chooses columns.
• WHERE city = 'Mumbai' filters only customers in Mumbai.

🧾 Step 3: Expected Output

This shows both customers living in Mumbai.

⚙️ Step 4: Why This Query Works

A direct equality filter returns only matching rows; it’s efficient with an index on city.

⚡ Step 5: Common Mistakes

• Forgetting quotes around string values.
• Using LIKE when exact match is intended (or vice versa).
• Case-sensitivity issues depending on DB collation.

🧠 Step 6: Alternate Approach (Optional)

-- Using parameter / placeholder (safer in application code)
SELECT *
FROM customers
WHERE city = ?;

Use this in prepared statements to avoid SQL injection.

🎯 Interview Tip

Explain that you would parameterize the city in production code and consider indexing city if queries are frequent.

🔍 Interviewer’s Analysis

• Concept Tested: WHERE filter / selective projection
• Difficulty: Beginner
• Time Complexity: O(n) scan (or O(log n) if indexed)
• Follow-Up Prompts: Indexing, case-sensitivity, parameterization.

🪄 End of Q1 — ready for the next question.

❓ Q2: Find employees whose names start with “A”.

🧩 Problem Statement

Return employee records where name begins with the letter A.

💾 Step 1: Assume Example Tables

employees

Columns:

• name: text to search with pattern.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Tested concept: pattern matching (LIKE) or ILIKE for case-insensitive checks.

🏗️ SQL Query

SELECT emp_id, name, dept_id, salary, hired_date
FROM employees
WHERE name LIKE 'A%';

(If your DB supports case-insensitive ILIKE and you want to catch lower-case a: use WHERE name ILIKE 'a%'.)

🔍 Explanation

• LIKE 'A%' matches strings starting with A. % means any following characters.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Pattern matching selects rows where the first character equals A. Use ILIKE for case-insensitive behavior if needed.

⚡ Step 5: Common Mistakes

• Forgetting %.
• Using = (exact match) instead of LIKE.
• Not considering case sensitivity.

🧠 Step 6: Alternate Approach (Optional)

SELECT emp_id, name
FROM employees
WHERE SUBSTR(name,1,1) = 'A';

Less flexible and may be slower than LIKE.

🎯 Interview Tip

Mention whether collation is case-sensitive in your DB and prefer ILIKE or LOWER(name) LIKE 'a%' accordingly.

🪄 End of Q2.

❓ Q3: Display unique department names from the employee table.

🧩 Problem Statement

Return distinct department names listed in employees (or a departments table). Use DISTINCT.

💾 Step 1: Assume Example Tables

employees

Columns:

• dept_name: department label (could also be a foreign key to departments).

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: SELECT DISTINCT to remove duplicates.

🏗️ SQL Query

SELECT DISTINCT dept_name
FROM employees;

🔍 Explanation

DISTINCT collapses duplicate department values so each name appears once.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

DISTINCT deduplicates values after selection.

⚡ Step 5: Common Mistakes

• Selecting multiple columns with DISTINCT when only one is desired.
• Not trimming whitespace/case differences leading to duplicates.

🧠 Step 6: Alternate Approach (Optional)

SELECT dept_name
FROM employees
GROUP BY dept_name;

Group by also returns unique dept names.

🎯 Interview Tip

If department IDs exist, prefer selecting ID + name to avoid ambiguity.

🪄 End of Q3.

❓ Q4: Fetch employees sorted by their joining date (latest first).

🧩 Problem Statement

Return employees ordered by joined_date descending so newest hires appear first.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: ORDER BY with DESC.

🏗️ SQL Query

SELECT emp_id, name, joined_date
FROM employees
ORDER BY joined_date DESC;

🔍 Explanation

ORDER BY joined_date DESC sorts newest first.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Ordering controls presentation; databases optimize sorting but large sorts can be costly—consider indexing if needed.

⚡ Step 5: Common Mistakes

• Forgetting DESC and getting oldest first.
• Sorting on string date instead of date datatype.

🧠 Step 6: Alternate Approach (Optional)

-- Show top 10 newest hires
SELECT emp_id, name, joined_date
FROM employees
ORDER BY joined_date DESC
LIMIT 10;

🎯 Interview Tip

State whether joined_date is stored as DATE/TIMESTAMP to avoid string-sorting issues.

🪄 End of Q4.

❓ Q5: Get the top 10 highest-paid employees.

🧩 Problem Statement

Return the 10 employees with the largest salary. If tie-handling required, define an order.

💾 Step 1: Assume Example Tables

employees

(Assume more rows beyond 5 in real table.)

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: ORDER BY + LIMIT (or window functions for ties).

🏗️ SQL Query

SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

(If you want deterministic tie-break: ORDER BY salary DESC, emp_id ASC)

🔍 Explanation

Sort by salary descending, then take the first 10 rows.

🧾 Step 3: Expected Output (top few shown)

⚙️ Step 4: Why This Query Works

Ordering then limiting fetches top rows efficiently; use index on salary to improve speed.

⚡ Step 5: Common Mistakes

• Using TOP syntax in non-supporting DBs (use LIMIT or FETCH FIRST).
• Not specifying tie-breaker for reproducibility.

🧠 Step 6: Alternate Approach (Optional)

-- Using ROW_NUMBER to handle ties / pagination
WITH ranked AS (
  SELECT emp_id, name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn
  FROM employees
)
SELECT emp_id, name, salary
FROM ranked
WHERE rn <= 10;

🎯 Interview Tip

Discuss tie-handling and how pagination will be implemented for large result sets.

🪄 End of Q5.

❓ Q6: Retrieve all transactions made after January 1, 2024.

🧩 Problem Statement

Return transactions with transaction_date strictly after 2024-01-01.

💾 Step 1: Assume Example Tables

transactions

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: date comparison with WHERE.

🏗️ SQL Query

SELECT tx_id, customer_id, amount, transaction_date
FROM transactions
WHERE transaction_date > '2024-01-01';

(If datetime column: '2024-01-01 00:00:00' as needed.)

🔍 Explanation

Rows with date strictly later than Jan 1, 2024 are returned.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Date comparisons use chronological ordering; ensure correct datatype.

⚡ Step 5: Common Mistakes

• Using >= '2024-01-01' if you want strictly after.
• Comparing strings if dates stored as text with inconsistent format.

🧠 Step 6: Alternate Approach (Optional)

-- Using DATE() conversion (if transaction_date includes time)
SELECT *
FROM transactions
WHERE DATE(transaction_date) > '2024-01-01';

🎯 Interview Tip

Clarify whether “after” includes the date (>=) or strictly greater (>).

🪄 End of Q6.

❓ Q7: Find the total salary expense for each department.

🧩 Problem Statement

Group employees by department and sum salaries to get department-level salary expense.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: GROUP BY + aggregate SUM.

🏗️ SQL Query

SELECT dept_id, dept_name, SUM(salary) AS total_salary_expense
FROM employees
GROUP BY dept_id, dept_name;

🔍 Explanation

Group rows by department and compute sum of salary per group.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

GROUP BY collapses rows into department buckets; SUM adds salaries within each bucket.

⚡ Step 5: Common Mistakes

• Omitting dept_name from GROUP BY when selected.
• Using COUNT instead of SUM.

🧠 Step 6: Alternate Approach (Optional)

-- With join to a departments table
SELECT d.dept_id, d.dept_name, COALESCE(SUM(e.salary),0) AS total_salary
FROM departments d
LEFT JOIN employees e
  ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

This includes departments with zero employees.

🎯 Interview Tip

Mention COALESCE to show departments with no employees return 0 instead of NULL.

🪄 End of Q7.

❓ Q8: Count how many employees belong to each department.

🧩 Problem Statement

For each department, count employees (i.e., headcount per dept).

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: COUNT(*) with GROUP BY. Decide whether to include NULL dept.

🏗️ SQL Query

SELECT dept_id, dept_name, COUNT(*) AS employee_count
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id, dept_name;

(To include NULLs as a separate group, remove the WHERE clause.)

🔍 Explanation

Group by dept and count rows per group.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

COUNT(*) counts rows in each department group.

⚡ Step 5: Common Mistakes

• Using COUNT(dept_id) which ignores NULLs (sometimes desired).
• Forgetting GROUP BY for non-aggregated columns.

🧠 Step 6: Alternate Approach (Optional)

-- Include departments with zero employees using departments table
SELECT d.dept_id, d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

🎯 Interview Tip

Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column).

🪄 End of Q8.

❓ Q9: Get departments with more than 5 employees.

🧩 Problem Statement

Find departments whose employee count exceeds 5.

💾 Step 1: Assume Example Tables

employees

(Only showing relevant counts.)

We will compute counts from employees.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: GROUP BY + HAVING for filtering groups.

🏗️ SQL Query

SELECT dept_id, dept_name, COUNT(*) AS employee_count
FROM employees
GROUP BY dept_id, dept_name
HAVING COUNT(*) > 5;

🔍 Explanation

HAVING filters aggregated groups by their computed count.

🧾 Step 3: Expected Output

(Example departments meeting the criterion.)

⚙️ Step 4: Why This Query Works

WHERE cannot filter aggregated results; HAVING can.

⚡ Step 5: Common Mistakes

• Using WHERE COUNT(*) > 5 (invalid).
• Forgetting GROUP BY on selected non-aggregated columns.

🧠 Step 6: Alternate Approach (Optional)

-- Using subquery
SELECT dept_id, dept_name, employee_count
FROM (
  SELECT dept_id, dept_name, COUNT(*) AS employee_count
  FROM employees
  GROUP BY dept_id, dept_name
) t
WHERE employee_count > 5;

🎯 Interview Tip

Explain HAVING vs WHERE clearly before coding.

🪄 End of Q9.

❓ Q10: Find the average salary per job title.

🧩 Problem Statement

Group by job_title and compute average salary for each title.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: AVG() aggregate with GROUP BY.

🏗️ SQL Query

SELECT job_title, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY job_title;

🔍 Explanation

AVG(salary) calculates the mean salary per job title. ROUND formats to 2 decimals.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Standard aggregation by category returns an average per job title.

⚡ Step 5: Common Mistakes

• Using integer division in some DBs (hence ROUND).
• Selecting non-aggregated columns without grouping.

🧠 Step 6: Alternate Approach (Optional)

-- Exclude zero or NULL salaries
SELECT job_title, AVG(salary) AS avg_salary
FROM employees
WHERE salary IS NOT NULL AND salary > 0
GROUP BY job_title;

🎯 Interview Tip

Clarify how to treat NULL salaries and outliers before computing averages.

🪄 End of Q10.

❓ Q11: Retrieve total sales per region.

🧩 Problem Statement

Group transactions by region and sum amount to compute total sales per region.

💾 Step 1: Assume Example Tables

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: GROUP BY region + SUM(amount).

🏗️ SQL Query

SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region;

🔍 Explanation

Sums order amounts grouped by region.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Aggregates amounts per region to produce regional totals.

⚡ Step 5: Common Mistakes

• Forgetting GROUP BY.
• Not handling NULL region values (may want to group them separately).

🧠 Step 6: Alternate Approach (Optional)

SELECT COALESCE(region, 'Unknown') AS region, SUM(amount) AS total_sales
FROM orders
GROUP BY COALESCE(region, 'Unknown');

🎯 Interview Tip

Discuss currency and timezone normalization if amounts come from different locales.

🪄 End of Q11.

❓ Q12: Count the total number of customers who placed at least one order.

🧩 Problem Statement

Count unique customers appearing in orders (customers with >=1 order).

💾 Step 1: Assume Example Tables

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concept: COUNT(DISTINCT customer_id).

🏗️ SQL Query

SELECT COUNT(DISTINCT customer_id) AS customers_with_orders
FROM orders;

🔍 Explanation

DISTINCT ensures each customer counted once, even with multiple orders.

🧾 Step 3: Expected Output

(Customers: 1, 2, and 4)

⚙️ Step 4: Why This Query Works

Counting distinct IDs gives unique customer count.

⚡ Step 5: Common Mistakes

• Using COUNT(*) which counts rows (orders), not unique customers.
• Counting distinct on wrong column.

🧠 Step 6: Alternate Approach (Optional)

SELECT COUNT(*) FROM (SELECT 1 FROM orders GROUP BY customer_id) t;

Equivalent but less direct.

🎯 Interview Tip

If large tables, mention potential cost of DISTINCT and ways to optimize (indexes, pre-aggregated tables).

🪄 End of Q12.

❓ Q13: Find customers who made transactions above $5,000 more than once.

🧩 Problem Statement

Find customers who have at least two transactions where amount > 5000 (i.e., count of such transactions per customer > 1).

💾 Step 1: Assume Example Tables

transactions

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: filtering + grouping + HAVING.

🏗️ SQL Query

SELECT customer_id, COUNT(*) AS high_value_tx_count
FROM transactions
WHERE amount > 5000
GROUP BY customer_id
HAVING COUNT(*) > 1;

🔍 Explanation

Filter transactions > 5000, then count per customer, and keep those with count > 1.

🧾 Step 3: Expected Output

(Customer 1 has two high-value transactions; customer 2 has only one >5000.)

⚙️ Step 4: Why This Query Works

WHERE narrows rows, GROUP BY groups per customer, HAVING filters groups by count.

⚡ Step 5: Common Mistakes

• Putting amount > 5000 in HAVING without filtering first (works but less efficient).
• Forgetting GROUP BY.

🧠 Step 6: Alternate Approach (Optional)

SELECT customer_id
FROM (
  SELECT customer_id,
         SUM(CASE WHEN amount > 5000 THEN 1 ELSE 0 END) AS cnt
  FROM transactions
  GROUP BY customer_id
) t
WHERE cnt > 1;

Useful when counting multiple conditional buckets.

🎯 Interview Tip

Discuss performance trade-offs when transactions is large: an index on amount or partitioning could help.

🪄 End of Q13.

❓ Q14: Identify the most profitable regions by total transaction amount.

🧩 Problem Statement

Compute total transaction amount per region and rank regions by descending totals to find top performers.

💾 Step 1: Assume Example Tables

transactions

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: aggregation + ordering + optional ranking.

🏗️ SQL Query

SELECT region, SUM(amount) AS total_amount
FROM transactions
GROUP BY region
ORDER BY total_amount DESC;

(To get top N regions: add LIMIT N.)

🔍 Explanation

Sums amounts per region and sorts highest to lowest.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Group & sum reveals aggregated revenue per region; ORDER BY shows rank.

⚡ Step 5: Common Mistakes

• Forgetting GROUP BY.
• Not handling NULL regions.

🧠 Step 6: Alternate Approach (Optional)

-- Add rank column
SELECT region, total_amount,
       RANK() OVER (ORDER BY total_amount DESC) AS region_rank
FROM (
  SELECT region, SUM(amount) AS total_amount
  FROM transactions
  GROUP BY region
) t
ORDER BY total_amount DESC;

🎯 Interview Tip

Discuss whether revenue or profit should be used; profit requires cost data.

🪄 End of Q14.

❓ Q15: Find customers who have not purchased anything in the last 6 months.

🧩 Problem Statement

Return customers with no orders/transactions in the past 6 months (relative to a reference date). For this example assume reference date = 2024-10-31 (today-like).

💾 Step 1: Assume Example Tables

customers

orders

(Reference date for “last 6 months”: 2024-10-31. Six months before: 2024-04-30.)

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Concepts: anti-join (LEFT JOIN ... WHERE ... IS NULL) or NOT EXISTS. Date arithmetic needed.

🏗️ SQL Query (preferred NOT EXISTS)

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date > DATE '2024-04-30'  -- 6 months before 2024-10-31
);

(If your DB has CURRENT_DATE, compute CURRENT_DATE - INTERVAL '6 months'.)

🔍 Explanation

For each customer, check if any order exists after the cutoff date. If none, return the customer.

🧾 Step 3: Expected Output

(Sunil has no orders after 2024-04-30.)

⚙️ Step 4: Why This Query Works

NOT EXISTS efficiently filters customers with zero qualifying orders.

⚡ Step 5: Common Mistakes

• Using LEFT JOIN incorrectly and forgetting WHERE o.order_id IS NULL.
• Wrong date arithmetic or inclusive/exclusive boundary confusion.

🧠 Step 6: Alternate Approach (Optional)

-- LEFT JOIN method
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
  AND o.order_date > DATE '2024-04-30'
WHERE o.order_id IS NULL;

🎯 Interview Tip

Clarify the reference date and whether “6 months” is exact calendar months or 180 days.

🪄 End of Q15.

❓ Q16: Calculate ARPU (Average Revenue Per User).

🧩 Problem Statement

ARPU = (total revenue in period) / (number of active users in period). For simplicity, compute for all-time: total transaction amount divided by number of unique customers who made at least one transaction.

💾 Step 1: Assume Example Tables

transactions

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Compute aggregate sum and distinct count, then divide. Use NULLIF to prevent division by zero.

🏗️ SQL Query

SELECT
  ROUND(SUM(amount) / NULLIF(COUNT(DISTINCT customer_id), 0), 2) AS arpu
FROM transactions;

🔍 Explanation

• SUM(amount) total revenue.
• COUNT(DISTINCT customer_id) active users.
• NULLIF(...,0) avoids division by zero if no customers.

🧾 Step 3: Expected Output

(Total revenue = 6500; unique customers = 3 → 6500/3 ≈ 2166.67)

⚙️ Step 4: Why This Query Works

Direct arithmetic on aggregates yields the average revenue per active user.

⚡ Step 5: Common Mistakes

• Dividing by COUNT(*) (orders) instead of distinct users.
• Not handling zero users.

🧠 Step 6: Alternate Approach (Optional)

Compute ARPU for a specific period using date filters:

SELECT ROUND(SUM(amount) / NULLIF(COUNT(DISTINCT customer_id), 0), 2) AS arpu
FROM transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-09-30';

🎯 Interview Tip

Define active user (e.g., made at least one purchase in period) before coding.

🪄 End of Q16.

❓ Q17: Calculate the monthly customer retention rate.

🧩 Problem Statement

Monthly retention rate = (customers active this month who were also active previous month) / (customers active previous month). We’ll compute for two months example: previous month = Sept 2024, current month = Oct 2024.

💾 Step 1: Assume Example Tables

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

We need sets: users active in previous month (Sept) and current month (Oct), then compute intersection size / previous month size.

🏗️ SQL Query

WITH prev_month AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date BETWEEN '2024-09-01' AND '2024-09-30'
),
curr_month AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date BETWEEN '2024-10-01' AND '2024-10-31'
),
counts AS (
  SELECT
    (SELECT COUNT(*) FROM prev_month) AS prev_count,
    (SELECT COUNT(*) FROM curr_month) AS curr_count,
    (SELECT COUNT(*) FROM prev_month pm
     JOIN curr_month cm ON pm.customer_id = cm.customer_id) AS retained_count
)
SELECT
  prev_count,
  curr_count,
  retained_count,
  CASE WHEN prev_count = 0 THEN NULL
       ELSE ROUND((retained_count::numeric / prev_count) * 100, 2)
  END AS retention_rate_percent
FROM counts;

(For DBs without ::numeric, use CAST.)

🔍 Explanation

• Gather distinct customers per month.
• Count previous, current, and intersection.
• Retention = retained / prev * 100.

🧾 Step 3: Expected Output

(Prev month customers: 1 and 2; current: 1 and 3; retained is customer 1 → 1/2 = 50%)

⚙️ Step 4: Why This Query Works

Set operations via CTEs create clear intermediate results; join computes intersection.

⚡ Step 5: Common Mistakes

• Forgetting DISTINCT leading to inflated counts.
• Using current month size as denominator instead of previous month.

🧠 Step 6: Alternate Approach (Optional)

Compute rolling retention for many months by month buckets and lagging; more advanced but same idea.

🎯 Interview Tip

Explain definition of “active” and whether to use purchases, logins, or other events to define activity.

🪄 End of Q17.

❓ Q18: Identify churned customers (inactive in the last 6 months).

🧩 Problem Statement

Return customers with no orders in the past 6 months (similar to Q15 but labelled as churn). Use a reference date (assume 2024-10-31 → cutoff 2024-04-30).

💾 Step 1: Assume Example Tables

customers

orders

Cutoff: 2024-04-30. Customers with last order date <= cutoff are churned if no orders after cutoff.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding the Problem

Find customers whose most recent order is older than the cutoff or who never ordered.

🏗️ SQL Query

WITH last_order AS (
  SELECT customer_id, MAX(order_date) AS last_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.name, lo.last_order_date
FROM customers c
LEFT JOIN last_order lo ON c.customer_id = lo.customer_id
WHERE COALESCE(lo.last_order_date, DATE '1900-01-01') <= DATE '2024-04-30';

(Here COALESCE treats customers with no orders as churned.)

🔍 Explanation

• last_order finds each customer’s latest order date.
• Left join to include customers without orders.
• Filter where last order is on/before cutoff → churned.

🧾 Step 3: Expected Output

(Customer 3 had order on 2024-05-01 > cutoff, so not churned.)

⚙️ Step 4: Why This Query Works

LAST order date compares recency per customer; COALESCE handles never-ordered customers.

⚡ Step 5: Common Mistakes

• Using WHERE on orders before aggregation, which can drop customers entirely.
• Not handling customers with no orders.

🧠 Step 6: Alternate Approach (Optional)

-- Using NOT EXISTS to find customers with no orders after cutoff
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date > DATE '2024-04-30'
);

🎯 Interview Tip

State churn definition clearly: “no purchase in X period” vs “decreasing engagement”, and adjust query accordingly.

🪄 End of Q18.

———

👩‍💼 Data Analyst

❓ Q1: Combine employee and department data using an INNER JOIN.

🧩 Problem Statement

Show each employee’s name along with their department name. Only include employees who belong to an existing department.

💾 Step 1: Assume Example Tables

employees

departments

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: INNER JOIN returns rows where a match exists in both tables.

🏗️ SQL Query

SELECT e.emp_id,
       e.name,
       d.dept_name
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.dept_id;

🔍 Explanation

• e and d are aliases for clarity.
• Only employees whose dept_id appears in departments are shown.

🧾 Step 3: Expected Output

Chetan (Dept 40) is excluded — no matching department.

⚙️ Step 4: Why This Query Works

INNER JOIN merges only intersecting rows by dept_id, giving valid employee-department pairs.

⚡ Step 5: Common Mistakes

• Forgetting the ON condition → Cartesian join.
• Selecting columns with same name without prefix → ambiguity.

🧠 Step 6: Alternate Approach

SELECT e.*, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

Old-style syntax — functionally same but less readable.

🎯 Interview Tip

Mention that INNER JOIN filters unmatched rows, ideal for integrity-checked relations.

🔍 Interviewer’s Analysis

• Concept Tested: INNER JOIN
• Difficulty: Beginner
• Complexity: ≈ O(n × m) without index; O(n log m) with index
• Follow-Ups: Explain LEFT JOIN vs INNER JOIN; Indexing strategy on dept_id.

🪄 End of Q1.

❓ Q2: List all employees with their departments (even if unassigned) using a LEFT JOIN.

🧩 Problem Statement

Return every employee and their department name. If an employee has no department, still include them with NULL as department.

💾 Step 1: Assume Example Tables

Same as Q1 but keep Chetan’s department 40 missing.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

LEFT JOIN keeps all rows from the left table (employees), even when no match on the right.

🏗️ SQL Query

SELECT e.emp_id,
       e.name,
       d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;

🔍 Explanation

All employees appear; unmatched ones show NULL for department.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

LEFT JOIN includes all employees (left table), filling missing matches with NULL.

⚡ Step 5: Common Mistakes

• Using INNER JOIN instead → drops unassigned employees.
• Forgetting to check for NULL when filtering post-join.

🧠 Step 6: Alternate Approach

SELECT e.emp_id, e.name,
       COALESCE(d.dept_name, 'Unassigned') AS dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;

COALESCE replaces NULL with “Unassigned”.

🎯 Interview Tip

Highlight that LEFT JOIN preserves completeness of the left entity — vital in reports.

🔍 Interviewer’s Analysis

• Concept Tested: LEFT JOIN / NULL handling
• Difficulty: Beginner → Intermediate
• Follow-Ups: How to show departments with no employees (→ RIGHT JOIN).

🪄 End of Q2.

❓ Q3: Retrieve all data from both tables with a FULL OUTER JOIN.

🧩 Problem Statement

Show every employee and every department. Include:

• employees without departments
• departments without employees.

💾 Step 1: Assume Example Tables

employees

departments

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

FULL OUTER JOIN = LEFT JOIN UNION RIGHT JOIN. It keeps all rows from both sides.

🏗️ SQL Query

SELECT e.emp_id,
       e.name,
       e.dept_id AS emp_dept,
       d.dept_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.dept_id = d.dept_id;

🔍 Explanation

• Matches where possible.
• When no match, one side’s columns → NULL.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

FULL OUTER JOIN merges the results of left and right joins, ensuring nothing is lost.

⚡ Step 5: Common Mistakes

• Using DB that doesn’t support FULL OUTER JOIN (PostgreSQL yes, MySQL no → need UNION).
• Forgetting to distinguish which dept_id comes from which table.

🧠 Step 6: Alternate Approach

SELECT e.emp_id, e.name, e.dept_id AS emp_dept, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_id, e.name, e.dept_id, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Equivalent manual implementation.

🎯 Interview Tip

Explain which DBs lack native FULL OUTER JOIN and how to emulate it.

🔍 Interviewer’s Analysis

• Concept Tested: FULL OUTER JOIN / Set operations
• Difficulty: Intermediate
• Follow-Ups: “Show only unmatched rows” (→ use WHERE dept_name IS NULL OR name IS NULL).

❓ Q4: Perform a SELF JOIN to show employees along with their managers.

🧩 Problem Statement

Display each employee’s name together with their manager’s name and department.

💾 Step 1: Assume Example Tables

employees

• manager_id points to another row’s emp_id — hence “self” join.

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept tested: SELF JOIN — joining a table to itself with aliases.

🏗️ SQL Query

SELECT e.name       AS employee_name,
       m.name       AS manager_name,
       e.dept_id
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.emp_id;

🔍 Explanation

• e = employee copy, m = manager copy.
• LEFT JOIN keeps top-level managers (NULL manager id).

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

The same table is scanned twice — once as employees, once as managers — matching on manager_id = emp_id.

⚡ Step 5: Common Mistakes

• Forgetting aliases → ambiguous column error.
• Using INNER JOIN → managers only (drops top bosses).

🧠 Step 6: Alternate Approach

SELECT e.name, m.name AS manager_name
FROM employees e
JOIN employees m USING (emp_id)

(Not valid here — shows why aliases + explicit join condition are required.)

🎯 Interview Tip

Explain clearly the difference between self-join and hierarchical CTE (for multi-level chains).

🔍 Interviewer’s Analysis

• Concept Tested: Self-Join / Aliasing
• Difficulty: Intermediate
• Follow-Ups: Find multi-level chain; show manager count per manager.

🪄 End of Q4.

❓ Q5: Find employees who do not belong to any department.

🧩 Problem Statement

Return employees whose dept_id does not exist in the departments table.

💾 Step 1: Assume Example Tables

employees

departments

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: Anti-Join using LEFT JOIN … IS NULL or NOT IN.

🏗️ SQL Query

SELECT e.emp_id, e.name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

🔍 Explanation

• LEFT JOIN keeps all employees.
• The WHERE d.dept_id IS NULL condition isolates those without a matching department.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Unmatched rows (from left table) show NULLs on the right — filtering those identifies missing links.

⚡ Step 5: Common Mistakes

• Using INNER JOIN (returns only matched rows).
• Using NOT IN without handling NULLs → no rows returned if NULL exists in subquery.

🧠 Step 6: Alternate Approach

SELECT emp_id, name
FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE dept_id IS NOT NULL);

🎯 Interview Tip

If interviewer asks “how to optimize,” mention that an anti-join using NOT EXISTS often beats NOT IN.

🔍 Interviewer’s Analysis

• Concept Tested: Left Anti-Join
• Difficulty: Intermediate
• Follow-Ups: Handle NULL values in subqueries; compare NOT IN vs NOT EXISTS.

🪄 End of Q5.

❓ Q6: Display products purchased by customers from more than one country.

🧩 Problem Statement

Find products that have been bought by customers in multiple countries, i.e., count of distinct countries > 1.

💾 Step 1: Assume Example Tables

customers

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concepts: JOIN → GROUP BY → COUNT(DISTINCT) with HAVING.

🏗️ SQL Query

SELECT o.product_id,
       COUNT(DISTINCT c.country) AS country_count
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
GROUP BY o.product_id
HAVING COUNT(DISTINCT c.country) > 1;

🔍 Explanation

• Join orders ↔ customers to access country.
• Group by product and count unique countries.
• Keep only those sold in > 1 country.

🧾 Step 3: Expected Output

(Product 100 bought in India & USA; Product 200 in India & USA.)

⚙️ Step 4: Why This Query Works

Aggregation over joined data computes cross-customer metrics (“distinct countries per product”).

⚡ Step 5: Common Mistakes

• Forgetting DISTINCT → over-count.
• Filtering with WHERE instead of HAVING.

🧠 Step 6: Alternate Approach

-- Using a CTE for clarity
WITH product_countries AS (
  SELECT o.product_id, c.country
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY o.product_id, c.country
)
SELECT product_id
FROM product_countries
GROUP BY product_id
HAVING COUNT(*) > 1;

🎯 Interview Tip

Discuss indexing (customer_id, product_id) for large joins and explain why COUNT(DISTINCT) is CPU-intensive.

🔍 Interviewer’s Analysis

• Concept Tested: Join + Aggregation + Distinct
• Difficulty: Intermediate
• Follow-Ups: Optimize COUNT(DISTINCT); filter by time period.

🪄 End of Q6.

❓ Q7: Find employees who earn more than the average salary.

🧩 Problem Statement

List employees whose salary is greater than the overall average salary across the company.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept tested: Subquery returning a single value (the company-wide average).

🏗️ SQL Query

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

🔍 Explanation

• The inner query computes one scalar — the average salary.
• The outer query filters all employees whose salary > that value.

🧾 Step 3: Expected Output

Average salary = (60000 + 75000 + 90000 + 50000) / 4 = 68750.

⚙️ Step 4: Why This Query Works

The scalar subquery executes once; each row compares its salary to the result.

⚡ Step 5: Common Mistakes

• Forgetting parentheses around subquery.
• Trying to compare multiple rows without aggregation → “subquery returns more than one row.”

🧠 Step 6: Alternate Approach

-- Using a window function (no subquery)
SELECT name, salary
FROM (
  SELECT name, salary, AVG(salary) OVER () AS avg_sal
  FROM employees
) t
WHERE salary > avg_sal;

More modern and efficient.

🎯 Interview Tip

If asked, mention that the window function version avoids recalculating averages repeatedly.

🔍 Interviewer’s Analysis

• Concept Tested: Scalar subquery / Aggregation
• Difficulty: Beginner → Intermediate
• Follow-Ups: Compare performance between subquery and window approach.

🪄 End of Q7.

❓ Q8: Fetch the second-highest salary using subquery and DENSE_RANK.

🧩 Problem Statement

Retrieve the employee(s) with the second-highest salary — including ties.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

We can either:

1. Use a subquery with MAX and <> logic, or
2. Use DENSE_RANK() window function.

We’ll demonstrate both.

🏗️ SQL Query (Approach 1 — Subquery)

SELECT name, salary
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE salary < (SELECT MAX(salary) FROM employees)
);

🔍 Explanation

• Inner-most: Finds highest salary (90000).
• Middle: Finds max salary below that (75000).
• Outer: Returns employees with that salary.

🏗️ SQL Query (Approach 2 — Using DENSE_RANK)

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

🔍 Explanation

• DENSE_RANK() assigns same rank for duplicates.
• We select rows with rank = 2 → second-highest.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

Both methods logically identify the “runner-up” salary value and filter for matching rows.

⚡ Step 5: Common Mistakes

• Using LIMIT 2 incorrectly → returns top two, not specifically second.
• Using ROW_NUMBER (which skips ties).

🎯 Interview Tip

Explain how DENSE_RANK handles duplicates gracefully; ROW_NUMBER would not.

🔍 Interviewer’s Analysis

• Concept Tested: Subqueries / Ranking Functions
• Difficulty: Intermediate
• Follow-Ups: Compare performance; show Nth-highest.

🪄 End of Q8.

❓ Q9: Retrieve products that have never been sold.

🧩 Problem Statement

Find products in the catalog that do not appear in the sales table.

💾 Step 1: Assume Example Tables

products

sales

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: Anti-Join / NOT EXISTS to find unmatched rows.

🏗️ SQL Query

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM sales s
  WHERE s.product_id = p.product_id
);

🔍 Explanation

The subquery checks if a product appears in sales. If no match exists, it’s unsold.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

NOT EXISTS efficiently excludes all products present in sales.

⚡ Step 5: Common Mistakes

• Using NOT IN without NULL handling (fails if sales.product_id has NULL).
• Using INNER JOIN instead of anti-join.

🧠 Step 6: Alternate Approach

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

🎯 Interview Tip

Mention that NOT EXISTS is typically faster than LEFT JOIN for large datasets due to early exits.

🔍 Interviewer’s Analysis

• Concept Tested: Anti-Join / Exclusion Logic
• Difficulty: Intermediate
• Follow-Ups: Performance difference between NOT EXISTS vs LEFT JOIN.

🪄 End of Q9.

❓ Q10: Display customers who never placed an order.

🧩 Problem Statement

List all customers who do not appear in the orders table.

💾 Step 1: Assume Example Tables

customers

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: Anti-Join / NOT EXISTS again, applied on customers ↔ orders.

🏗️ SQL Query

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

🔍 Explanation

Each customer checked against the orders table. Only those with no orders are returned.

🧾 Step 3: Expected Output

⚙️ Step 4: Why This Query Works

The correlated subquery ensures exclusion only when there are no matches.

⚡ Step 5: Common Mistakes

• Using NOT IN directly → issues with NULL values.
• Forgetting correlation (o.customer_id = c.customer_id).

🧠 Step 6: Alternate Approach

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

🎯 Interview Tip

In large-scale data, NOT EXISTS tends to outperform joins when proper indexes exist.

🔍 Interviewer’s Analysis

• Concept Tested: Correlated Subquery / Anti-Join
• Difficulty: Beginner → Intermediate
• Follow-Ups: Add condition for “no order in last 6 months”.

🪄 End of Q10.

❓ Q11: Identify departments that don’t have any employees hired after 2020.

🧩 Problem Statement

Find all departments where no employee joined after the year 2020.

💾 Step 1: Assume Example Tables

employees

departments

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: Use NOT EXISTS to exclude departments with any recent hire.

🏗️ SQL Query

SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.dept_id = d.dept_id
    AND e.hire_date > '2020-12-31'
);

🔍 Explanation

• The subquery searches for employees hired after 2020 in that department.
• If none exist → department qualifies.

🧾 Step 3: Expected Output

Sales excluded (has Ananya hired in 2021).

⚙️ Step 4: Why This Query Works

NOT EXISTS removes departments with any post-2020 hire.

⚡ Step 5: Common Mistakes

• Using HAVING incorrectly (not applicable).
• Mixing up WHERE hire_date < vs > logic.

🧠 Step 6: Alternate Approach

SELECT d.dept_id, d.dept_name
FROM departments d
LEFT JOIN employees e
  ON d.dept_id = e.dept_id AND e.hire_date > '2020-12-31'
WHERE e.emp_id IS NULL;

🎯 Interview Tip

State clearly why you used NOT EXISTS: it ensures logical clarity and performance.

🔍 Interviewer’s Analysis

• Concept Tested: Correlated Subquery / Temporal Filtering
• Difficulty: Intermediate
• Follow-Ups: How to adapt for “no new hires in past N months.”

🪄 End of Q11.

❓ Q12: Retrieve customers whose total purchases exceed the overall customer average.

🧩 Problem Statement

Find customers whose total spend (sum of order amounts) is greater than the average of all customers’ total spend.

💾 Step 1: Assume Example Tables

orders

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Two levels of aggregation:

1. Inner query: total per customer.
2. Outer query: compare each total to overall average.

🏗️ SQL Query

SELECT customer_id, total_amount
FROM (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
) t
WHERE total_amount > (
  SELECT AVG(total_amount)
  FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
  ) sub
);

🔍 Explanation

• Inner aggregation: computes SUM(amount) per customer.
• Outer query compares each to average of those totals.

🧾 Step 3: Expected Output

Average customer total = (3000 + 500 + 5000)/3 = 2833.33.

⚙️ Step 4: Why This Query Works

It’s a double subquery: one for per-customer totals, one for the global average.

⚡ Step 5: Common Mistakes

• Comparing to AVG(amount) directly → wrong (compares per-order, not per-customer).
• Forgetting aliasing in subqueries.

🧠 Step 6: Alternate Approach

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > (SELECT AVG(total_amount) FROM customer_totals);

CTE improves readability.

🎯 Interview Tip

Explain trade-off between nested subquery and CTE — same result, but CTE improves maintainability.

🔍 Interviewer’s Analysis

• Concept Tested: Aggregation within subqueries / CTEs
• Difficulty: Intermediate
• Follow-Ups: Optimize with temporary tables or materialized views.

🪄 End of Q12.

❓ Q13: Assign ranks to employees by salary within each department.

🧩 Problem Statement

Rank employees based on salary within their department, so that the highest earner in each department gets rank 1.

💾 Step 1: Assume Example Tables

employees

🧮 Step 2: Step-by-Step Solution

🧠 Understanding

Concept: Window function — RANK() or DENSE_RANK() partitioned by department.

🏗️ SQL Query

SELECT emp_id,
       name,
       dept_id,
       salary,
       DENSE_RANK() OVER (
         PARTITION BY dept_id
         ORDER BY salary DESC
       ) AS dept_rank
FROM employees;