Question 1

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 2

Write a query to find the top three highest-paid employees in each department using window functions.

Accepted Answer

**Architectural Logic:** Same pattern as ID 116—window + filter. DENSE_RANK for 'include ties'; ROW_NUMBER for exactly 3. **Query:** WITH ranked AS (SELECT employee_id, name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) SELECT employee_id, name, department, salary FROM ranked WHERE rn <= 3 ORDER BY department, salary DESC....

Question 3

Can you describe a project where you handled large volumes of data?

Accepted Answer

**Situation:** At [Company], we had to process 50TB daily from clickstream and events.

**Task:** Deliver analytics within 2 hours of data arrival, with 99.9% SLA.

**Action:** I designed a Spark pipeline with dynamic partitioning by user_id to reduce skew. Used Delta Lake for ACID and compaction. Tuned executor memory and shuffle partitions. Implemented incremental processing with watermark and dead-letter handling for bad records....

Question 4

Demonstrate how to use a LEFT JOIN to combine data from two tables and handle null values.

Accepted Answer

**Architectural Logic**: LEFT JOIN preserves left rows; nulls arise from non-matches—handle for display and aggregation. **Example**: `SELECT o.order_id, c.customer_name, COALESCE(c.city, 'Unknown') AS city FROM orders o LEFT JOIN customers c ON o.customer_id = c.id`. **Aggregation**: `COUNT(c.id)` counts matches; `COUNT(*)` counts all left. Use COALESCE/IFNULL for display; CASE for conditional logic....

Question 5

How do you handle negotiations if your expected salary isn't met?

Accepted Answer

**Situation**: Offer 15% below expectation. **Task**: Reach agreement or walk away professionally. **Action**: (1) Researched Levels.fyi, Glassdoor for range. (2) Requested time to consider. (3) Presented value: scope delivered, market data. (4) Explored total comp: equity, bonus, sign-on. (5) Non-monetary: flex, growth, scope. **Result**: Increased offer 10%; accepted with clear growth path....

Question 6

How would you retrieve the first and last order for each customer from a sales table?

Accepted Answer

To get first and last order per customer, use window functions MIN/MAX over order dates, or FIRST_VALUE/LAST_VALUE. Example: SELECT customer_id, order_id, order_date, FIRST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_id, LAST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_id FROM sales....

Wipro SQL Interview Questions

Companies asking these questions

All 6 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Wipro SQL Interview Questions

Companies asking these questions

All 6 Questions

More Interview Prep Guides

Get All Answers in PDF Format