SQL Tricky Interview Questions – Day 1

SQL Tricky Interview Questions
SQL, which stands for Structured Query Language, is a domain-specific programming language used for managing and manipulating relational databases. It is the standard language for interacting with relational database management systems (RDBMS), which are software systems designed to store, retrieve, and manage structured data.

Here are some key aspects of SQL:

  1. Data Retrieval: SQL allows users to retrieve data from a database using queries. With SQL queries, you can specify the data you want to retrieve, filter it based on certain criteria, and sort it in a particular order.
  2. Data Manipulation: SQL provides commands for inserting, updating, and deleting data in a database. These commands allow users to modify the content of a database while maintaining data integrity.
  3. Schema Definition: SQL allows users to define the structure of a database, including tables, columns, data types, constraints, and relationships between tables. This is done using Data Definition Language (DDL) statements.
  4. Data Modification: SQL provides Data Manipulation Language (DML) statements, such as INSERT, UPDATE, and DELETE, to modify the data stored in database tables.
  5. Data Querying: SQL’s SELECT statement is used to query and retrieve data from one or more database tables. Users can use various clauses and operators to filter, aggregate, and manipulate the retrieved data.
  6. Transaction Control: SQL supports transactions, which are sequences of one or more SQL statements that are treated as a single unit of work. Transactions ensure data consistency and integrity by allowing operations to be either fully completed or fully rolled back in case of errors.
  7. Indexing: SQL allows the creation of indexes on database tables to improve query performance. Indexes help the database system quickly locate and retrieve data rows.
  8. Security: SQL provides mechanisms for controlling access to the database, including user authentication and authorization. Database administrators can grant or revoke permissions to specific users or roles.
  9. Reporting: SQL is commonly used for generating reports from a database. It allows users to aggregate and summarize data, perform calculations, and format results for presentation.

SQL is used in a wide range of applications and industries, from web development to business intelligence and data analysis. Popular relational database management systems that use SQL as their query language include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite, among others. SQL is an essential skill for database administrators, data analysts, software developers, and anyone working with structured data in a relational database.
SQL Tricky Interview Questions

SQL Tricky Interview Questions

Day 1 – SQL Tricky Interview Questions

1.Common Table Expressions (CTEs):

A CTE is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs make complex queries more readable and maintainable.
Use CTEs when you need to break down a complex query into smaller, more understandable parts

2.Subqueries vs. JOINs:

-Subqueries are nested queries within another query and are used to retrieve data for further processing.
-JOINs combine rows from two or more tables based on a related column.
-Use subqueries when you need to retrieve a single value or a small set of values, and use JOINs when you need to combine data from multiple tables.

3.How do the SQL commands flow at the back end?

Ans.
Order of execution for an SQL query
1) FROM, including JOINs
2) WHERE
3) GROUP BY
4) HAVING
5) WINDOW Functions
6) SELECT
7) DISTINCT
8) UNION
9) ORDER BY
10) LIMIT AND OFFSET

4.Write a SQL query to find all the student names Nitin in a table

SELECT name
FROM student
WHERE lower(name) like ‘%nitin%’

Now the trick is to make sure you convert the name in lower for the complete column

What will get you wrong output?
name like ‘%nitin%’

As this will not capture Nitin, niTin, etc.

5.Write a query to get all the student with name length 10, starting with K and ending with z.

SELECT name
FROM student
WHERE length(name)=10 and lower(name) like ‘k%z’

7.ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of database transactions.
Atomicity ensures that a transaction is treated as a single, indivisible unit.
Consistency guarantees that a transaction brings the database from one consistent state to another.
Isolation ensures that transactions are executed independently.
Durability guarantees that once a transaction is committed, its effects are permanent.

8.Convert ‘2023-10-15′ to ’15-Oct-2023’.
You can use the TO_CHAR function to format the date:

SELECT TO_CHAR(TO_DATE('2023-10-15', 'YYYY-MM-DD'), 'DD-Mon-YYYY') AS formatted_date;

9.Calculate the date that is 90 days from today.
Use the CURRENT_DATE and INTERVAL for date arithmetic:

SELECT CURRENT_DATE + INTERVAL '90 days' AS future_date;

10.Determine the day of the week for ‘2023-11-20’.
Use the TO_CHAR function to extract the day of the week

SELECT TO_CHAR(TO_DATE('2023-11-20', 'YYYY-MM-DD'), 'Day') AS day_of_week;

11.Display ‘N/A’ for employees with no ‘hire_date’.
Use the COALESCE function to provide a default value for NULL dates:

SELECT COALESCE(TO_CHAR(hire_date, 'YYYY-MM-DD'), 'N/A') AS formatted_hire_date FROM employees;

12.Convert a timestamp from one time zone to another.
Use the AT TIME ZONE clause to perform the conversion:

SELECT timestamp_column AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS converted_timestamp 
FROM table_name;

TDM Youtube Video – Day 1

Go through the below 3 videos without fail on Day 1
Fetch Duplicate Records in SQL
LIKE operator in SQL
Substring and Replace in SQL

TDM Instagram Post – Day 1 (Click and Follow the page)

How The Data Monk can help you?

We have created products and services on different platforms to help you in your Analytics journey irrespective of whether you want to switch to a new job or want to move into Analytics.

Our services

  1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
    Link – The Data E-shop Page
  4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
    Link – The Data Monk Instagram page
  5. Mock Interviews
    Book a slot on Top Mate
  6. Career Guidance/Mentorship
    Book a slot on Top Mate
  7. Resume-making and review
    Book a slot on Top Mate 

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

Business Analyst -> 1250+ Most Asked Interview Questions

Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

Full Stack Analytics Professional2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000


How to avail of this offer?
Send a mail to nitinkamal132@gmail.com

How to do Exploratory Data Analysis in Python?

How to do Exploratory Data Analysis in Python?
Exploratory Data Analysis (EDA) is an essential step in the data analysis process to understand the structure, patterns, and relationships within your dataset. Python offers various libraries, including Pandas, Matplotlib, Seaborn, and Plotly, to perform EDA effectively.
How to do Exploratory Data Analysis in Python?

Exploratory Data Analysis in Python

Here’s a step-by-step guide on how to do EDA in Python using sample code

Import Libraries: Start by importing the necessary Python libraries

import pandas as pd 
import numpy as np 
import matplotlib.pyplot as plt 
import seaborn as sns

Load Your Dataset and Data Inspection: Read your dataset into a Pandas DataFrame. Replace "your_data.csv" with the path to your dataset.

df = pd.read_csv("your_data.csv")
print(df.head())
print(df.info())
print(df.describe())
print(df.describe())

Handling Missing Data: Check for missing values and decide how to handle them (e.g., fill, drop, or interpolate).

# Check for missing values 
print(df.isnull().sum()) 
# Fill missing values 
df.fillna(method='ffill', inplace=True) # Example: Forward fill

dropna(): Removing rows or columns with missing values.
fillna(): Filling missing values with specified values or methods.
interpolate(): Interpolating missing values.
replace(): Replacing values with other values.

Data Visualization

df.hist(figsize=(10, 8))
plt.show()

corr_matrix = df.corr()
sns.heatmap(corr_matrix, annot=True, cmap='coolwarm')
plt.show()

sns.boxplot(data=df[['column_name']])
plt.show()

sns.countplot(data=df, x='categorical_column')
plt.show()

Feature Engineering: Create new features or transform existing ones to make them more suitable for analysis.

Example: Creating a new feature from existing columns
df['new_feature'] = df['feature1'] + df['feature2']

Let’s look at the top two techniques of feature engineering

Binning/Discretization: Convert continuous features into categorical bins or intervals. This can be useful when the relationship between the feature and the target variable is non-linear.

df['age_group'] = pd.cut(df['age'], bins=[0, 18, 35, 60, np.inf], labels=['child', 'young_adult', 'adult', 'senior'])

One-Hot Encoding: Convert categorical variables into binary (0/1) columns, one for each category. This is necessary for many machine learning algorithms.

df = pd.get_dummies(df, columns=['gender'], drop_first=True)

Data Insights: Based on your visualizations and data exploration, draw initial insights and hypotheses about your data.

Further Analysis: Depending on your dataset and objectives, you may want to perform additional analyses such as time series analysis, clustering, or predictive modeling.

Documentation: Document your findings, code, and visualizations in a clear and organized manner, which can be shared with others or used for reference in the future.

EDA is an iterative process, and you may need to revisit previous steps as you gain more insights and refine your analysis. The above steps provide a basic framework for EDA in Python, but the specific analysis and visualizations will vary depending on your dataset and research questions.

How The Data Monk can help you?

We have created products and services on different platforms to help you in your Analytics journey irrespective of whether you want to switch to a new job or want to move into Analytics.

Our services

  1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
    Link – The Data E-shop Page
  4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
    Link – The Data Monk Instagram page
  5. Mock Interviews
    Book a slot on Top Mate
  6. Career Guidance/Mentorship
    Book a slot on Top Mate
  7. Resume-making and review
    Book a slot on Top Mate 

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

Business Analyst -> 1250+ Most Asked Interview Questions

Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

Full Stack Analytics Professional2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000


How to avail of this offer?
Send a mail to nitinkamal132@gmail.com

50 Most Important Pandas Functions

50 Most Important Pandas Functions
What is Pandas and their key features?

Pandas is an open-source Python library that provides data structures and data analysis tools for working with structured data. It is one of the most widely used libraries in the field of data science and data analysis. Pandas is designed to simplify and accelerate data manipulation and analysis tasks, making it an essential tool for data professionals, including data scientists, data analysts, and researchers.

Key features of Pandas include:

  1. DataFrame: Pandas introduces a powerful data structure called the DataFrame, which is a two-dimensional, labeled data structure with columns of potentially different data types. It resembles a spreadsheet or a SQL table, and it allows you to store, manipulate, and analyze data efficiently.
  2. Series: Pandas also provides the Series data structure, which is a one-dimensional array-like object. Series can be thought of as a single column of a DataFrame, and they are useful for working with single columns of data.
  3. Data Import and Export: Pandas supports various file formats for data import and export, including CSV, Excel, SQL databases, JSON, and more. This makes it easy to read data from external sources and save your analysis results.
  4. Data Cleaning: Pandas offers a wide range of functions and methods for cleaning and preprocessing data, such as handling missing values, removing duplicates, and transforming data.
  5. Data Selection and Filtering: You can easily select and filter data using Pandas, whether it’s based on specific conditions or by column and row labels or indices.
  6. Data Aggregation and Grouping: Pandas allows you to perform data aggregation and summarization operations, including grouping data by specific criteria and applying aggregation functions like sum, mean, count, etc.
  7. Data Visualization: While Pandas itself is not a data visualization library, it seamlessly integrates with popular visualization libraries like Matplotlib and Seaborn, enabling you to create various plots and charts to visualize your data.
  8. Time Series Analysis: Pandas includes robust support for time series data, making it particularly useful for analyzing temporal data.
  9. Powerful Indexing: Pandas offers flexible indexing capabilities, including hierarchical indexing (MultiIndex), which allows for complex data organization.

Pandas is an integral part of the Python data ecosystem and is often used in conjunction with other libraries such as NumPy for numerical computations and Matplotlib or Seaborn for data visualization. It provides a user-friendly and efficient way to work with data, making it a go-to choice for data analysis and manipulation tasks in Python.

Let’s go through the 50 Most Important Pandas Functions

50 Most Important Pandas Functions

Dataframe Creation and Loading:

  1. pandas.DataFrame(): Creating a DataFrame.
  2. read_csv(): Reading data from a CSV file into a DataFrame.
  3. read_excel(): Reading data from an Excel file into a DataFrame.
  4. from_dict(): Creating a DataFrame from a dictionary.
  5. from_records(): Creating a DataFrame from a list of records.
  6. pd.concat(): Combining multiple DataFrames.

Data Exploration:

  1. head(): Viewing the first few rows of a DataFrame.
  2. tail(): Viewing the last few rows of a DataFrame.
  3. info(): Displaying information about the DataFrame.
  4. describe(): Generating summary statistics of numeric columns.
  5. shape: Getting the dimensions (rows and columns) of a DataFrame.
  6. columns: Accessing the column names of a DataFrame.
  7. dtypes: Getting data types of columns.

Data Selection and Filtering:

  1. loc[]: Accessing rows and columns by label.
  2. iloc[]: Accessing rows and columns by integer index.
  3. at[]: Accessing a single element by label.
  4. iat[]: Accessing a single element by integer index.
  5. isin(): Filtering rows based on a condition.
  6. query(): Filtering rows using a query expression.

Data Manipulation:

  1. drop(): Removing rows or columns from a DataFrame.
  2. rename(): Renaming columns or indices.
  3. sort_values(): Sorting a DataFrame by one or more columns.
  4. fillna(): Filling missing values in a DataFrame.
  5. drop_duplicates(): Removing duplicate rows.
  6. apply(): Applying a function to each element or row of a DataFrame.
  7. replace(): Replacing values in a DataFrame.
  8. pivot_table(): Creating a pivot table for data aggregation.

Grouping and Aggregation:

  1. groupby(): Grouping data by one or more columns for aggregation.
  2. agg(): Applying aggregation functions (e.g., sum, mean) to grouped data.
  3. count(): Counting non-null values in each group.
  4. sum(): Calculating the sum of values in each group.
  5. mean(): Calculating the mean of values in each group.
  6. max() and min(): Finding maximum and minimum values in each group.

Data Cleaning:

  1. dropna(): Removing rows or columns with missing values.
  2. fillna(): Filling missing values with specified values or methods.
  3. interpolate(): Interpolating missing values.
  4. replace(): Replacing values with other values.

Merging and Joining:

  1. merge(): Merging two DataFrames based on common columns.
  2. concat(): Concatenating DataFrames vertically or horizontally.

String Operations:

  1. str.contains(): Checking for substring existence in string columns.
  2. str.split(): Splitting string columns into multiple columns.
  3. str.strip(): Removing leading and trailing whitespaces.

Datetime Handling:

  1. to_datetime(): Converting a column to datetime format.
  2. dt.year, dt.month, etc.: Extracting date components.

Reshaping Data:

  1. melt(): Unpivoting a DataFrame.
  2. stack() and unstack(): Pivoting and unstacking data.
  3. pivot(): Creating a pivot table from long data.

Statistical Analysis:

  1. corr(): Calculating the correlation between columns.
  2. cov(): Calculating the covariance between columns.
  3. value_counts(): Counting unique values in a column.

How The Data Monk can help you?

We have created products and services on different platforms to help you in your Analytics journey irrespective of whether you want to switch to a new job or want to move into Analytics.

Our services

  1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
    Link – The Data E-shop Page
  4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
    Link – The Data Monk Instagram page
  5. Mock Interviews
    Book a slot on Top Mate
  6. Career Guidance/Mentorship
    Book a slot on Top Mate
  7. Resume-making and review
    Book a slot on Top Mate 

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

Business Analyst -> 1250+ Most Asked Interview Questions

Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

Full Stack Analytics Professional2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000


How to avail of this offer?
Send a mail to nitinkamal132@gmail.com

10 Most Asked SQL Interview Questions

10 Most Asked SQL Interview Questions will help you understand the type of questions asked in SQL interviews in the Analytics domain.
Below are the 10 Most Asked SQL Interview Questions.
Here are a few FREE and paid resources that will help you in making career in Analytics

The Data Monk – We have 2000+ solved interview questions, you can explore all of these for free

The Data Monk Youtube channel
We have ~200 Videos created only based on the most asked topics in SQL, Python, ML, Statistics, Case Stud,ies and other topics. Do subscribe and watch 1-2 video daily.

Link to Channel – The Data Monk

The Data Monk E-book

Full Stack Analytics Professional – Full Stack Analytics Professional – 2200 Most Asked Interview Questions
Data Analyst – 1100+ Most Asked Data Analyst Interview Questions
Business Analyst – 1250+ Most Asked Business Analyst Interview Questions
Data Scientist – 23 e-books covering all the ML Algorithms Interview Questions

The Data Monk Instagram Channel – Where we regularly post about the most asked definitions and questions


Question 1: Suppose you have a database with two tables:

  1. Customers
    • Columns: CustomerID (Primary Key), FirstName, LastName, Email
  2. Orders
    • Columns: OrderID (Primary Key), CustomerID (Foreign Key referencing Customers), OrderDate, TotalAmount

Write an SQL query to retrieve the names and total amounts spent by customers who have made at least two orders, along with the number of orders they’ve made.

Answer 1:
SELECT
    c.FirstName,
    c.LastName,
    COUNT(o.CustomerID) AS NumberOfOrders,
    SUM(o.TotalAmount) AS TotalAmountSpent
FROM
    Customers c
JOIN
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID
HAVING
    COUNT(o.CustomerID) >= 2;

Question 2: Consider a table named Products:

  • Columns: ProductID (Primary Key), ProductName, Category, Price

Write an SQL query to find the average price of products in each category, along with the total number of products in that category.

Answer 2:
SELECT
    Category,
    AVG(Price) AS AveragePrice,
    COUNT(*) AS TotalProducts
FROM
    Products
GROUP BY
    Category;

Question 3: You have a table named Employees:

  • Columns: EmployeeID (Primary Key), FirstName, LastName, Department, Salary

Write an SQL query to find the top 5 earning employees in each department.

Answer 3:
SELECT
    e1.Department,
    e1.FirstName,
    e1.LastName,
    e1.Salary
FROM
    Employees e1
WHERE
    5 > (
        SELECT COUNT(DISTINCT e2.Salary)
        FROM Employees e2
        WHERE e2.Department = e1.Department AND e2.Salary > e1.Salary
    );

Question 4 :
Suppose you have a table named Employees with columns: EmployeeID, FirstName, LastName, and Department. Write an SQL query to retrieve the employees who have the same first names and are in the same department.

Answer 4:
SELECT e1.EmployeeID, e1.FirstName, e1.LastName, e1.Department
FROM Employees e1
JOIN Employees e2 ON e1.FirstName = e2.FirstName AND e1.EmployeeID <> e2.EmployeeID
                  AND e1.Department = e2.Department;


Question 5:
Given a table named Orders with columns: OrderID, OrderDate, and TotalAmount, write an SQL query to find the date with the highest total amount of orders.

Answer 5:
SELECT OrderDate, SUM(TotalAmount) AS Total 
FROM Orders 
GROUP BY OrderDate 
ORDER BY Total DESC 
LIMIT 1;

Question 6:
Consider a table named Books with columns: BookID, Title, AuthorID, and PublicationYear, and a table named Authors with columns: AuthorID, FirstName, and LastName. Write an SQL query to retrieve the top 3 authors with the highest average publication years.

Answer 6:
SELECT a.AuthorID, a.FirstName, a.LastName, AVG(b.PublicationYear) AS AvgPublicationYear 
FROM Authors a 
JOIN Books b ON a.AuthorID = b.AuthorID 
GROUP BY a.AuthorID, a.FirstName, a.LastName 
ORDER BY AvgPublicationYear DESC 
LIMIT 3;

Question 7:
Suppose you have a table named Transactions with columns: TransactionID, UserID, TransactionDate, and Amount. Write an SQL query to find the UserID of the user who made the highest total amount of transactions.

Answer 7:
SELECT UserID 
FROM Transactions 
GROUP BY UserID 
ORDER BY SUM(Amount) 
DESC LIMIT 1;

Question 8:
Given a table named Logs with columns: LogID, UserID, and Activity, write an SQL query to find the UserID that has performed all possible activities.

Answer 8:
SELECT UserID 
FROM Logs 
GROUP BY UserID 
HAVING COUNT(DISTINCT Activity) = 
(
   SELECT COUNT(DISTINCT Activity) FROM Logs
);

Question 9: What are the Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:
NOT NULL – Restricts NULL value from being inserted into a column.
CHECK – Verifies that all values in a field satisfy a condition.
DEFAULT – Automatically assigns a default value if no value has been specified for the field.
UNIQUE – Ensures unique values are inserted into the field.
INDEX – Indexes a field providing faster retrieval of records.
PRIMARY KEY – Uniquely identifies each record in a table.
FOREIGN KEY – Ensures referential integrity for a record in another table.

Question10: How do the SQL commands flow at the back end?

Order of execution for an SQL query

1) FROM, including JOINs
2) WHERE
3) GROUP BY
4) HAVING
5) WINDOW Functions
6) SELECT
7) DISTINCT
8) UNION
9) ORDER BY
10) LIMIT AND OFFSET

Case Study for Analytics Interview

Case Study Statement – Suppose you work for Amazon, suggest top 20 KPIs that you should have on a CXO level dashboard

Sales and Revenue Metrics:

  1. Total Revenue: The total amount of money generated from all sales on the platform.
  2. Gross Merchandise Volume (GMV): The total value of goods sold through the platform, including Amazon’s and third-party sellers’ sales.
  3. Average Order Value (AOV): The average amount spent by customers in each order.
  4. Conversion Rate: The percentage of website visitors who make a purchase.
  5. Customer Lifetime Value (CLTV): The predicted total value a customer will generate over their lifetime as an Amazon customer.

Operational Metrics:

6. Order Fulfillment Rate: The percentage of orders fulfilled on time and accurately.

  1. Inventory Turnover: The rate at which inventory is sold and replaced within a specific period.
  2. Fulfillment Center Utilization: The capacity utilization of Amazon’s fulfillment centers.
  3. Return Rate: The percentage of orders returned by customers.

Customer Experience Metrics:

10. Customer Satisfaction (CSAT): Customer satisfaction rating based on post-purchase surveys.

  1. Net Promoter Score (NPS): A measure of customer loyalty and willingness to recommend Amazon to others.
  2. Customer Retention Rate: The percentage of customers who make repeat purchases.

Marketplace Metrics:

13. Number of Sellers: The total number of third-party sellers on Amazon’s marketplace.

  1. Third-Party Seller Sales: The percentage of overall sales generated by third-party sellers.
  2. Seller Feedback Rating: Ratings and feedback given by customers to third-party sellers.

Digital Metrics:

16. Amazon Web Services (AWS) Revenue: Revenue generated from Amazon’s cloud computing platform.

  1. Amazon Prime Membership Growth: The growth rate of Amazon Prime subscriptions.
  2. Digital Content Consumption: Metrics related to e-books, video streaming, and other digital content.

Logistics and Delivery Metrics:
19. Delivery Speed: Average time taken to deliver products to customers.

  1. Fulfillment Costs: Costs associated with warehousing, packaging, and shipping.

AirBnB Data Scientist Interview Questions

AirBnB Data Scientist Interview Questions
Company Name –Airbnb
Designation – Data Science
Salary – 15 to 22 LPA depending upon current CTC and graduation college
Interview Year – 2023
AirBnB Data Scientist Interview Questions

AirBnB Data Scientist Interview Questions
AirBnB Data Scientist Interview Questions

Following is a set of questions asked in the interview at AirBnB, we request you to attempt these questions

Round 1 -Technical Interview

Topics covered –Application Data science
Mode of interview –Meet
Duration – 90 Minutes
Level of Questions –Hard
Design a recommender system for Airbnb listings.
Which tables and indexes do you need in a SQL db to manage chat threads?
How would you measure the effectiveness of our operations team?
We see a dip in page views yesterday. How would you investigate what happened?

Round 2 -Technical Interview

Topics covered –Applied Machine Learning
Mode of interview –Meet
Duration – 90 Minutes
Level of Questions –Hard

If Airbnb wanted to open a new office, how would you determine how many meeting rooms to build into it?
How would you go about designing a metric for customer service calls?
What would happen if you were to include another X in your regression model?
An important metric goes down, how would you dig into the causes?


Round 3 –Technical Interview
Topics-Machine Learning
Mode of Interview- Meet
Duration 1 hour
Level of questions- Medium

A few questions from this round:
How does boosting work
What are the different error metrics employed in machine learning?

How would you normalize data ?

What is an ROC curve?


Round 4 – Hiring Manager Round
Topics covered: Resume
Mode: Hangout
Duration –1 hours
Level of Questions – Medium

A few questions below from this round:

Tell me about a time when you faced a challenge, how did you overcome it.

How have you made someone outside your immediate social circle feel that they belong?”.

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,

OYO Data Analyst Interview Questions

OYO Data Analyst Interview Questions

Target job roles – Business Analyst, Data Analyst, Data Scientist, Business Intelligence Engineer, Product Analyst, Machine Learning Engineer, Data Engineer

Target Companies – FAANG and only product-based companies

CTC offered –
12 to 20 LPA for Level 1 (0 to 4 YOE)
20 to 35 LPA for Level 2 (Senior level – 4 to 7 YOE )
35 to 50 LPA for Level 3 (Team Lead or Manager – 7 to 9 YOE)
50 to 80 LPA for Level 4 (Manager or Senior Manager – 9 to 12 YOE)


Tools and Technologies required
SQL – 9/10
Python – 7/10
Visualization tool (Power BI or Tableau) – Good to have
Machine Learning Algorithm – Expert in at least a couple of algorithms (if going for Data Science role)

OYO Data Analyst Interview Questions

Why The Data Monk?

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000


How to avail of this offer?
Send a mail to nitinkamal132@gmail.com

OYO Data Analyst Interview Questions
OYO Data Analyst Interview Questions

OYO Data Analyst Interview Questions

Company – OYO
Designation – Data Analyst

Year of Experience required – 0 to 4 years
Technical expertise – SQL, Python, Case Study, and Statistics
Salary offered – 10 to 18 LPA (no Stocks, 10% variable) – 80% hike

Number of Rounds – 4

OYO SQL Interview Questions

There were 10+ SQL questions, 6-7 easy/theoretical , a couple of medium problem and 1 hard problem.
The hard problem was picked directly from Hacker Rank, so practice all the problems.
The medium difficulty problems were like the one give below:

You have data on people have applied for a lottery ticket. The data consists of their name and ticket number. You have to choose winners by selecting the people present in the alternate rows (the first winner starting from row number 3). Write a query to make things easy to select the winners.

select *
from (select name, ROW_NUMBER() over (order by ticket_no) as srNo
from db) t
where (t.srNo % 2) = 1

Find all the students who either are male or live in Mumbai ( have Mumbai as a part of their address).
Select name
From students
Where lower(gender) in (‘male’,’m’)
Or lower(address) = ‘%mumbai%’

Can you join two table without any common column?
Yes we can do cross join without any common column.
Eg: We have Roll Number, Name of Students in Table A and their Class (let’s say 5th) in Table B.
21
We will use cross join to append class against each student.

SELECT B.CLASS,A.ID,A.NAME
FROM A, B
WHERE 1=1


Select case when null=null then ‘Amit’ else ‘Rahul’ end from dual. What will be the output of the above query?
The Null value has a memory reference.2 Null values cannot have same memory Reference. So output will be ‘Rahul’.

List the different types of relationships in SQL.
There are different types of relations in the database:
One-to-One – This is a connection between two tables in which each record in one table corresponds to the maximum of one record in the other.
One-to-Many and Many-to-One – This is the most frequent connection, in which a record in one table is linked to several records in another.
Many-to-Many – This is used when defining a relationship that requires several instances on each sides.
Self-Referencing Relationships – When a table has to declare a connection with itself, this is the method to employ.

What are the differences between OLTP and OLAP?
OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system.

What is the usage of the NVL() function?
You may use the NVL function to replace null values with a default value. The function returns the value of the second parameter if the first parameter is null. If the first parameter is anything other than null, it is left alone.


We have put all the 250 most asked questions in our SQL Interview Questions e-book. Do check it out

OYO Case Study Questions

Case Study – Suggest as many important KPIs as possible that you want to put on the CXOs dashboard

Following were the suggested KPIs

  1. Average Daily Rate (ADR)
  2. Occupancy rate
  3. Revenue per Available Room (RevPAR)
  4. Gross Operating Profit per Available Room (GOPPAR)
  5. Average Length of Stay (ALOS)
  6. Customer Acquisition Cost (CAC)
  7. Customer Lifetime Value (CLV)
  8. Net Promoter Score (NPS)
  9. Online Reputation Score (ORS)
  10. Room Revenue Contribution by Channel
  11. Website Conversion Rate
  12. Direct Booking Ratio
  13. Repeat Guest Ratio
  14. Housekeeping Productivity Ratio
  15. Employee Turnover Rate
  16. Revenue per Employee (RPE)
  17. Cost per Occupied Room (CPOR)
  18. Cost per Available Room (CPAR)
  19. Total Revenue by Property
  20. Total Expenses by Property

We have 100 case studies and guesstimate completely solved that are repeatedly asked in Analytics Interview. Do check out.

OYO Statistics and Python Interview Questions

I had a couple of projects on Machine Learning, so a few questions were asked on statistics

1.What is Skewness? 

  • Skewness is a measure of the asymmetry of a distribution. This value can be positive or  negative. 
  • A negative skew indicates that the tail is on the left side of the distribution, which extends  towards more negative values. 
  • A positive skew indicates that the tail is on the right side of the distribution, which extends  towards more positive values. 
  • A value of zero indicates that there is no skewness in the distribution at all, meaning the  distribution is perfectly symmetrical. 

2. What is Kurtosis? 

Kurtosis is a measure of whether or not a distribution is heavy-tailed or light-tailed relative to  a normal distribution.

• The kurtosis of a normal distribution is 3. 

• If a given distribution has a kurtosis less than 3, it is said to be platykurtic, which means it  tends to produce fewer and less extreme outliers than the normal distribution. 

• If a given distribution has a kurtosis greater than 3, it is said to be leptokurtic, which means it  tends to produce more outliers than the normal distribution. 

3.How are covariance and correlation different from one another? 

Covariance measures how two variables are related to each other and how one would vary  with respect to changes in the other variable. If the value is positive, it means there is a direct  relationship between the variables and one would increase or decrease with an increase or decrease  in the base variable respectively, given that all other conditions remain constant. 

Correlation quantifies the relationship between two random variables and has only three specific  values, i.e., 1, 0, and -1. 

1 denotes a positive relationship, -1 denotes a negative relationship, and 0 denotes that the two  variables are independent of each other. 

4.What is Multicollinearity ? 

Multicollinearity occurs when two or more independent variables are highly correlated with  one another in a regression model. This means that an independent variable can be predicted from  another independent variable in a regression model.

5.What is VIF? 

Variance inflation factor (VIF) is a measure of the amount of multicollinearity in a set of  multiple regression variables. In general, a VIF above 5 indicates high correlation and is cause for  concern. Some authors suggest a more conservative level of 2.5 or above and it depends on the  situation. 

6.What is a confusion matrix and why do you need it? 

Confusion matrix is a table that is frequently used to illustrate the performance of a  classification model i.e., classifier on a set of test data for which the true values are well-known. It  allows us to visualize the performance of an algorithm/model. It allows us to easily identify the  confusion between different classes. It is used as a performance measure of a model/algorithm. It is  summary of predictions on a classification model. 

7.What do you mean when you say “Strings are immutable”?

Strings in Python are immutable i.e you can not change the defined string.

You can not change a part of the string, as it is immutable.

8.Are lists mutable ?
Lists are mutable i.e. you can change the values already present in the list.

9.Is dictionary zero indexed? Can we pull something like Team[0] from the above example?

The whole purpose of having a dictionary is  that you can have your own index i.e. key. So, to answer the question, Dictionary is not zero indexed.

You can not use the basic index thing example, you can not use Team[0] to pull the first value because you have already specified an index to all the values

10.What is the function range() ?

Range(10) will get you numbers from 0 to 9. But you need to put this range in some data type. Suppose you want to put this in a list.

There were a 2-3 more questions on Python, mostly around for loop and pattern printing.

All the questions asked above and in 50+ Analytics companies are covered head to toe in our 2200 Interview Questions to become full stack Analytics Professional book. Do check out

The Data Monk Product and Services

  1. Youtube Channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
    Link – The Data E-shop Page
  4. Mock Interviews
    Book a slot on Top Mate
  5. Career Guidance/Mentorship
    Book a slot on Top Mate
  6. Resume-making and review
    Book a slot on Top Mate 

The Data Monk e-book Bundle 

1.For Fresher to 7 Years of Experience
2000+ interview questions on 12 ML Algorithm,AWS, PCA, Data Preprocessing, Python, Numpy, Pandas, and 100s of case studies

2. For Fresher to 1-3 Years of Experience
Crack any analytics or data science interview with our 1400+ interview questions which focus on multiple domains i.e. SQL, R, Python, Machine Learning, Statistics, and Visualization

3.For 2-5 Years of Experience
1200+ Interview Questions on all the important Machine Learning algorithms (including complete Python code) Ada Boost, CNN, ANN, Forecasting (ARIMA, SARIMA, ARIMAX), Clustering, LSTM, SVM, Linear Regression, Logistic Regression, Sentiment Analysis, NLP, K-M

Flipkart Business Analyst Interview Questions

Flipkart Business Analyst Interview Questions
We have been curating interview questions for the Top Product based companies. Following are the interview questions shared so far:-

We have 100 case studies and guesstimate completely solved that are repeatedly asked in Analytics Interview. Do check it out.

Why Analytics as a Career?

Target job roles – Business Analyst, Data Analyst, Data Scientist, Business Intelligence Engineer, Product Analyst, Machine Learning Engineer, Data Engineer

Target Companies – FAANG and only product-based companies

CTC offered –
12 to 20 LPA for Level 1 (0 to 4 YOE)
20 to 35 LPA for Level 2 (Senior level – 4 to 7 YOE )
35 to 50 LPA for Level 3 (Team Lead or Manager – 7 to 9 YOE)
50 to 80 LPA for Level 4 (Manager or Senior Manager – 9 to 12 YOE)

Tools and Technologies required
SQL – 9/10
Python – 7/10
Visualization tool (Power BI or Tableau) – Good to have
Machine Learning Algorithm – Expert in at least a couple of algorithms (if going for Data Science role)

Why The Data Monk?

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000


How to avail of this offer?
Send a mail to nitinkamal132@gmail.com

Flipkart Business Analyst Interview Questions
Flipkart Business Analyst Interview Questions

Flipkart Business Analyst Interview Questions

Company – Flipkart
Designation – Business Analyst

Year of Experience required – 0 to 3 years
Technical expertise – SQL, Python, Case Study
Salary offered – 10 to 18 LPA (10% variable)

Number of Rounds – 5

Flipkart SQL Interview Questions

There were 20 questions in the online mode of exam, there were candidates who actually had to take offline exam. The questions were basic in nature and were around
– finding error in code
– output of code
– optimizing queries
– basic MCQ
Just follow any good interview question e-book or blog. We also have 250 SQL interview questions in the 2200 interview question books, these questions will help you become a complete analytics professional. Do check out the topics covered in thie book.

What is the usage of the NVL() function?
You may use the NVL function to replace null values with a default value. The function returns the value of the second parameter if the first parameter is null. If the first parameter is anything other than null, it is left alone.
This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have IFNULL() and SQL Server have ISNULL() function.

Explain character-manipulation functions? Explains its different types in SQL.
Change, extract, and edit the character string using character manipulation routines. The function will do its action on the input strings and return the result when one or more characters and words are supplied into it.
The character manipulation functions in SQL are as follows:
A) CONCAT (joining two or more values): This function is used to join two or more values together. The second string is always appended to the end of the first string.
B) SUBSTR: This function returns a segment of a string from a given start point to a given endpoint.
C) LENGTH: This function returns the length of the string in numerical form, including blank spaces.
D) INSTR: This function calculates the precise numeric location of a character or word in a string.
E) LPAD: For right-justified values, it returns the padding of the left-side character value.
F) RPAD: For a left-justified value, it returns the padding of the right-side character value.
G) TRIM: This function removes all defined characters from the beginning, end, or both ends of a string. It also reduced the amount of wasted space.

Write the SQL query to get the third maximum salary of an employee from a table named employees.
SELECT * FROM(
SELECT employee_name, salary, DENSE_RANK()
OVER(ORDER BY salary DESC)r FROM Employee)
WHERE r=&n;
To find 3rd highest salary set n = 3

What are UNION, MINUS and INTERSECT commands?
The UNION operator is used to combine the results of two tables while also removing duplicate entries. 
The MINUS operator is used to return rows from the first query but not from the second query. 
The INTERSECT operator is used to combine the results of both queries into a single row.
Before running either of the above SQL statements, certain requirements must be satisfied –
Within the clause, each SELECT query must have the same amount of columns.
The data types in the columns must also be comparable.
In each SELECT statement, the columns must be in the same order.

You always have a big data i.e. millions of rows in your tables, how would you partition it for optimum performance?
MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data across the file system based on a set of user-defined rules. In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster. Partitioning makes the most sense when dealing with millions of data.
Horizontal partitioning means that all rows matching the partitioning function will be assigned to different physical partitions.
Vertical partitioning allows different table columns to be split into different physical partitions.
RANGE Partitioning:
This type of partition assigns rows to partitions based on column values that fall within a stated range. The values should be contiguous, but they should not overlap each other. The VALUES LESS THAN operator will be used to define such ranges in order from lowest to highest.
LIST partitioning:
It is similar to RANGE, except that the partition is selected based on columns matching one of a set of discrete values. In this case, the VALUES IN statement will be used to define matching criteria.
HASH Partitioning:
In HASH partitioning, a partition is selected based on the value returned by a user-defined expression. This expression operates on column values in rows that will be inserted into the table. A HASH partition expression can consist of any valid MySQL expression that yields a nonnegative integer value. HASH is used mainly to evenly distribute data among the number of partitions the user has chosen.
LINEAR HASH:
Instead of using the modulo described above, when MySQL uses LINEAR HASH a powers-of-two algorithm is employed to calculate the partition where the data is to be stored. Syntactically, LINEAR HASH is exactly the same as HASH, except for the addition of the word LINEAR.

Suppose in class, you have 3n boys and 2n girls with their names tabulated along with their weight and gender. Write a SQL query to separate students alphabetically who are over-weight (55kg for girls, 75kg for boys)

select *
from table_name
where gender = ‘Male’ and weight > ’75kg’
union all
select *
from table_name
where gender = ‘Female’ and weight > ’55kg’

We have put all the 250 most asked questions in our SQL Interview Questions e-book. Do check it out

Flipkart Python Interview Questions

Check if a number is Armstrong number in Python. An armstrong number is a number that equals the sum of cube of all the digits of the number. Ex. 153 = 1^3+5^3+3^3
num = int(input(“Enter a number = “))
s = 0
x = num
while (x >0 ):
digit = x%10
s = s+(digitdigitdigit)
x = x//10
print(s)

print(“Armstrong” if s == num else “Not Armstrong”)

Write a program to get the Prime number in a list of numbers with starting and end point
x = int(input(“Enter a starting point = “))
y = int(input(“Enter an ending point = “))
def prime(x,y):
prime = []
for i in range(x,y):
if (i == 0 or i == 1):
continue
else:
for j in range(2,int(i/2)+1):
if(i%j == 0):
break
else:
prime.append(i)
return prime
print(“The list of prime numbers are = “, prime(x,y))

Largest element of an array
a = [1,5,7,3,4,9,18,222]
num = len(a)
def lar(a):
largest = a[0]
for i in range(2,num):
if largest <= a[i]:
largest = a[i]
return largest

print(“The largest element is = “,lar(a))

Rotate an array
x = [1,2,5,6,7]
y = len(x)
z = []
for i in range(0,(y)):
z.append(x[y-i-1])
print(z)

Flipkart Case Study Round

What are the KPIs for Walmart?
Walmart, as a retail company, has several key performance indicators (KPIs) that it uses to measure the success and efficiency of its operations. Some of the main KPIs that Walmart may track include:
Revenue: Walmart tracks its revenue as a measure of overall financial performance.
Same-store Sales: Walmart tracks the sales of stores open for at least one year to measure the performance of its existing store base.
Gross Margin: Walmart tracks the difference between its revenue and cost of goods sold to measure profitability.
Market Share: Walmart tracks its market share in retail industry to measure its competitiveness.
Customer Satisfaction: Walmart tracks customer satisfaction through surveys and ratings provided by customers to measure the quality of its products and services.
Inventory turnover: Walmart tracks the number of times its inventory is sold and replaced over a certain period to measure efficiency in inventory management.
Online Sales: Walmart tracks the sales made through its online platform to measure the success of its e-commerce efforts.
Same-day Delivery and Pickup: Walmart tracks the use of its same-day delivery and pickup services to measure the efficiency and popularity of these services.
Employee Turnover: Walmart tracks the rate at which employees leave the company to measure the effectiveness of its human resources practices.
Operating Margin: Walmart tracks the operating margin, which is the difference between revenue and operating expenses as a percentage of revenue, to measure the efficiency of its operations.
These are some of the key performance indicators that Walmart may track, but it may also track other metrics depending on its specific goals and objectives.

We have 100 case studies and guesstimate completely solved that are repeatedly asked in Analytics Interview. Do check it out.

Flipkart Behavioral Round

There were questions like:
– Why you want to quit your current organisation?
– How do you lead a team of 100 people ?
– How are you trying to upgrade your technical skills?
– How much time do you invest in data cleaning ?
– Project description

The Data Monk Product and Services

  1. Youtube Channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
    Link – The Data E-shop Page
  4. Mock Interviews
    Book a slot on Top Mate
  5. Career Guidance/Mentorship
    Book a slot on Top Mate
  6. Resume-making and review
    Book a slot on Top Mate 

The Data Monk e-book Bundle 

1.For Fresher to 7 Years of Experience
2000+ interview questions on 12 ML Algorithm,AWS, PCA, Data Preprocessing, Python, Numpy, Pandas, and 100s of case studies

2. For Fresher to 1-3 Years of Experience
Crack any analytics or data science interview with our 1400+ interview questions which focus on multiple domains i.e. SQL, R, Python, Machine Learning, Statistics, and Visualization

3.For 2-5 Years of Experience
1200+ Interview Questions on all the important Machine Learning algorithms (including complete Python code) Ada Boost, CNN, ANN, Forecasting (ARIMA, SARIMA, ARIMAX), Clustering, LSTM, SVM, Linear Regression, Logistic Regression, Sentiment Analysis, NLP, K-M


Myntra Business Analyst Interview Questions

Myntra Business Analyst Interview Questions

Company Name – Myntra
Designation – Business Analyst
Salary – 18 to 22 LPA (Depending on current CTC and graduation college)
Number of rounds – 4
Level of questions – Moderate to Hard

Myntra Business Analyst Interview Questions

Myntra Business Analyst Interview Questions
Myntra Business Analyst Interview Questions

Round 1 – 50 MCQs to be solved in 60 minutes
Round 2 – Technical Round with focus on SQL and Python (Pandas and Numpy)
Round 3 – Case Study Round
Round 4 – Hiring Manager with some SQL coding

Round 1 – MCQs

There were 50 questions that was supposed to be solved in one hour. There were 40 MCQs and 10 query writing questions. We can’t disclose the exact questions as it is still used for recruitment but below is the type of questions asked:-
1. Calculating date difference between two dates
2. Extracting string using Regular expression
3. Output of outer join
4. Caluclating cumulative sum on a data set
5. Difference between rank() and dense_rank()
6. Finding error in query (mostly on group by and aggregate functions)
7. Difference between key (primary and foreign key)
8. Output of simple but confusing commands like Select ‘1’+2 or Select Null+’2′

Round 2 – SQL and Python

Following is the link to the questions, most of the questions are solved by the fellow Analytics professionals. Do check it out

Find all the students who either are male or live in Mumbai ( have Mumbai as a part of their address).
Get all the distinct email id from a column in SQL
Join two tables without using any common column
Output based question
Output based question – 2

Python Questions

1. Difference between mutable and immutable data type
2. Missing data treatment using Pandas
3. Join and group by syntax
4. Write a nested lambda function to multiply two numbers.
5. Difference between parameter and argument

Round 3 – Case Study

There were a variety of case studies asked to different candidates, the one asked to me was

Can you choose a strategy for increasing the number of songs listened by the user on an online application? How will you decide the types of playlists to suggest him?

Approach

Mainly I will tackle this problem like any recommendation engine.

Machine learning algorithms in recommender systems are typically classified under two main categories :

1.  Content-based – strategy relies on analyzing factors and demographics that are directly associated with the user or product, such as the age, sex and demographic of the user or a song genre or playlist,

2. Collaborative filtering – Collaborative Filtering takes consumer behavior data and utilizes it to predict future behavior This consumer behavior leaves a trail of data, generated through implicit and explicit feedback, based on the user’s listening history, in tandem with songs enjoyed by users who seem to have a similar history

Round 4 – Hiring Manager Round

Hiring Manager round was mostly around work experience and past projects, but be prepared to have a few questions on SQL (related to your project) and a few basic questions on statistics.

First round is the most important as your score is passed to all the following rounds. A few questions might be asked from the 1st round question(that you might have messed up)

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,

Amazon Business Intelligence Engineer Questions

Amazon Business Intelligence Engineer Questions
Company Name – Amazon
Designation – Business Intelligence Engineer
Salary – 15 to 30 LPA (Depending upon stock options and current CTC)
Number of rounds – 7 (It also depends on the team for which you are getting interviewed)
Level of the interview – Moderate
Amazon Business Intelligence Engineer Questions

Amazon Business Intelligence Engineer Questions
Amazon Business Intelligence Engineer Questions

In general, the interview process at Amazon is quite different for different teams. We know candidates that have faced 4 technical interviews, a few had 7 rounds of technical interviews and some have 2 technical and 5 rounds around Amazon’s Leadership principles. So, the interview process will depend on the team for which you are getting interviewed.

We have interviewed many candidates that have converted Amazon at BIE I and BIE II positions and below are the top 5 SQL questions that are asked in Amazon as well as in other product-based companies:

In order to check your level of preparation, do try to solve the below 5 questions by yourself and comment your score below

Following is the point charter:-
5 correct – SQL God, You are going to nail 9/10 SQL interviews
4 correct – Really Good, clearing the SQL rounds should not be a problem
3 correct – Ummm..Dicey, you should be able to crack a few rounds
2 correct – You need at least 2-3 weeks before you start applying
1 correct – Padh lo Beta, sochna bhi nai apply krne ka abhi 😛
0 correct – Try an MBA/MS in Analytics or SBI PO 😛

Q1. Suppose there is a Movie Theatre with 26 rows(A,B,C..Z) and in each row you have 6 seats. The structure of the table is given below

DateRow_NoSeat_NoOccupiedName
04-Apr-20AA1YesKuchi Bhi
04-Apr-20AA2YesKuchi Bhi
04-Apr-20AA3No
04-Apr-20AA4No
04-Apr-20AA5No
04-Apr-20AA6No
04-Apr-20BB1YesKuchi Bhi
04-Apr-20BB2No
04-Apr-20BB3No
04-Apr-20BB4YesKuch Bhi
04-Apr-20BB5No
04-Apr-20BB6YesKuchi Bhi
Table Name – PVR

First, let me know all the starting seats where the number of consecutive vacant seats is 2 (B2 here)
Secondly, write a generalized approach to solve for any number of vacant seats. Basically, you need to create a table with two columns,
1. Seat_No
2. Number of consecutive vacant seats

Q2. There are multiple ways to get the 3rd highest salary, write down at least three. This question is important because the moment you tell the interviewer the first way, he/she will ask to solve the same in any other way

Hint –
1. Naive Approach
2. Inner Query
3. Ranking


Q3. I don’t remember any interview which doesn’t have this question

Table ATable B
11
11
11
1
1

There are two tables, the column name in Table A is X and in Table B is Y

How many rows will the resultant have, if you do:-
a. inner join
b. left join
c. right join
d. outer join
e. cross join

Justify your answer

Q4. I own 5 restaurants in Bangalore, following is the table of business, get me the date on which each of these restaurants crossed a total revenue of Rs.10,000

RestaurantDateRevenue
Bangalore Mandrian01/04/205000
Bangalore Mandrian02/04/204000
Bangalore Mandrian03/04/203000
Bangalore Mandrian04/04/206000
Boondock Bistro01/04/207000
Boondock Bistro02/04/203000
Boondock Bistro03/04/205000
Oliver’s02/04/2011000
Oliver’s03/04/208000
Oliver’s04/04/209000
Whitefield Social02/04/2010000
Punjabi By Nature03/04/208000

Q5. There is a table with employee and manager information, the schema is given below. Write a query to get the EmployeeName and ManagerName.

IdEmployee NameManager Id
1Sachin3
2Sehwag3
3Dhoni
4Kohli1
5Sharma1

Hint – Consider the edge cases, i.e. your query should cater to the Manager Name of the CEO as well. In the above example, Dhoni is the CEO, so he has no manager id associated with him.

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

AirBnB Data Scientist Interview Questions

AirBnB Data Scientist Interview Questions
Company Name –Airbnb
Designation – Data Science
Salary – 15 to 22 LPA depending upon current CTC and graduation college
Interview Year – 2022
AirBnB Data Scientist Interview Questions

AirBnB Data Scientist Interview Questions
AirBnB Data Scientist Interview Questions

Following is a set of questions asked in the interview at AirBnB, we request you to attempt these questions

Round 1 -Technical Interview

Topics covered –Application Data science
Mode of interview –Meet
Duration – 90 Minutes
Level of Questions –Hard
Design a recommender system for Airbnb listings.
Which tables and indexes do you need in a SQL db to manage chat threads?
How would you measure the effectiveness of our operations team?
We see a dip in page views yesterday. How would you investigate what happened?

Round 2 -Technical Interview

Topics covered –Applied Machine Learning
Mode of interview –Meet
Duration – 90 Minutes
Level of Questions –Hard

If Airbnb wanted to open a new office, how would you determine how many meeting rooms to build into it?
How would you go about designing a metric for customer service calls?
What would happen if you were to include another X in your regression model?
An important metric goes down, how would you dig into the causes?


Round 3 –Technical Interview
Topics-Machine Learning
Mode of Interview- Meet
Duration 1 hour
Level of questions- Medium

A few questions from this round:
How does boosting work
What are the different error metrics employed in machine learning?

How would you normalize data ?

What is an ROC curve?


Round 4 – Hiring Manager Round
Topics covered: Resume
Mode: Hangout
Duration –1 hours
Level of Questions – Medium

A few questions below from this round:

Tell me about a time when you faced a challenge, how did you overcome it.

How have you made someone outside your immediate social circle feel that they belong?”.

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,