The Indian Business Analyst’s SQL Playbook: Mastering CTEs, Window Functions, and Scenario-Based Interview Queries

Across technical interview loops at top Global Capability Centers (GCCs), product unicorns, and IT consultancies in Bengaluru, Gurgaon, Hyderabad, Pune, and Noida, one technical evaluation stands between Business Analyst candidates and job offers: the SQL live-coding assessment.

While generic candidate resumes claim proficiency in database querying, hiring managers test for production-ready capabilities. During a technical interview, asking a candidate to run a basic SELECT * or a simple two-table JOIN only tests introductory literacy. To evaluate real-world analytical capability, interviewers present scenario-based problems requiring Common Table Expressions (CTEs), Window Functions, and operational Service Level Agreement (SLA) breach tracking.

Mastering these advanced SQL concepts enables Business Analysts to extract transactional insights, diagnose workflow bottlenecks, and build production-grade queries that power enterprise BI dashboards.

1. Common Table Expressions (CTEs): Structuring Modular Analysis

In production databases containing millions of row items, writing deeply nested subqueries creates unreadable, unmaintainable code that is difficult for engineering teams to audit. A Common Table Expression (CTE) defines a temporary, named result set that exists only within the execution scope of a single query.

CTEs break complex, multi-stage business logic into readable sequential blocks.

+--------------------------------------------------------------------------+
|                  Subquery Spaghetti vs. Modular CTE                      |
+--------------------------------------------------------------------------+
| Nested Subqueries (Hard to Debug)   | CTE Architecture (Modular & Clean) |
+-------------------------------------+------------------------------------+
| SELECT * FROM (                     | WITH Regional_Summary AS (         |
|   SELECT dept_id, AVG(sal) FROM (   |   -- Stage 1: Aggregate data       |
|     ... complex joins ...           |   SELECT ...                       |
|   ) GROUP BY dept_id                | ),                                 |
| ) WHERE avg > 50000;                | SLA_Breaches AS (                  |
|                                     |   -- Stage 2: Filter bottlenecks   |
|                                     |   SELECT ...                       |
|                                     | )                                  |
|                                     | SELECT * FROM SLA_Breaches;        |
+-------------------------------------+------------------------------------+

Syntax and Execution Structure:

WITH Payment_Summary AS (
    SELECT 
        merchant_id,
        COUNT(transaction_id) AS total_transactions,
        SUM(CASE WHEN status = 'SUCCESS' THEN amount ELSE 0 END) AS successful_volume
    FROM fact_merchant_payments
    WHERE transaction_date >= '2026-01-01'
    GROUP BY merchant_id
)
SELECT 
    merchant_id,
    total_transactions,
    successful_volume,
    ROUND((successful_volume / NULLIF(total_transactions, 0)), 2) AS conversion_rate
FROM Payment_Summary
WHERE total_transactions > 1000;

By isolating the aggregation step inside Payment_Summary, the primary query remains clean, readable, and easy to modify during live whiteboard interviews.

2. Window Functions: Computing In-Set Analytics Without Collapsing Rows

Standard GROUP BY clauses aggregate multiple records into a single summary row, losing underlying line-item detail. Window Functions perform calculations across a set of table rows related to the current row without collapsing the raw dataset.

In enterprise analytics, Window Functions are essential for running calculations like cumulative totals, moving averages, row-by-row differences, and ranking operations.

                      +----------------------------------+
                      |   Core SQL Window Functions for BAs  |
                      +----------------------------------+
                                       |
        +------------------+-----------+-----------+------------------+
        |                  |                       |                  |
+---------------+  +---------------+       +---------------+  +---------------+
| Ranking       |  | Navigation    |       | Aggregations  |  | Distribution  |
| - ROW_NUMBER()|  | - LAG()       |       | - SUM() OVER  |  | - NTILE()     |
| - RANK()      |  | - LEAD()      |       | - AVG() OVER  |  | - PERCENT_RANK|
| - DENSE_RANK()|  |               |       |               |  |               |
+---------------+  +---------------+       +---------------+  +---------------+

Key Window Functions Every BA Must Master:

1. ROW_NUMBER() vs. DENSE_RANK() vs. RANK():

   • ROW_NUMBER(): Assigns a unique sequential integer to each row (1, 2, 3, 4). Ideal for deduplicating records.

   • RANK(): Assigns identical ranks to tie values, skipping subsequent rank numbers (1, 2, 2, 4).

   • DENSE_RANK(): Assigns identical ranks to tie values without skipping numbers (1, 2, 2, 3). Essential for finding “N-th highest” business metrics.

2. LAG() and LEAD():

   • LAG(column, offset): Accesses data from a previous row in the same result set without performing a self-join. Used for calculating Month-over-Month (MoM) growth or latency delays.

   • LEAD(column, offset): Accesses data from a subsequent row. Used for calculating user journey transition times between funnel steps.

3. Real-World Scenario-Based Interview Queries

During technical rounds, interviewers present practical business scenarios reflecting operational challenges across Indian tech domains—such as quick-commerce dark stores, payment gateway switches, and support ticketing platforms.

Scenario A: Identifying Customer Support SLA Breaches

• Business Problem: A quick-commerce platform requires tracking ticket resolution times for order refunds. A ticket is flagged as an “SLA Breach” if the resolution turnaround time (TAT) exceeds 4 hours (240 minutes).

• Interview Goal: Write a query that computes ticket resolution times, isolates SLA breaches, and ranks support agents by breach count.

WITH Ticket_Processing AS (
    SELECT 
        agent_id,
        ticket_id,
        created_at,
        resolved_at,
        -- Calculate processing duration in minutes
        DATEDIFF(minute, created_at, resolved_at) AS resolution_time_mins,
        CASE 
            WHEN DATEDIFF(minute, created_at, resolved_at) > 240 THEN 1 
            ELSE 0 
        END AS is_sla_breached
    FROM fact_support_tickets
    WHERE created_at >= '2026-08-01'
),
Agent_Metrics AS (
    SELECT 
        agent_id,
        COUNT(ticket_id) AS total_handled,
        SUM(is_sla_breached) AS total_sla_breaches,
        AVG(resolution_time_mins) AS avg_tat_mins
    FROM Ticket_Processing
    GROUP BY agent_id
)
SELECT 
    agent_id,
    total_handled,
    total_sla_breaches,
    ROUND(avg_tat_mins, 1) AS avg_tat_mins,
    DENSE_RANK() OVER (ORDER BY total_sla_breaches DESC) AS breach_rank
FROM Agent_Metrics
WHERE total_handled >= 20;

Scenario B: Finding the Second Highest Transaction Amount Per Merchant

• Business Problem: A payment gateway needs to identify the 2nd highest individual transaction processed by each merchant to analyze transaction volumes without skewed outlier peaks.

• Interview Goal: Write a query that returns the exact transaction record using DENSE_RANK().

WITH Ranked_Transactions AS (
    SELECT 
        merchant_id,
        transaction_id,
        amount,
        payment_mode,
        created_at,
        DENSE_RANK() OVER (
            PARTITION BY merchant_id 
            ORDER BY amount DESC
        ) AS transaction_rank
    FROM fact_gateway_payments
    WHERE status = 'SUCCESS'
)
SELECT 
    merchant_id,
    transaction_id,
    amount,
    payment_mode,
    created_at
FROM Ranked_Transactions
WHERE transaction_rank = 2;

4. Quantifying Operational System SLAs in SQL

In modern enterprise platforms—including payment processors, healthcare claims engines, and logistics networks—performance is governed by strict Service Level Agreements (SLAs).

An SLA defines the mandatory performance threshold or maximum allowable turnaround time (TAT) for a system call, microservice API, or manual task queue. Business Analysts write SQL queries to quantify SLA compliance percentages across operational units:

-- Calculating Monthly API Latency SLA Compliance Rate (< 1500 ms)
SELECT 
    FORMAT_DATE('%Y-%m', transaction_timestamp) AS processing_month,
    api_endpoint,
    COUNT(request_id) AS total_api_requests,
    SUM(CASE WHEN latency_ms <= 1500 THEN 1 ELSE 0 END) AS requests_within_sla,
    ROUND(
        (SUM(CASE WHEN latency_ms <= 1500 THEN 1.0 ELSE 0.0 END) / COUNT(request_id)) * 100, 
        2
    ) AS sla_compliance_percentage
FROM log_api_gateway_transactions
GROUP BY processing_month, api_endpoint
ORDER BY processing_month DESC, sla_compliance_percentage ASC;

Demonstrating an understanding of how to quantify system performance against operational SLAs during technical interviews signals strong business domain maturity.

5. Bridging the Gap Between Basic Syntax and Production Execution

For freshers and non-CS graduates, transitioning from simple database syntax to multi-stage CTEs, Window Functions, and SLA governance queries requires structured, hands-on practice. Self-studying through static tutorials often leaves candidates underprepared when facing live, time-pressured whiteboard coding tests during company interview rounds.

Acquiring job-ready SQL capabilities requires practical instruction centered on real-world business case studies. Enrolling in an industry-aligned business analyst course offered by established institutions like SLA Consultants India helps learners build practical capabilities from the ground up. Programs focused on live SQL query optimization, Star Schema database modeling, Power BI integration, BPMN 2.0 process flow design, and Agile Jira documentation prepare candidates to clear corporate technical evaluations with confidence.

6. Whiteboard Strategy for SQL Technical Rounds

When solving SQL scenarios during virtual or face-to-face technical interviews, follow this structured communication strategy:

[ Step 1: Clarify Rules ] ──► Confirm table schemas, NULL handling, & SLA targets
                                          │
                                          ▼
[ Step 2: State Approach ] ──► Explain CTE structure & window function choice out loud
                                          │
                                          ▼
[ Step 3: Write Modular Code ] ──► Draft SQL using clear CTE blocks & explicit column aliases
                                          │
                                          ▼
[ Step 4: Validate Edge Cases ] ──► Check zero-division (NULLIF), duplicate ranks, & performance

1. Clarify Business Requirements First: Ask explicit questions about edge cases before writing code. Are transaction amounts nullable? Should duplicate transaction amounts receive identical ranks? What is the precise SLA turnaround time window?

2. Talk Through Your Query Architecture: Explain your approach out loud before typing. For instance: “I will first construct a CTE to aggregate total tickets and calculate turnaround times per agent, and then apply a DENSE_RANK() window function to rank performance based on SLA breach counts.”

3. Handle Potential Division-by-Zero Errors: Always wrap denominators in NULLIF(denominator, 0) when calculating percentages or conversion ratios to prevent query failure.

4. Optimize Code Readability: Use uppercase keywords (WITH, SELECT, OVER, PARTITION BY), indent CTE blocks cleanly, and use explicit column aliases.

By mastering CTEs for modular query design, leveraging Window Functions for row-level analytics, and quantifying operational SLAs across real-world business scenarios, Business Analysts can navigate technical screening loops, prove day-one readiness, and secure competitive job offers across India’s booming technology sector.