Question 1

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 2

CSV Without Column Names/Schema - how to read

Accepted Answer

**Pandas:** header=None, names=['a','b','c']. **PySpark:** option('header','false'), schema or inferSchema. **Validate:** Check column count. Infer types cautiously.

pd.read_csv('file.csv', header=None, names=['a','b','c'])
spark.read.option('header','false').schema('a INT, b STRING').csv('file.csv')

Question 3

Case statement in SQL - explain

Accepted Answer

**Simple:** CASE col WHEN v1 THEN r1 ... **Searched:** CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ELSE r3 END. Use in SELECT, WHERE, ORDER BY, GROUP BY.

SELECT name, CASE WHEN score>=90 THEN 'A' WHEN score>=80 THEN 'B' ELSE 'C' END AS grade FROM students;

Question 4

Coalesce function in SQL - explain

Accepted Answer

**COALESCE(v1, v2, ..., vN):** Returns first non-NULL. Standard SQL. Use for: defaults, merging columns, full outer join keys.

**vs ISNULL:** COALESCE takes N args; ISNULL takes 2. COALESCE is portable....

Question 5

Filter Rows Where Employee Salary > Manager Salary

Accepted Answer

Self-join pairs employee with manager: SELECT e.employee_id, e.name, e.salary, m.salary AS manager_salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary. **Why self-join**: Hierarchical data in one table. **Edge case**: CEO has manager_id NULL—excluded by INNER JOIN. Use LEFT JOIN + WHERE manager_id IS NOT NULL if CEO shouldn't appear....

Question 6

Find 3rd Highest Salary

Accepted Answer

DENSE_RANK handles ties: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM employees) t WHERE rk = 3. Subquery MAX approach: nested 3 levels. **Why DENSE_RANK**: Ties get same rank; RANK skips (two 2nd → next is 4th). **Edge case**: Fewer than 3 distinct salaries returns empty....

Question 7

No Column Names in CSV - how to handle

Accepted Answer

CSV without headers: (1) pandas: pd.read_csv('file.csv', header=None) or names=['col1','col2',...]. (2) Spark: spark.read.option('header','false').csv('path') with schema or toDF('c1','c2'). (3) BigQuery: skipHeaderRows or autodetect with header=false. (4) Redshift: use IGNOREHEADER 0 or specify columns. Best: define schema explicitly to avoid type inference errors. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

LTIMindtree SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format

LTIMindtree SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format