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

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Myntra Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies