Advanced SQL for
senior data interviews in 2027

Advanced SQL is the gate to senior data analyst, analytics engineer, BI developer and data engineer roles — and to product analytics jobs built on event data. These roles pay more because the SQL sits closer to production data and decisions.

On AmbitionBox in October 2026, senior data analysts in India typically earned ₹12.9–14.2 lakh and data engineers ₹11.5–12.7 lakh, compared with ₹6.5–7.2 lakh for data analysts overall.¹² The jump from analyst to senior analyst is, in most loops, a jump in SQL depth.

Typical senior data analyst pay in India (AmbitionBox, Oct 2026)

₹12.9–14.2 lakh

Growth in senior data analyst pay over two years (AmbitionBox)

17%

Typical data engineer pay in India

₹11.5–12.7 lakh

Recurring patterns that cover most advanced SQL rounds

~12

Table: How advanced SQL is tested in senior data hiring (editorial summary)

PostgreSQL's documentation explains window functions and frames precisely;³ the same concepts apply in BigQuery, Snowflake and SQL Server with small syntax differences.

Analytics engineering and data engineering interviews add SQL that changes data, not just reads it:

• MERGE / upserts — insert new rows and update changed ones in one statement.⁵
• Incremental models — process only new or changed data, with a safe lookback for late arrivals; dbt's incremental models are the common pattern.⁷
• Slowly changing dimensions — closing old rows and inserting new versions.
• Partitioning and clustering — so queries scan less data; BigQuery's partitioned tables are a typical example.⁶
• Transactions and locking — why two jobs updating the same table can collide.

Performance questions test whether you understand what the database does. Know how to read a basic plan with EXPLAIN,⁴ why a function on an indexed column can prevent index use, how filtering early reduces work, why SELECT * on a wide warehouse table costs money, and how join order and fan-out explode row counts.

"Longest streak of consecutive active days per user."

Deduplicate to one row per user-day, compute date minus ROW_NUMBER() OVER (PARTITION BY user ORDER BY day) as an island key, group by user and key, take the MAX length. Mention time zones and duplicate events.

"Day-30 retention by signup cohort."

CTE for each user's signup date, CTE for activity days, join on activity date = signup + 30 (or within a window, per definition), aggregate by cohort week. State the definition first.

"Deduplicate a table keeping the latest version of each record."

ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC, ingested_at DESC) and keep 1. Discuss tie-breakers.

"Seven-day moving average of revenue."

Aggregate to one row per day (filling missing days with a calendar table), then AVG() OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

"Why does this query return more revenue than finance?"

Look for join fan-out (summing at the wrong grain), duplicate events and missing filters for refunds or test orders.

"Write an upsert for a daily customer snapshot."

MERGE INTO target USING staging ON key — WHEN MATCHED AND values differ THEN UPDATE, WHEN NOT MATCHED THEN INSERT. Mention idempotency and deleted records.

"This query got slow after data doubled. What do you check?"

The plan: full scans, missing partition filters, non-sargable predicates, exploding joins and sort or spill steps. Then fix the biggest cost first.

"RANK vs DENSE_RANK vs ROW_NUMBER?"

ROW_NUMBER gives unique numbers; RANK leaves gaps after ties; DENSE_RANK doesn't. Choose by how ties should be treated.

Table: How advanced SQL expectations have changed (editorial summary)

• A verified advanced SQL score — timed, with explanations reviewed.
• Three pattern write-ups — gaps and islands, cohort retention and a funnel, on public data.
• One incremental model — with a test proving it's idempotent.
• A performance before/after — a query you made faster and why.

1. Week 1: Windows — ROW_NUMBER, RANK, LAG, frames. Fifteen problems.
2. Week 2: Analytical patterns — Cohorts, funnels, sessionisation, gaps and islands.
3. Week 3: Data quality patterns — Deduplication, late data, NULL and date edge cases.
4. Week 4: Engineering SQL — MERGE, incremental logic, SCD type 2.
5. Week 5: Performance — EXPLAIN, indexes, partitions; take the Advanced SQL Assessment.
6. Week 6: Timed practice — Four-problem sets under time, explaining aloud.

Pair it with the Data Engineering Fundamentals test for engineering roles or the Product Analytics certification for product roles; our product analyst guide shows how these patterns appear in that loop.

The question: "For each user, find their longest run of consecutive active days."

WITH days AS (
  SELECT DISTINCT user_id, CAST(event_time AT TIME ZONE 'Asia/Kolkata' AS DATE) AS d
  FROM events
),
grouped AS (
  SELECT user_id, d,
         d - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS INT) AS island
  FROM days
)
SELECT user_id, MAX(streak) AS longest_streak
FROM (SELECT user_id, island, COUNT(*) AS streak FROM grouped GROUP BY user_id, island) s
GROUP BY user_id;

The DISTINCT removes duplicate events on the same day, the time-zone conversion decides which day an event belongs to, and subtracting the row number from the date gives consecutive days the same "island" value. Mention that the syntax for date arithmetic differs by database, and you've covered the edge cases interviewers probe.

1. Build an events table — Generate or download app event data (Google's public GA4 sample e-commerce dataset in BigQuery works well) and keep one table of user, event, timestamp and properties.
2. Five patterns on it — Write sessionisation, day-7 retention by cohort, a three-step funnel within 24 hours, top three products per category and a seven-day moving average.
3. Gaps and islands — Find each user's longest streak of active days; then break your own query with duplicate events and time zones and fix it.
4. Incremental model — Write a MERGE that loads only new or changed rows, run it twice and prove the result is identical.
5. Plan reading — Take a slow query, run EXPLAIN, change one thing (a filter, an index or partition pruning) and record the before and after.

Advanced SQL is the most common gate between junior and senior data roles. Typical pay on AmbitionBox in October 2026:

Table: Roles that test advanced SQL and their typical pay in India (AmbitionBox, October 2026; indicative)

• Losing the grain halfway through a multi-CTE query.
• Default window frames producing wrong running totals with duplicate dates.
• Ignoring ties in top-N problems.
• Fixing fan-out with DISTINCT instead of aggregating at the right level.
• Optimising blindly without reading the plan.

Pay figures come from AmbitionBox's senior data analyst and data engineer pages (read October 2026). SQL references are PostgreSQL's documentation (window functions, EXPLAIN, MERGE), Google Cloud's BigQuery documentation and dbt's documentation. Test formats, the pattern list and the history table are editorial summaries. Science of Apps publishes the free assessment linked here. Last reviewed October 2026.

• Window function — a function computed over a set of related rows while keeping each row.
• Frame — the subset of rows a window function uses, such as the previous seven rows.
• CTE — a named subquery defined with WITH, which can be recursive.
• Gaps and islands — the problem of finding consecutive runs in ordered data.
• Upsert / MERGE — inserting new rows and updating existing ones in one operation.
• Query plan — the database's step-by-step strategy for running a query.
• Partition pruning — skipping partitions that can't contain matching rows.