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....

Myntra Data Engineer Interview Questions

+3 More Questions with Expert Answers

Other Companies

Myntra Data Engineer Interview Questions

+3 More Questions with Expert Answers

Other Companies