Question 1

Write a Python program to calculate total spending, identify top 5 users by spending, and find the most purchased product

Accepted Answer

**Pandas:** total = df['amount'].sum(); top_users = df.groupby('user_id')['amount'].sum().nlargest(5); most_purchased = df['product_id'].value_counts().index[0].

**SQL:** SUM, GROUP BY + ORDER BY + LIMIT. For top-5: ROW_NUMBER() or RANK() OVER (PARTITION BY 1 ORDER BY total DESC).

**Scalability:** For Myntra-scale: run in warehouse (Snowflake/BigQuery)—push compute....

Question 2

Advanced SQL with CTEs and Conditional Joins

Accepted Answer

**CTEs:** WITH cte AS (...) SELECT ... FROM cte. Readability, reuse, recursion. **Conditional join:** AND in ON, or CASE in ON. Multiple LEFT JOINs with different conditions.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) rn
  FROM events
)
SELECT r.*, u.name
FROM ranked r
LEFT JOIN users u ON r.user_id = u.id AND u.status = 'active'
WHERE r.rn = 1;

Question 3

Fact vs Dimension Table - categorize data

Accepted Answer

**Fact tables**: Measures (quantity, amount); granular events; append-only; FKs to dimensions; high row count. **Dimension tables**: Descriptors (product name, customer); lower row count; used for grouping/filtering. **Why separate**: Facts grow linearly; dimensions change slowly. Star schema optimizes analytics—filter/group by dimension, aggregate facts. **Scalability**: Facts partitioned by date; dimensions often fit in memory for broadcast....

Question 4

HAVING vs WHERE - explain

Accepted Answer

**WHERE**: Filters rows before GROUP BY; cannot use aggregates. **HAVING**: Filters after GROUP BY; can use aggregates. Example: SELECT dept, AVG(salary) FROM employees WHERE hire_date > '2020' GROUP BY dept HAVING AVG(salary) > 50K. WHERE reduces input; HAVING filters groups....

Question 5

Indexing - True/False question on indexes and query optimization

Accepted Answer

Indexing true/false: TRUE—Indexes speed up SELECT with WHERE/JOIN on indexed columns; indexes can slow down INSERT/UPDATE/DELETE (writes must update index); unique indexes enforce uniqueness; composite indexes support left-prefix queries. FALSE—Indexes always improve all queries (no: only relevant columns); more indexes always better (no: diminishing returns, write cost); indexes work on all data types equally (some types less effective)....

Question 6

REPARTITION vs COALESCE for managing partitions

Accepted Answer

REPARTITION: full shuffle; creates N partitions; use when changing partition count or key. COALESCE: reduces partitions without full shuffle; combines partitions; use before writes to reduce output files. repartition(200) vs coalesce(50): repartition spreads data; coalesce merges. Use coalesce when reducing; repartition when increasing or changing key. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 7

SELF JOIN Applications - scenarios like manager-employee relationships

Accepted Answer

Self-join: table joined to itself. Manager-employee: SELECT e.name AS employee, m.name AS manager FROM emp e LEFT JOIN emp m ON e.manager_id = m.emp_id. Other uses: compare rows (find duplicates), hierarchies (org chart), sequences (consecutive dates). Alias both instances. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 8

Slowly Changing Dimension (SCD) Type 4 - describe hybrid approach

Accepted Answer

SCD Type 4: hybrid—current in main dim, history in separate table. Main table: one row per key (Type 1 style). History table: all changes (Type 2 style). Query current: main table. Query history: history table. Balances performance (current) with full audit (history). Implement: main table overwrite; history table append on change. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 9

Solve medium-level SQL questions involving two tables with null values - LEFT JOIN, RIGHT JOIN, INNER JOIN

Accepted Answer

With nulls in join keys: INNER excludes null keys (no match). LEFT: keeps left rows, null for right when no match or when key is null. RIGHT: mirror of LEFT. Test: create small tables with 0, NULL, 1; run each join; observe. Use COALESCE in ON to normalize: ON COALESCE(a.key,-1)=COALESCE(b.key,-1). **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 10

Write a SQL query leveraging window functions and timestamps to identify updates over time

Accepted Answer

SELECT id, value, LAG(value) OVER (ORDER BY timestamp) AS prev_value, value - LAG(value) OVER (ORDER BY timestamp) AS delta FROM table ORDER BY timestamp. For change detection: SELECT *, LAG(column) OVER (PARTITION BY id ORDER BY updated_at) AS prev, CASE WHEN column != LAG(column) OVER (...) THEN 'changed' END AS change_flag. **Why**: LAG gives previous row; compare to detect changes....

Myntra Data Engineer Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 12 Questions

More Interview Prep Guides

Unlock All Expert Answers