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

Companies asking these questions

All 16 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Presidio SQL Interview Questions

Companies asking these questions

All 16 Questions

More Interview Prep Guides

Get All Answers in PDF Format