Abstract:

This mock interview transcript captures a candidate, Bhagish, responding to four standard SQL scenario-based questions focusing on relational database manipulation. The queries require the use of joins, aggregation functions (COUNT, SUM, AVG), grouping (GROUP BY), and filtering mechanisms (HAVING, subqueries/self-joins). As far as I can tell from the verbal explanation—since the executable code is not visible—the solutions proposed are structurally sound for typical SQL environments, though the candidate's verbal explanation in several instances is unnecessarily long and sometimes imprecise (e.g., the logic presented for retrieving the 'Top 5').

SQL Interview Analysis: Efficiency and Correctness in Relational Queries

• 0:00:50 Query 1: Find Products Sold More Than Once in a Day.

  • Details: Requires checking for duplicate sales records within the same date.
  • Key Takeaway: Solved efficiently by joining Sales and Product tables, grouping the results by product name and sales date, and using the HAVING COUNT(*) > 1 clause. This demonstrates correct use of aggregation filtering.

• 0:04:38 Query 2: Find the Top Five Customers by Total Order Amounts.

  • Details: Requires calculating the total expenditure per customer by multiplying sales quantity (from Sales table) by price (from Product table).
  • Key Takeaway: The approach correctly uses SUM(Quantity * Price) grouped by Customer ID and ordered descendingly. However, the candidate's specific mention of using ROW_NUMBER() = 5 for "Top 5" (0:09:49) is highly confusing; that usually selects only the fifth row, not the top five. I don't care about the specific SQL dialect, but the explanation is fundamentally flawed if they truly mean selecting only the fifth entry.

• 0:12:36 Query 3: Find Employees Earning Above Departmental Average Salary.

  • Details: Standard query requiring comparison of an employee's salary against a dynamic departmental average.
  • Key Takeaway: The solution correctly relies on a correlated subquery (or self-join structure) where the internal query calculates AVG(Salary) for the specific department being evaluated by the outer query. This is the correct, if slightly complex, method for this problem.

• 0:17:04 Query 4: Count Employees by Gender within a Specific Birth Date Range (1960–1975).

  • Details: Requires filtering records based on date of birth and summarizing the counts by gender.
  • Key Takeaway: A simple and effective solution combining a WHERE clause using date comparisons (or BETWEEN) and GROUP BY Gender. The final counts reported were 3 males and 2 females in the specified range (0:19:29).