Meesho Data Analyst Interview question and answer (0-3 Years)

Meesho Data Analyst Interview Experience (0-3 Years)

Recently, I interviewed for a Data Analyst position at Meesho, and I encountered an engaging mix of Power BI and SQL questions. Below, I’ve outlined how I approached and answered these questions to help others preparing for similar roles.

---

Power BI Questions

1️⃣ Explain the concept of context transition in DAX and provide an example.

Context transition refers to the conversion of row context into filter context when using certain functions like CALCULATE. For example:

DAX

SalesTable = SUMMARIZE(
    Orders,
    Orders[CustomerID],
    "TotalSales", CALCULATE(SUM(Orders[SalesAmount]))
)

Here, CALCULATE changes the row context (specific customer) into a filter context, allowing aggregate functions like SUM to work accurately.

---

2️⃣ How would you optimize a complex Power BI report for faster performance?

Some key optimization techniques include:

• Reducing the model size: Remove unused columns and reduce the granularity of data.
• Efficient DAX formulas: Avoid complex and iterative measures; use variables to store intermediate results.
• Aggregations: Pre-aggregate data at the database level.
• Optimize visuals: Minimize the use of visuals like tables and matrices with large data.

---

3️⃣ Describe the process of creating and using calculation groups in Power BI.

Calculation groups allow reusing measures with different time-intelligence calculations:

1. Create a calculation group using Tabular Editor.
2. Define calculation items (e.g., YTD, MTD, QoQ).
3. Use these items across measures to apply consistent calculations.
   Example: Adding a "Year-To-Date" calculation to multiple measures using one group.

---

4️⃣ Explain how you would handle large datasets in Power BI without compromising performance.

• Use DirectQuery for real-time data and aggregations for high-level metrics.
• Implement incremental refresh for historical data updates.
• Use Power Query for data transformations to optimize data ingestion.
• Leverage partitioning for large tables.

---

5️⃣ What is a composite model in Power BI, and how can it be used effectively?

A composite model combines Import and DirectQuery modes, enabling flexibility. Example: Use Import for frequently queried data and DirectQuery for real-time updates.
Effective use: Linking aggregated historical sales (Import) with live inventory data (DirectQuery).

---

6️⃣ How does the USERELATIONSHIP function work, and when would you use it?

USERELATIONSHIP activates an inactive relationship in a model temporarily. Example:

DAX

TotalSales = CALCULATE(
    SUM(Sales[Amount]), 
    USERELATIONSHIP(Sales[OrderDate], Calendar[Date])
)

Use it when you need to analyze data using a non-default relationship.

---

7️⃣ Describe how to use Power Query M language for advanced data transformations.

Power Query M allows you to manipulate data effectively:

• Column transformations: Use Table.AddColumn.
• Pivot/unpivot: Use Table.Pivot or Table.Unpivot.
• Parameterization: Create reusable queries for dynamic filtering.

Example: Splitting a full name column into first and last names using Text.Split.

---

8️⃣ Explain the difference between CROSSFILTER and TREATAS in DAX.

• CROSSFILTER: Controls the direction of relationship filtering.

  DAX
  
  CALCULATE(SUM(Sales[Amount]), CROSSFILTER(Orders[OrderID], Sales[OrderID], BOTH))
  

• TREATAS: Applies a table as a filter without creating a relationship.

  DAX
  
  CALCULATE(SUM(Sales[Amount]), TREATAS({1, 2, 3}, Products[CategoryID]))
  

Use CROSSFILTER for modifying relationships and TREATAS for temporary filters.

---

SQL Questions

1️⃣ How would you optimize a slow-running query with multiple joins?

• Indexes: Add indexes to columns used in joins.
• Reduce result set: Filter data early using WHERE or JOIN conditions.
• Query execution plan: Analyze for bottlenecks.
• Avoid nested loops: Use temp tables or CTEs to simplify logic.

---

2️⃣ What is a recursive CTE, and can you provide an example of when to use it?

A recursive CTE is a query that references itself to traverse hierarchical data. Example: Employee hierarchy.

sql

WITH RecursiveCTE AS (
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveCTE;

---

3️⃣ Explain the difference between clustered and non-clustered indexes and when to use each.

• Clustered index: Sorts and stores data physically (e.g., PRIMARY KEY).
• Non-clustered index: Logical structure for faster lookups.
  Use clustered for unique columns (ID) and non-clustered for search queries.

---

4️⃣ Write a query to find the second highest salary in each department.

sql

WITH RankedSalaries AS (
    SELECT 
        DepartmentID, 
        Salary, 
        RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT DepartmentID, Salary
FROM RankedSalaries
WHERE Rank = 2;

---

5️⃣ How would you detect and resolve deadlocks in SQL?

• Detection: Use SQL Server Profiler or system views (sys.dm_tran_locks).
• Resolution:
  • Reduce lock time by using shorter transactions.
  • Use NOLOCK where appropriate.
  • Avoid cyclic dependencies in queries.

---

6️⃣ Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK.

Window functions perform calculations across a set of table rows:

• ROW_NUMBER: Assigns a unique number to rows.
• RANK: Assigns ranks with gaps.
• DENSE_RANK: Assigns ranks without gaps.

Example:

sql

SELECT 
    EmployeeID, 
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

---

7️⃣ Describe the ACID properties in database transactions and their significance.

• Atomicity: Entire transaction succeeds or fails.
• Consistency: Maintains data integrity.
• Isolation: Prevents transactions from interfering.
• Durability: Ensures data persists after commit.

---

8️⃣ Write a query to calculate a running total with partitions based on specific conditions.

sql

SELECT 
    DepartmentID, 
    EmployeeID, 
    Salary, 
    SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY JoinDate) AS RunningTotal
FROM Employees;

---

Final Thoughts

The Meesho interview thoroughly tested my skills in SQL, DAX, and Power BI. These questions required a strong understanding of fundamentals and real-world application. Sharing these insights will hopefully help others ace their interviews. Best of luck! 🚀

#Meesho #PowerBI #SQL #InterviewExperience #DataAnalyst