Question 1

Write an SQL query to find the second-highest salary from an employee table.

Accepted Answer

**Using subquery with MAX**:
```sql
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
```

**Using LIMIT/OFFSET** (MySQL, PostgreSQL):
```sql
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
```

**Using DENSE_RANK** (ANSI SQL, most robust):
```sql
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employee
) t
WHERE rk = 2;
```

**Architectural Logic & Trade-offs**:
- **Subque...

Question 2

Demonstrate the difference between DENSE_RANK() and RANK()

Accepted Answer

**RANK()**: Same rank for ties; skips subsequent ranks (e.g., 1, 2, 2, 4, 5). **DENSE_RANK()**: Same rank for ties; no gaps (e.g., 1, 2, 2, 3, 4). **Why it matters**: RANK preserves "position" semantics (e.g., 4th place); DENSE_RANK gives consecutive integers useful for filtering (e.g., TOP 10). **Example**: `SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rk FROM employee`....

Question 3

Discuss differences between ROW_NUMBER(), RANK(), and DENSE_RANK(), and provide examples from your projects.

Accepted Answer

**ROW_NUMBER()**: Unique sequential numbers (1, 2, 3...); no ties—deterministic only with ORDER BY uniqueness. **RANK()**: Same rank for ties; skips (1, 2, 2, 4). **DENSE_RANK()**: Same rank for ties; no gaps (1, 2, 2, 3). **Project examples**: ROW_NUMBER() to deduplicate events by (user_id, event_time) keeping first—critical when upstream sends duplicates. DENSE_RANK() for 'top 10 products per category' reports—avoids gaps when filtering....

Question 4

Explain the differences between Data Warehouse, Data Lake, and Delta Lake

Accepted Answer

**Data Warehouse**: Structured, schema-on-write; optimized for SQL analytics (Snowflake, BigQuery). High compute cost, fast queries. **Data Lake**: Raw/semi-structured object storage (S3, ADLS); schema-on-read; low cost, flexible. **Delta Lake**: Open-source storage layer on a data lake adding ACID transactions, schema enforcement, time travel, upserts. **Why the distinction**: Warehouses scale compute and storage together; lakes decouple them....

Question 5

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 6

What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?

Accepted Answer

**Partitioning**: Physically divides data by column values (e.g., date, region); enables partition pruning; one directory per partition value. **Bucketing**: Divides data within a partition into a fixed number of files via hash of bucketing column(s); co-locates same-key rows. **When to bucket**: Frequent joins or group-bys on a column (e.g., user_id). Same bucket count on both sides enables sort-merge join without shuffle....

Question 7

Can you explain the difference between OLTP and OLAP?

Accepted Answer

**OLTP**: Optimized for many small transactions (inserts, updates, deletes). Row-oriented, normalized, high concurrency. Examples: MySQL, PostgreSQL. **OLAP**: Optimized for complex analytical queries and aggregations on large datasets. Column-oriented, denormalized (star/snowflake). Examples: Snowflake, BigQuery, Redshift. **Why the split**: Different access patterns; mixing them degrades both. OLTP needs low latency and ACID; OLAP needs scan throughput....

Question 8

Describe a time when you had to optimize a slow SQL query. What steps did you take?

Accepted Answer

**Situation**: A critical exec report was timing out at 30+ minutes; SLA was 5 minutes. **Task**: Diagnose and fix without changing business logic. **Action**: I ran EXPLAIN (ANALYZE) and found: (1) missing index on join key causing full table scan, (2) cross join that could be inner join with a filter, (3) filter in HAVING that could move to WHERE to reduce rows early, (4) unnecessary ORDER BY on a subquery....

Question 9

Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Accepted Answer

**INNER JOIN**: Only rows with matches in both tables. **LEFT JOIN**: All from left; matches from right; NULLs where no match. **RIGHT JOIN**: All from right; matches from left. **FULL JOIN**: All from both; NULLs where no match. **Why it matters**: Join choice affects result cardinality and semantics. Wrong join = wrong numbers. **Scalability**: Hash joins are common; broadcast for small dimension. FULL OUTER can be expensive—large shuffle....

Question 10

How do you handle NULL values in SQL? Mention functions like COALESCE and NULLIF.

Accepted Answer

**Approaches**: IS NULL / IS NOT NULL for filtering. **COALESCE(val1, val2, ...)**: First non-NULL value; useful for defaults. **NULLIF(val1, val2)**: Returns NULL if equal; e.g., NULLIF(divisor, 0) to avoid divide-by-zero. **Why it matters**: NULL propagates in expressions; aggregate functions ignore NULL (except COUNT(*)). JOIN on NULL yields no match (NULL ≠ NULL). **Scalability**: COALESCE in SELECT is cheap; in WHERE or JOIN it can prevent index use....

Medium SQL Interview Questions

Companies asking these questions

All 60 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Medium SQL Interview Questions

Companies asking these questions

All 60 Questions

More Interview Prep Guides

Get All Answers in PDF Format