Meesho Analytics Manager Interview Question

Topic – Meesho Analytics Manager Interview Question
Company Name – Meesho
Location – Remote
Salary – 45 to 60 LPA(10% stocks, depending on current and counter offer)
Experience Required – 7+ years
Expertise – Big Data Infrastructure, SQL and Problem Solving
Meesho Analytics Manager Interview Question

Number of Rounds – 3
Round 1 – SQL and Project (3 hours Hangout Interview)
Round 2 – Problem-solving and Project (2 Hours Hangout Interview)
Round 3 – Hiring Manager, Project and cultural fit (2 hours)

Round 1 – SQL and Project

There were a few basic queries and some detailed theoretical questions where your SQL knowledge was checked, but the main emphasis was on the projects that you have delivered in the past organisations.
SQL questions:-
For the advance level questions please practice the questions listed in the following post
Amazon most asked SQL questions

1. Get the columns first_name, last_name, and books_sold from the table books, but only where cars_sold is greater than the average number of cars sold.
The table books has three columns – first_name,last_name and books_sold
Ans –

SELECT first_name, last_name, cars_sold
FROM books
WHERE books_sold > (SELECT AVG (books_sold)
FROM books);

2. How would you partition a table in an optimal way?

Partitioning allows you to store parts of your table in their own logical space.
With partitioning, you want to divide up your rows based on how you access them.
If you partition your rows and you are still hitting all the partitions, it does you no good.
The goal is that when you query, you will only have to look at a subset of the data to get a result,
and not the whole table.

There are various ways to partition a database like
1)Range – Rows are partitioned based on the range of the column(like Age 10-20,20-30,30-40)
2) Hash – hashes a column and depending upon the result of the hash, has a different partition.
3) LIST, Key

3. Explain indexing in SQL with syntax and example.

An index can be used to efficiently find all rows matching some column in your query and
then walk through only that subset of the table to find exact matches. If you don’t have
indexes on any column in the WHERE clause, the SQL server has to walk through the whole table
and check every row to see if it matches, which may be a slow operation on big tables.

Creating an index involves the CREATE INDEX statement, which allows you to name the index,
to specify the table and which column or columns to index, and to indicate whether the index
is in an ascending or descending order.

Basic syntax
CREATE INDEX index_name ON table_name;

Single Column Index
CREATE INDEX index_name
ON table_name (column_name);

Unique Index
CREATE UNIQUE INDEX index_name
on table_name (column_name);

Following are the questions that were asked in all the three rounds (we are putting it at one place as there were a lot of overlaps in the questions)

A couple of projects were discussed in detail, the questions were asked around the project so, be very particular about the what you have written in the resume. I had a project on Customer Life Time Value (CLTV), so the questions were like:-
– How would you determine the Lifetime value of Customer for Amazon?
– What are the various parameters to consider while determining CLTV?
– Is CLTV predictive or historic or a combination of the two?
If CLV is being provided at the individual level, you want to make sure the CLV is predicted (i.e., includes the value you expect to get from him or her in the future), and not just historic. The problem with relying on historical profit/revenue is that you can misclassify recent customers. For example, if you acquired a customer last month, you cannot compare this customer to a customer acquired one year ago — unless you can predict the one-year behavior of the recent customer.

– Does it make sense to include or exclude super old customers?
It’s also important to consider how far back you should go to compute lifetime value. Imagine if you were a company as old as Coca Cola. Should you include customers in your model who started consuming the beverage back in 1920? Are those customers still relevant? This is an extreme example that I have used to prove a point, but how about a company like Apple? Should a customer who joined to buy the original Mac be considered in the CLV model? There is no established answer to this, but generally, we suggest considering a relevant business window that goes back at least a few years. This will help the model understand seasonality, but is not so far back that the business and customers are no longer relevant.

Other questions that were generic in nature and asked in the 3 rounds were:-
– Define Normal distribution with example
– What is IQR?
– Can a forecasting model be 99% accurate but still be of no use? Give example(if any)?
– Why did you use Linear Regression for a forecasting model?
– Assumptions of Linear Regression

All of the above questions and more than 2000 questions are completely solved in the list of books given below, do explore 🙂

The Data Monk Interview Books – Don’t Miss

Now we are also available on our website where you can directly download the PDF of the topic you are interested in. On Amazon, each book costs ~299, on our website we have put it at a 60-80% discount. There are ~4000 solved interview questions prepared for you.

10 e-book bundle with 1400 interview questions spread across SQL, Python, Statistics, Case Studies, and Machine Learning Algorithms – Ideal for 0-3 years experienced candidates

23 E-book with ~2000 interview questions spread across AWS, SQL, Python, 10+ ML algorithms, MS Excel, and Case Studies – Complete Package for someone between 0 to 8 years of experience (The above 10 e-book bundle has a completely different set of e-books)

12 E-books for 12 Machine Learning algorithms with 1000+ interview questions – For those candidates who want to include any Machine Learning Algorithm in their resume and to learn/revise the important concepts. These 12 e-books are a part of the 23 e-book package

Individual 50+ e-books on separate topics

Important Resources to crack interviews (Mostly Free)

There are a few things that might be very useful for your preparation

The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews with Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics managers, etc.
Go through the watchlist which makes you uncomfortable:-

All the list of 200 videos
Complete Python Playlist for Data Science
Company-wise Data Science Interview Questions – Must Watch
All important Machine Learning Algorithm with code in Python
Complete Python Numpy Playlist
Complete Python Pandas Playlist
SQL Complete Playlist
Case Study and Guesstimates Complete Playlist
Complete Playlist of Statistics

Keep Learning !!

Thanks,