Question 1

Describe a scenario where partitioning and bucketing would improve query performance.

Accepted Answer

Situation: An events table with billions of rows serving time-range and user-level analytics. Task: Achieve sub-second query latency while controlling storage and compute costs. Why Partitioning: Partition pruning at read time eliminates entire data scans—a query filtering by date_range only touches relevant partition dirs. This reduces I/O by orders of magnitude (e.g., 365 partitions → scan 1 vs all)....

Question 2

Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.

Accepted Answer

**Architectural Logic**: All three assign ordinals over partitions but differ in tie-handling and downstream semantics. ROW_NUMBER(): Deterministic unique ordering—required for deduplication (pick one row per key). RANK(): Ties get same rank, next rank skips—classic leaderboard (e.g., Olympic medals). DENSE_RANK(): Ties get same rank, no skip—"top N per category" when N must be exact count. **Why**: ROW_NUMBER needs ORDER BY with a tie-breaker for determinism. RANK reflects competitive gaps....

Question 3

Difference between where and having clause with examples.

Accepted Answer

**Architectural Logic**: WHERE filters rows before aggregation; HAVING filters groups after aggregation. Execution order: FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT. **Why**: WHERE enables predicate pushdown—filtering early reduces rows before shuffle and aggregation, saving memory and I/O. HAVING operates on aggregates; pushing filter logic into WHERE when possible is critical for performance....

Question 4

Explain the difference between UNION and UNION ALL.

Accepted Answer

**Architectural Logic**: UNION concatenates result sets and deduplicates (implicit DISTINCT); UNION ALL concatenates and keeps all rows. **Why**: UNION requires sort/hash to detect duplicates—O(n log n) or O(n) with hash. UNION ALL is a simple concatenation—O(n). **Scalability**: UNION doubles I/O (read both, write deduped). At scale, UNION on large inputs can spill and bottleneck. **Cost**: UNION can 2–5x bytes processed vs UNION ALL when inputs are large....

Question 5

Implement a query to find the top 5 customers by total sales amount.

Accepted Answer

**Architectural Logic**: Two approaches. 1. GROUP BY + ORDER BY + LIMIT: SELECT customer_id, SUM(sales_amount) total_sales FROM sales GROUP BY customer_id ORDER BY total_sales DESC LIMIT 5. 2. Window: SELECT * FROM (SELECT customer_id, SUM(sales_amount) total_sales, RANK() OVER (ORDER BY SUM(sales_amount) DESC) rk FROM sales GROUP BY customer_id) t WHERE rk <= 5. **Why**: LIMIT is simpler, stops early in some engines....

Question 6

What are primary keys and foreign keys? Why are they important?

Accepted Answer

**Architectural Logic**: PK: Unique, NOT NULL identifier for entity integrity; often clustered index. FK: Reference to PK in another table; enforces referential integrity (no orphans). **Why**: PKs enable uniqueness checks, join performance, replication. FKs prevent orphaned child rows and encode relationships. In warehouses, PKs/FKs are often logical (not enforced) for load performance....

Question 7

What is a self-join, and when would you use it?

Accepted Answer

**Architectural Logic**: Self-join = table joined to itself with aliases. Use cases: hierarchies (emp→manager), same-table comparisons (find pairs with same attribute, different on another), sequential comparison (before LAG/LEAD existed). **Why**: Hierarchy: e JOIN emp m ON e.mgr_id = m.id. Comparison: find users who share signup_date but different region. **Scalability**: Self-join doubles table scan; for hierarchies, recursive CTEs or closure tables scale better....

Question 8

What is normalization and denormalization? When would you use each?

Accepted Answer

**Architectural Logic**: Normalization (1NF→3NF): Remove redundancy, enforce integrity. OLTP optimized for writes, consistency. Denormalization: Add redundancy for read performance. OLAP/data marts optimized for analytics. **Why**: Normalized = single source of truth, easier updates. Denormalized = fewer joins, faster queries, larger storage. **Scalability**: Normalized scales for writes; denormalized scales for reads but requires ETL to maintain....

Question 9

What is the difference between a clustered and non-clustered index?

Accepted Answer

**Architectural Logic**: Clustered: Data stored in index order; one per table (usually PK). Table = clustered index. Non-clustered: Separate structure with pointers to data; multiple per table. **Why**: Clustered optimizes range scans (e.g., ORDER BY PK); inserts may cause page splits. Non-clustered optimizes lookups and covering queries. **Scalability**: Clustered on wrong key can hurt insert performance. Non-clustered adds write amplification....

Question 10

What is the difference between a view and a materialized view?

Accepted Answer

**Architectural Logic**: View: Virtual; query runs at read time. No storage. Materialized view: Physical storage of query result; refreshed on schedule or on commit. **Why**: Views for abstraction, security, logic reuse. Materialized views for precomputed expensive aggregations, low-latency dashboards. **Scalability**: View scales with underlying tables. Materialized view scales with refresh strategy—incremental vs full....

Swiggy SQL Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 21 Questions

More Interview Prep Guides

Unlock All Expert Answers