Question 1

Explain the differences between Repartition and Coalesce. When would you use each?

Accepted Answer

**Repartition(n)**: Full shuffle; creates exactly n partitions. Can increase or decrease. **Coalesce(n)**: Merges partitions without full shuffle; only decreases. **Why it matters**: Shuffle is expensive—network and disk I/O. Coalesce avoids shuffle when reducing partitions by merging within existing partitions. **When Repartition**: Increasing partitions, fixing skew (repartition by key), or before a join to align partition counts....

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

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 6

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 7

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 8

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 9

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....

Question 10

What is the difference between DELETE and TRUNCATE?

Accepted Answer

**Architectural Logic**: DELETE: DML; row-by-row (or batch); logged per row; supports WHERE; triggers fire; slower. TRUNCATE: DDL; deallocates data pages; minimal logging; no WHERE; resets identity; faster. **Why**: DELETE for conditional removal, audit trails, cascade. TRUNCATE for full table clear (e.g., staging before load). **Scalability**: DELETE locks rows/pages; long-running on large tables blocks. TRUNCATE is metadata change + page deallocation—near-instant....

Presidio SQL Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 16 Questions

More Interview Prep Guides

Unlock All Expert Answers