31.10 SQL and Excel Questions Paired with Power BI Rounds

Many MNC Power BI rounds include a short SQL section. The examples below use our fictional tables dbo.Orders, dbo.DarkStore and dbo.Customer.

M96. Explain the logical order of execution of a SQL query, and WHERE vs HAVING.

Reported for: Wipro [S19] · also KPMG [S10], Tech Mahindra [S24]

Logical order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/LIMIT (OFFSET). That is why a column alias from SELECT can't be used in WHERE. WHERE filters rows before grouping; HAVING filters groups after aggregation.

SELECT s.City, SUM(o.Amount) AS Sales
FROM dbo.Orders o JOIN dbo.DarkStore s ON s.StoreID = o.StoreID
WHERE o.OrderStatus = 'Delivered'              -- row filter
  AND s.City IN ('Pune','Nashik','Nagpur','Kolhapur','Solapur','Sambhaji Nagar')
GROUP BY s.City
HAVING SUM(o.Amount) > 100000;                -- group filter

M97. Find the Nth (for example 2nd or 3rd) highest value.

Reported for: Wipro [S19] · also Amazon [S32], KPMG [S10], FedEx India [S33]

Our version: the store with the 3rd-highest sales.

WITH s AS (
  SELECT StoreID, SUM(Amount) AS Sales,
         DENSE_RANK() OVER (ORDER BY SUM(Amount) DESC) AS rnk
  FROM dbo.Orders GROUP BY StoreID)
SELECT StoreID, Sales FROM s WHERE rnk = 3;

DENSE_RANK handles ties correctly. The older answer (SELECT MAX(x) WHERE x < (SELECT MAX(x) …)) works for 2nd highest only.

M98. Find each customer's second most recent order date.

Reported for: Wipro [S19]

WITH o AS (
  SELECT CustomerID, OrderDate,
         DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rnk
  FROM (SELECT DISTINCT CustomerID, CAST(OrderDateTime AS date) AS OrderDate FROM dbo.Orders) x)
SELECT CustomerID, OrderDate FROM o WHERE rnk = 2;

The inner DISTINCT removes repeated order lines, so each date counts once.

M99. How do window functions like SUM() OVER (PARTITION BY …) work? Give a running total.

Reported for: Tech Mahindra [S24] · also Amazon [S32], KPMG [S10], Wipro [S19] (cumulative salary)

A window function calculates across related rows without collapsing them into one row. PARTITION BY restarts the calculation for each group and ORDER BY defines the running order.

SELECT s.City, CAST(o.OrderDateTime AS date) AS OrderDate, SUM(o.Amount) AS DaySales,
       SUM(SUM(o.Amount)) OVER (PARTITION BY s.City
            ORDER BY CAST(o.OrderDateTime AS date)) AS RunningSales
FROM dbo.Orders o JOIN dbo.DarkStore s ON s.StoreID = o.StoreID
GROUP BY s.City, CAST(o.OrderDateTime AS date);

M100. DELETE vs TRUNCATE, and primary vs unique vs foreign key.

Reported for: KPMG [S10] · also Tech Mahindra [S24]

DELETE removes selected rows (it can use WHERE), is fully logged and fires triggers. TRUNCATE removes all rows quickly, can't use WHERE and usually resets identity values. Primary key: unique, not null, one per table (DarkStore.StoreID). Unique key: unique values, usually allows one NULL (in SQL Server), several per table. Foreign key: a column that references another table's primary key (Orders.StoreID → DarkStore.StoreID) and enforces referential integrity.

M101. Excel/VBA: what are modules and functions in VBA?

Reported for: Wipro [S18]

A module is a container for VBA code in a workbook (standard modules, sheet/workbook modules, class modules). Inside it you write Sub procedures (perform actions, such as formatting a daily Blinkit export) and Functions (return a value and can be used in worksheet formulas as user-defined functions). In Power BI projects, Power Query now replaces many VBA clean-up macros.