1 | Page

Find answers to these questions related to

Interviews:
1)Mention the differences between Data Mining and Data
Profiling?
2)Define the term 'Data Wrangling in Data Analytics.
3)What are the various steps involved in any analytics project?
4)What are the common problems that data analysts encounter
during analysis?
5)Which are the technical tools that you have used for analysis
and presentation purposes?
6) What are the best methods for data cleaning?
7) What is the significance of Exploratory Data Analysis (EDA)?
8) Can you walk us through a recent project you worked on as
a Data Analyst?
9) How do you handle missing or incomplete data in your
analysis?
10) Can you explain the process you follow to clean and
preprocess data before analysis?
11) What data visualization tools or techniques do you use to
communicate your findings effectively?
12) Describe a challenging data analysis problem you
encountered and how you solved it.
13) How do you ensure the accuracy and reliability of your
analysis results?
14) Can you discuss a time when you had to work with large
datasets? How did you handle it?
15) Have you ever used machine learning algorithms in your
analysis? If so, can you provide an example?
16) How do you stay updated on industry trends and
advancements in data analysis techniques?
2 | Page

17) Can you discuss a time when you had to present your
analysis findings to non-technical stakeholders? How did you
ensure they understood the insights?

Here are 7 Beginner Data Modelling for Data Analytics

Interview Questions and Answers to guide you through:
Question 1: Explain the importance of data modelling in data
analytics and its key components.

Answer: Data modelling structures data in a way that facilitates

analysis and decision-making. Key components include entities,
attributes, relationships, and constraints, which help organize
and represent data accurately.

Question 2: How do you ensure data quality and consistency in

data modelling?

Answer: Validate input data, enforce data integrity constraints,

and conduct data profiling to identify anomalies. Implement
standardized naming conventions and documentation practices
to maintain consistency and accuracy.

Question 3: Define dimensional modelling and its significance

in data analytics.

Answer: Dimensional modelling organizes data into easily

understandable structures called dimensions and measures. It's
essential for creating intuitive and efficient data models that
support analytical queries and reporting.

Question 4: What are the steps involved in developing a data

model, and how do you approach them?

Answer: The steps include requirements gathering, conceptual

modelling, logical modelling, and physical modelling. Start by
understanding business needs, then translate them into a
conceptual model before refining it into logical and physical
models for implementation.
3 | Page

Question 5: How do you handle complex relationships between

entities in data modelling?

Answer: Utilize techniques like normalization and

denormalization to manage complex relationships efficiently.
Normalize data to reduce redundancy and improve data
integrity, and de-normalize for performance optimization and
query efficiency.

Question 6: Explain the concept of data warehouse modelling

and its role in data analytics.

Answer: Data warehouse modelling involves designing

structures optimized for analytical queries and reporting. It
integrates data from multiple sources into a centralized
repository, enabling comprehensive analysis and strategic
decision-making.

Question 7: Why is proficiency in data modelling crucial for

data analysts, and how do you continue to enhance your skills
in this area?

Answer: Proficiency in data modelling enables data analysts to

design efficient and scalable data solutions that meet business
objectives. Continuous learning through courses, workshops,
and hands-on projects enhances modelling skills and ensures
alignment with industry best practices.

-----------------------------------------------------------------------------------------
-----
4 | Page

Here are 7 Beginner Business Intelligence Analyst

Interview Questions and Answers to help you excel:
Question 1: Define descriptive and inferential statistics and
their applications.

Answer: Descriptive stats summarize data, while inferential

stats make predictions from samples. Descriptive stats offer
insights, while inferential stats make broader predictions.

Question 2: How do you clean and prepare data for analysis?

Answer: Data cleaning corrects errors and inconsistencies using

tools like Excel, Python, or R. Preprocessing includes handling
missing data and standardizing formats.

Question 3: Explain correlation and its role in data analysis.

Answer: Correlation measures relationships between variables.

It's vital for understanding patterns and making data-driven
decisions.

Question 4: What are common data visualization techniques,

and how do you select the appropriate one?

Answer: Bar charts, scatter plots, and histograms are common.

Choose based on data type, desired insights, and audience
preferences.

Question 5: How are exploratory data analysis (EDA)

conducted, and what insights can it reveal?

Answer: EDA uncovers patterns, trends, and relationships

through summary stats and visualization. It identifies outliers
and generates hypotheses.
5 | Page

Question 6: Describe hypothesis testing and its application in

data analysis.

Answer: Hypothesis testing infers population trends from

sample data. For example, it can determine significant
differences in sales performance between regions.

Question 7: Why is data quality essential, and how do you

ensure it in analysis?

Answer: Data quality ensures accurate analysis and decision-

making. Validate data, clean it thoroughly, and establish
governance practices for integrity.

-----------------------------------------------------------------------------------------
-----
6 | Page

SQL Interview question 1

Consider a table named Employee with the following

schema:

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);

Write an SQL query to find the employee(s) with the

highest salary within each department.

ANS :
-----------------------------------------------------------------------------
---

manager after sql, start to 𝐚𝐬𝐤 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐨𝐧 𝐄𝐱𝐜𝐞𝐥,

In data analyst interviews, 50% of the time hiring

and same goes with my colleagues and friends. After

discussing with them, i have prepared some list of
questions for you. Here is the break down:

The interviewer show some datasets and give you

scenarios to solve:

📊 Excel Functions Scenario 1:

Imagine you have a dataset containing sales figures for
different products across various regions. The management
wants to calculate the total sales for each product category.
7 | Page

How would you use the SUMIF function to accomplish this task
efficiently?

🔄 Excel Functions Scenario 2:

You have a large dataset with customer information including
names, email addresses, and phone numbers. Your task is to
extract the domain names from the email addresses provided.
Explain how you would use the RIGHT, LEFT, and FIND functions
in combination to achieve this.

🔍 Pivot Tables Scenario 1:

You're given a dataset containing sales data for a retail store
over the past year. Your manager wants to analyze the sales
performance by month and product category. Describe how
you would create a pivot table to summarize and visualize this
information effectively.

📈 Pivot Tables Scenario 2:

Your company has conducted a survey with multiple-choice
questions. The survey results are stored in an Excel sheet with
each respondent's choices recorded in separate columns. How
would you use pivot tables to analyze the survey data and
present the distribution of responses for each question?

🔗 Power Query Scenario 1:

You receive a CSV file with inconsistent date formats in one of
the columns. Some dates are in "MM/DD/YYYY" format, while
others are in "DD/MM/YYYY" format. Explain how you would use
Power Query to standardize the date format across the entire
column.

🔄 Power Query Scenario 2:

You're working with a large dataset that contains duplicate
rows. Before proceeding with analysis, you need to remove
these duplicate rows to ensure data accuracy. How would you
use Power Query to identify and remove duplicate records from
the dataset efficiently?
8 | Page

📊 Excel Functions Question:

What is the difference between the VLOOKUP and INDEX-
MATCH functions in Excel? When would you use one over the
other?

🔍 Power Query Question:

What are some advantages of using Power Query over
traditional data manipulation techniques in Excel? Provide
examples.

📈 Excel Functions Question:

How would you use the AVERAGEIF and AVERAGEIFS functions
in Excel to calculate the average sales for a specific product
category within a given date range?

🔗 Pivot Tables Question:

What are "Slicers" in Excel pivot tables, and how can they
enhance data analysis and visualization?"

🔄 Power Query Question:

Explain the process of merging queries in Power Query. How
does it help in combining data from different sources for
analysis?

-----------------------------------------------------------------------------
-----------------

MNC SQL interview questions for data analyst& business

analyst! 💼

1. Explain Order of Execution of SQL query

2. Provide a use case for each of the functions Rank,
Dense_Rank & Row_Number ( 💡 majority struggle )
9 | Page

3. Write a query to find the cumulative sum/Running Total

4. Find the Most selling product by sales/ highest Salary of
employees
5. Write a query to find the 2nd/nth highest Salary of
employees
6. Difference between union vs union all
7. Identify if there any duplicates in a table
8. Scenario based Joins question, understanding of Inner, Left
and Outer Joins via simple yet tricky question
9. LAG, write a query to find all those records where the
transaction value is greater than previous transaction value
10. Rank vs Dense Rank, query to find the 2nd highest Salary
of employee
( Ideal soln should handle ties)
11. Write a query to find the Running Difference (Ideal sol'n
using windows function)
12. Write a query to display year on year/month on month
growth
13. Write a query to find rolling average of daily sign-ups
14. Write a query to find the running difference using self-join
(helps in understanding the logical approach, ideally this
question is solved via windows function)
15. Write a query to find the cumulative sum using self-join
(helps in understanding the logical approach, ideally this
question is solved via windows function)
16. Optimize a query to find the latest transaction for each
customer
17. Explore the benefits of CTEs (Common Table Expressions)
in a real-world scenario
18. Write a query to calculate the percentile rank of a given
value in a dataset
19. Implement a pivot table in SQL for dynamic column
aggregation
20. Craft a query to identify and remove duplicates based on
specific criteria
10 | P a g e

-----------------------------------------------------------------------------
-----------------

Some common #SQL interview questions along with

their queries:

1. Write a SQL query to find the second highest salary

from the 'emp' table. (Columns: id, salary)

SELECT MAX(salary) AS second_highest_salary

FROM emp
WHERE salary < (SELECT MAX(salary) FROM emp);

2. Write a SQL query to find the days when the

temperature was higher than its previous dates.
(Columns: Days, Temp)

SELECT Days, Temp

FROM table_name t1
WHERE Temp > (SELECT Temp FROM table_name t2 WHERE
[Link] = [Link] - 1);

-----------------------------------------------------------------------------
-----------------

Recently a candidate interviewed by India's biggest

Airline for Executive Data Analyst.
11 | P a g e

Questions -
1) What is SQL. What are the different joins we can used?
2) Gave me the three tables randomly maked by interviewer
and told me to apply the joins and give me the result?
3)Define where and having clause.? They gave me a table and
conditions then we have to apply clause? Where clause is used
with Group by or not?
4) Rate yourself in Powe BI, SQL, Microsoft Excel out of 10, and
when I answered they told me rate yourself always high.
5)Define different types of Look up function? Difference
between Lookup and index function in excel, you have to show
how these function works with an example?
6)What formulas you know in excel? Define Advance Excel and
what's the use of it?
7)Define Nested-if and Ifs function with an example?
8)Define Power BI and ETL?
9)Define your projects? What type of analysis you did in your
Projects?
10)How do you do data cleansing? How to deal with missing
values?
11)Define Pandas? What is Statistical Analysis? what types of
Statistical Analysis you have done?
12)Define Mean, Median, Mode?

-----------------------------------------------------------------------------------------
-----

SQL queries that are commonly asked during interviews:

1. Retrieve the first 10 rows from a table:

SELECT * FROM table_name LIMIT 10;
12 | P a g e

2. Find the second highest salary from an Employee

table:
SELECT MAX(salary) FROM Employee WHERE salary < (SELECT
MAX(salary) FROM Employee);

3. List employees who joined in the last month:

SELECT * FROM Employee WHERE JOIN_DATE >=
DATEADD(MONTH, -1, GETDATE());

4. Count the number of employees in each department:

SELECT department, COUNT(*) AS num_employees
FROM Employee
GROUP BY department;

5. Retrieve employees who have the highest salary in

each department:
SELECT department, MAX(salary) AS max_salary
FROM Employee
GROUP BY department;

6. Find the nth highest salary from an Employee table:

SELECT DISTINCT salary FROM Employee ORDER BY salary
DESC LIMIT n-1, 1;

7. List employees who don't have a manager:

SELECT * FROM Employee WHERE manager_id IS NULL;

8. Retrieve employees who earn more than their

managers:
SELECT e.*
FROM Employee e
JOIN Employee m ON e.manager_id = m.employee_id
WHERE [Link] > [Link];

9. Find duplicate records in a table:

SELECT column1, column2, COUNT(*)
FROM table_name
13 | P a g e

GROUP BY column1, column2

HAVING COUNT(*) > 1;

10. Calculate the total sales for each product:

SELECT product_id, SUM(quantity * price) AS total_sales
FROM Sales
GROUP BY product_id;

-----------------------------------------------------------------------------------------
-----

Understanding SQL Joins 🧐

• Ever heard of SQL joins but felt lost in the database labyrinth?
• Let's unravel this mystery with a relatable analogy and
simple language!

Scenario:
• Imagine you're hosting a party with two guest lists – one for
friends and another for colleagues.
• Now, let's use SQL joins to merge these lists and see who's
coming together!

1. Inner Join:
• Think of an inner join as the ultimate networking tool at your
party.
• It brings together only the guests who are on both the
friends' and colleagues' lists.
• It's like inviting friends who are also colleagues or vice versa.
14 | P a g e

Syntax:-

SELECT [Link], [Link]

FROM Friends
INNER JOIN Colleagues ON [Link] = [Link];

Example:
If "John" is on both lists with the same email, the inner join will
include him in the final guest list since he's both a friend and a
colleague.

2. Left Join:
• Now, let's say you're more inclined to prioritize your friends
over colleagues.
• A left join fetches all guests from the friends' list and matches
them with colleagues if they're present.
• If not, they're still invited!

Syntax:

SELECT [Link], [Link]

FROM Friends
LEFT JOIN Colleagues ON [Link] = [Link];

Example:
Even if "Mary" is on the friends' list but not the colleagues' list,
the left join ensures she still gets an invite, leaving the
colleague slot empty.

3. Right Join:

• Conversely, imagine you're giving more weight to colleagues

this time.
• A right join prioritizes colleagues, ensuring all colleagues get
an invite, even if they're not on the friends' list.
15 | P a g e

Syntax:

SELECT [Link], [Link]

FROM Friends
RIGHT JOIN Colleagues ON [Link] = [Link];

Example:
If "David" is a colleague but not a friend, the right join ensures
he's included in the guest list alongside his colleague
companions.

4. Full Outer Join:

• Now, let's consider a scenario where you want to invite
everyone from both lists, regardless of their friendship status. •
A full outer join combines all guests from both lists, ensuring no
one gets left out.

Syntax:

SELECT [Link], [Link]

FROM Friends
FULL OUTER JOIN Colleagues ON [Link] =
[Link];

Example:
Whether "Emily" is a friend, a colleague, or both, the full outer
join ensures she receives an invite to your inclusive gathering.

5. Self Join:
• Think of a self join as inviting someone to the party and
asking them to bring their clone!
• It's like looking at the same guest list twice and making
connections within the same table.
16 | P a g e

Syntax:
SELECT [Link] AS FriendName, [Link] AS ColleagueName
FROM Guests f
JOIN Guests c ON [Link] = [Link];

Example:
If "Alice" and "Bob" share the same email on the guest list, a
self join will pair them together, creating a unique networking
opportunity within the same table.

-----------------------------------------------------------------------------------------
-----

SQL queries that are commonly asked during interviews:

part 3.O

1. Calculate the total number of orders placed each

month:
SELECT EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS num_orders
FROM Orders
GROUP BY EXTRACT(MONTH FROM order_date);

2. Find the customer who has placed the highest

number of orders:
SELECT customer_id, COUNT(*) AS num_orders
FROM Orders
GROUP BY customer_id
ORDER BY num_orders DESC
17 | P a g e

LIMIT 1;

3. Retrieve the top 10% of highest-paid employees:

SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT (SELECT COUNT(*) * 0.1 FROM Employee);

4. List employees who have the same manager:

SELECT e1.employee_id, e1.employee_name, e1.manager_id
FROM Employee e1
JOIN Employee e2 ON e1.manager_id = e2.manager_id AND
e1.employee_id <> e2.employee_id;

5. Calculate the running total of sales for each month:

SELECT order_date, SUM(amount) OVER (ORDER BY
order_date) AS running_total
FROM Orders;

6. Retrieve the latest order placed by each customer:

SELECT DISTINCT ON (customer_id) *
FROM Orders
ORDER BY customer_id, order_date DESC;

7. Find customers who have never placed an order:

SELECT *
FROM Customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id
FROM Orders);

8. List the products that have never been sold:

SELECT *
FROM Products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM
Sales);
18 | P a g e

9. Retrieve the average time taken to ship orders for

each shipping method:
SELECT shipping_method, AVG(DATEDIFF(DAY, order_date,
ship_date)) AS avg_shipping_time
FROM Orders
GROUP BY shipping_method;

10. Find the total number of unique customers who

made purchases in each year:
SELECT EXTRACT(YEAR FROM order_date) AS year,
COUNT(DISTINCT customer_id) AS num_customers
FROM Orders
GROUP BY EXTRACT(YEAR FROM order_date);

-----------------------------------------------------------------------------------------
-----

TOP CONCEPTS FOR INTERVIEW PREPARATION!!

🚀TOP 10 SQL Concepts for Job Interview

1. Aggregate Functions (SUM/AVG)

2. Group By and Order By
3. JOINs (Inner/Left/Right)
4. Union and Union All
5. Date and Time processing
6. String processing
7. Window Functions (Partition by)
8. Subquery
9. View and Index
10. Common Table Expression (CTE)

🚀TOP 10 Statistics Concepts for Job Interview

19 | P a g e

1. Sampling
2. Experiments (A/B tests)
3. Descriptive Statistics
4. p-value
5. Probability Distributions
6. t-test
7. ANOVA
8. Correlation
9. Linear Regression
10. Logistics Regression

🚀TOP 10 Python Concepts for Job Interview

1. Reading data from file/table

2. Writing data to file/table
3. Data Types
4. Function
5. Data Preprocessing (numpy/pandas)
6. Data Visualisation (Matplotlib/seaborn/bokeh)
7. Machine Learning (sklearn)
8. Deep Learning (Tensorflow/Keras/PyTorch)
9. Distributed Processing (PySpark)
10. Functional and Object Oriented Programming.

-----------------------------------------------------------------------------------------
-----

-----𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬-----

20 | P a g e

In a Power BI interview, you can expect a range of questions to

assess your knowledge and skills related to Power Query, data
visualization, data modelling, and Power BI tools. Here are
some common Power BI interview questions:

Here, Power Bi Interview Questions

1️⃣. What Is the difference between Append

and Merge ?

2️⃣. What is the Query Folding

3️⃣. What is the difference between the Copying and

Reference a table ?

4️⃣. What is M Query/Code

5️⃣. How Will you change the datatype of the data

column(20210819) having whole number as it datatype

6️⃣. What are parameters and provide few of its use cases ?

-----------------------------------------------------------------------------------------
-----

Power BI scenario-based questions

Data Preparation:
Scenario: You have been given a dataset with inconsistent date
formats and missing values. How would you clean and prepare
the data for analysis using Power BI?
21 | P a g e

Data Modelling:
Scenario: Explain the difference between a star schema and a
snowflake schema. When would you choose one over the other
in Power BI, and what are the implications for performance?

Data Visualization:
Scenario: Imagine you need to create a dashboard to show the
sales performance of a company over the last year. What
visualizations would you choose, and how would you ensure the
dashboard effectively communicates key insights?

DAX (Data Analysis Expressions):

Scenario: You need to calculate the year-to-date sales growth
percentage in Power BI. How would you write the DAX formula
for this, and what considerations would you take into account?

Performance Optimization:
Scenario: Your Power BI report is loading slowly. How would you
identify and address performance bottlenecks in terms of both
data retrieval and visualization rendering?

Power Query:
Scenario: You have a large dataset, and you want to filter out
only the relevant rows before loading it into Power BI to
improve performance. How would you achieve this using Power
Query?

Data Integration:
Scenario: The company uses various data sources, including
Excel files, SQL databases, and web APIs. How would you
integrate and consolidate data from these diverse sources in
Power BI for comprehensive analysis?

Dynamic Reporting:
22 | P a g e

Scenario: You are tasked with creating a Power BI report where

the user can dynamically change the time period for analysis.
How would you implement dynamic date filtering in your
report?

Row-Level Security:
Scenario: The company wants to restrict access to certain sales
data based on the user's role. How would you implement row-
level security in Power BI to ensure that each user sees only
the relevant data?

Error Handling:
Scenario: During the ETL process, you encounter errors while
loading data into Power BI. How would you identify and handle
errors to ensure data integrity in your reports?

-----------------------------------------------------------------------------------------
-----

Python interview questions for Data/business analyst

roles: -

1. What is Pandas, and why is it popular in data analysis?

2. How do you import Pandas in Python?
3. What is a DataFrame in Pandas?
4. How do you read a CSV file into a Pandas DataFrame?
5. How can you check the first few rows and last few rows of a
DataFrame?
6. What is the difference between loc and iloc in Pandas?
7. How do you select a specific column in a DataFrame?
8. How do you filter rows in a DataFrame based on a condition?
9. How do you handle missing values in Pandas?
10. Explain the process of merging two DataFrames in Pandas.
23 | P a g e

11. What is the purpose of the groupby function in Pandas, and

how is it used?
12. How can you rename columns in a DataFrame?
13. Explain the difference between Series and DataFrame in
Pandas.
14. What is the purpose of the apply function in Pandas, and
how is it used?
15. How do you export a Pandas DataFrame to a CSV file?
16. Explain the concept of pivot tables in Pandas.

-----------------------------------------------------------------------------------------
-----

SQL queries that are commonly asked during interviews

41. Retrieve the most recent order for each product:

SELECT product_id, MAX(order_date) AS recent_order_date
FROM Orders
GROUP BY product_id;

42. Find the total number of orders and the average

order amount for each customer:
SELECT customer_id, COUNT(*) AS total_orders, AVG(amount)
AS avg_order_amount
FROM Orders
GROUP BY customer_id;

43. List the products that have been sold more than 100
times:
SELECT product_id
FROM Sales
GROUP BY product_id
HAVING COUNT(*) > 100;
24 | P a g e

44. Retrieve the email addresses of customers who have

not made any purchases:
SELECT email
FROM Customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id
FROM Orders);

45. Calculate the total number of days each product has

been in stock:
SELECT product_id, DATEDIFF(DAY, MIN(stock_date),
MAX(stock_date)) AS total_days_in_stock
FROM Stock
GROUP BY product_id;

[Link] the departments with the highest and lowest

average employee salaries:
Highest average
SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

lowest average
SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
ORDER BY avg_salary Asc
LIMIT 1;

47. List the customers who have made purchases in all

months of the year:
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date))
= 12;
25 | P a g e

48. Calculate the difference in sales between the

current year and the previous year for each product:
SELECT product_id,
SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) =
EXTRACT(YEAR FROM CURRENT_DATE) THEN amount ELSE 0
END) -
SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) =
EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN amount ELSE 0
END) AS sales_difference
FROM Sales
GROUP BY product_id;

49. Retrieve the employees who have joined in the last

quarter:
SELECT *
FROM Employee
WHERE JOIN_DATE >= DATEADD(QUARTER, -1, GETDATE());

50. List the products that have never been out of stock:
SELECT product_id
FROM Products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM
Stock
WHERE stock_quantity = 0);
-----------------------------------------------------------------------------------------
-----
SQL queries that are commonly asked during interviews:
part 6.O

51. Retrieve the average number of days between

orders for each customer:
SELECT customer_id, AVG(DATEDIFF(day, LAG(order_date)
OVER(PARTITION BY customer_id ORDER BY order_date),
order_date)) AS avg_days_between_orders
FROM Orders
GROUP BY customer_id;
26 | P a g e

52. Find the customers who have placed orders on

consecutive days:
SELECT DISTINCT o1.customer_id
FROM Orders o1
JOIN Orders o2 ON o1.customer_id = o2.customer_id
WHERE DATEDIFF(day, o1.order_date, o2.order_date) = 1;

53. Calculate the total revenue generated from each

product category:
SELECT p.category_id, SUM([Link]) AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.category_id;

54. Retrieve the top 3 most profitable products based

on total revenue:
SELECT product_id, SUM(amount) AS total_revenue
FROM Orders
GROUP BY product_id
ORDER BY total_revenue DESCLIMIT 3;

55. Find the number of employees in each salary range

(e.g., 0-50000, 50001-100000, etc.):
SELECT CONCAT(FLOOR(salary/50000)50000 + 1, '-',
FLOOR(salary/50000)*50000 + 50000) AS salary_range,
COUNT() AS num_employees
FROM Employee
GROUP BY FLOOR(salary/50000);

56. Retrieve the top 5 most frequent words from a text

column:
SELECT word, COUNT(*) AS frequency
FROM (
SELECT regexp_split_to_table(text_column, '\s+') AS word
FROM table_name
) AS words
GROUP BY word
ORDER BY frequency DESC
27 | P a g e

LIMIT 5;

57. Calculate the percentage change in sales amount

compared to the previous month for each product:
SELECT product_id,
(SUM(amount) - LAG(SUM(amount)) OVER(PARTITION BY
product_id ORDER BY EXTRACT(YEAR_MONTH FROM
sale_date))) / LAG(SUM(amount)) OVER(PARTITION BY
product_id ORDER BY EXTRACT(YEAR_MONTH FROM sale_date))
* 100 AS percentage_change
FROM Sales
GROUP BY product_id;

58. List the customers who have placed orders for all
products:
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*)
FROM Products);

59. Retrieve the orders placed by customers who have

not logged in to the system in the last 30 days:
SELECT *
FROM Orders
WHERE customer_id IN (
SELECT customer_id
FROM Customers
WHERE last_login_date <= DATEADD(day, -30, GETDATE()));

60. Find the average number of products sold per order:

SELECT AVG(num_products) AS avg_products_per_order
FROM (
SELECT order_id, COUNT(*) AS num_products
FROM OrderDetails
GROUP BY order_id
) AS order_products;
28 | P a g e

-----------------------------------------------------------------------------------------
-----

Here are some key points for SQL optimization:

Use Indexing: Properly indexing your database tables can

significantly speed up query performance by allowing the
database to quickly locate the rows needed for a query.

Optimize Joins: Minimize the number of joins and use

appropriate join types (e.g., INNER JOIN, LEFT JOIN) to ensure
efficient data retrieval.

Avoid SELECT * : Instead of selecting all columns using

SELECT *, explicitly specify only the columns needed for the
query to reduce unnecessary data transfer and processing
overhead.

Use WHERE Clause Wisely: Filter rows early in the query

using WHERE clause to reduce the dataset size before joining
or aggregating data.

Avoid Subqueries: Whenever possible, rewrite subqueries as

JOINs or use Common Table Expressions (CTEs) for better
performance.

Limit the Use of DISTINCT: Minimize the use of DISTINCT as

it requires sorting and duplicate removal, which can be
resource-intensive for large datasets.
29 | P a g e

Optimize GROUP BY and ORDER BY: Use GROUP BY and

ORDER BY clauses judiciously, and ensure that they are using
indexed columns whenever possible to avoid unnecessary
sorting.

Consider Partitioning: Partition large tables to distribute data

across multiple nodes, which can improve query performance
by reducing I/O operations.

Monitor Query Performance: Regularly monitor query

performance using tools like query execution plans, database
profiler, and performance monitoring tools to identify and
address bottlenecks.

-----------------------------------------------------------------------------------------
-----

Some of #Excel interview questions tailored for both

data analysts and business analysts:

1) What are the basic functions of Microsoft Excel?

2) Explain the difference between a workbook and a worksheet.
3) How would you freeze panes in Excel?
4) Can you name some common keyboard shortcuts in Excel?
5) What is the purpose of VLOOKUP and HLOOKUP?
7) How do you remove duplicate values in Excel?
8) Explain the steps to filter data in Excel.
9) What is the significance of the "IF" function in Excel, and can
you provide an example of its use?
10) How would you create a pivot table in Excel?
11) Explain the use of the CONCATENATE function in Excel.
12) How do you create a chart in Excel?
30 | P a g e

13) Explain the difference between a line chart and a scatter

plot.
14) What is conditional formatting, and how can it be applied in
Excel?
15) How would you create a dynamic chart that updates with
new data?
16) What is the INDEX-MATCH function, and how is it different
from VLOOKUP?
17) Can you explain the concept of "PivotTables" and when you
would use them?
18) How do you use the "COUNTIF" and "SUMIF" functions in
Excel?
19) Explain the purpose of the "What-If Analysis" tools in Excel.
20) What are array formulas, and can you provide an example
of their use?

Business Analysis Specific:

1) How would you analyze a set of sales data to identify trends

and insights?
2) Explain how you might use Excel to perform financial
modeling.
3) What Excel features would you use for forecasting and
budgeting?
4) How do you handle large datasets in Excel, and what tools or
techniques do you use for optimization?
5) What are some common techniques for cleaning and
validating data in Excel?
6) How do you identify and handle errors in a dataset using
Excel?

Scenario-based Questions:

1) Imagine you have a dataset with missing values. How would

you approach this problem in Excel?
2) You are given a dataset with multiple sheets. How would you
consolidate the data for analysis?
31 | P a g e

Remember to tailor these questions based on the specific job

requirements and the level of expertise expected for the role.

-----------------------------------------------------------------------------------------
-----

SQL queries that are commonly asked during interviews:

part 7.O

61. Retrieve the customers who have made purchases

on weekdays only:
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT CASE WHEN EXTRACT(ISODOW FROM
order_date) < 6 THEN EXTRACT(ISODOW FROM order_date)
END) = COUNT(DISTINCT EXTRACT(ISODOW FROM
order_date));

62. Find the average time taken to ship orders for each
product category:
SELECT p.category_id, AVG(DATEDIFF(day, order_date,
ship_date)) AS avg_shipping_time
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.category_id;

63. Retrieve the customers who have placed orders for

more than 10 unique products in a single order:
SELECT customer_id, order_id
FROM (
SELECT customer_id, order_id, COUNT(DISTINCT product_id)
AS unique_products
FROM OrderDetails
32 | P a g e

GROUP BY customer_id, order_id

) AS order_products
WHERE unique_products > 10;

64. Calculate the total sales for each product category

in the last quarter:
SELECT p.category_id, SUM([Link]) AS
total_sales_last_quarter
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
WHERE order_date >= DATEADD(QUARTER, -1, GETDATE())
GROUP BY p.category_id;

65. List the employees who have worked in multiple

departments:
SELECT employee_id, employee_name
FROM (
SELECT employee_id, employee_name, COUNT(DISTINCT
department_id) AS num_departments
FROM Employee_Departments
GROUP BY employee_id, employee_name
) AS multi_department_employees
WHERE num_departments > 1;

66. Retrieve the orders with the highest and lowest

order amounts:
SELECT * FROM Orders WHERE amount = (SELECT
MAX(amount) FROM Orders)
UNION
SELECT * FROM Orders WHERE amount = (SELECT
MIN(amount) FROM Orders);

[Link] the top 3 most common pairs of products

bought together:
SELECT product_id1, product_id2, COUNT(*) AS pair_count
FROM (
SELECT od1.product_id AS product_id1, od2.product_id AS
product_id2
33 | P a g e

FROM OrderDetails od1

JOIN OrderDetails od2 ON od1.order_id = od2.order_id AND
od1.product_id < od2.product_id
) AS product_pairs
GROUP BY product_id1, product_id2
ORDER BY pair_count DESC
LIMIT 3;

68. Calculate the percentage of total orders for each

product category:
SELECT p.category_id, COUNT(o.order_id) * 100.0 / (SELECT
COUNT(*) FROM Orders) AS percentage_total_orders
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.category_id;

69. Retrieve the customers who have made purchases

on weekends only:
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT CASE WHEN EXTRACT(ISODOW FROM
order_date) > 5 THEN EXTRACT(ISODOW FROM order_date)
END) = COUNT(DISTINCT EXTRACT(ISODOW
FROM order_date)));

-----------------------------------------------------------------------------------------
-----

#Sharing a Candidate recent #technical #interview

questions for the Data Analyst role, hope it will help!

Technical questions that were asked :

34 | P a g e

1. Asked me to explain my (Power BI) project worked on.

2. What is Cardinality in Power BI. Cardinality types?

3. Components in Power BI

4. What is Bidirectional cross filtering in Power BI

5. What is Data Modelling ?

6. What is RDBMS ?

7. What are there window functions in SQL. Why do we use it

( explain all )

8. What is difference between Where and Having ?

9. What is difference between Stored procedure and Views ?

10. Movie dataset given ( 3- Columns - title, length, rating

create 4 column based on ranking
( Based on the ranking of the length of the movies in each
rating category)

11. Question on JOIN ( Explain all types of JOINS )

table A table B
10 10
10 10
11 14
12 NULL
NULL 12
12 NULL
Find Inner join, Left , right, cross join values.

12. What is VLOOKUP, why do we use it , Syntax of VLOOKUP

( Asked 1 question on VLOOKUP.)
35 | P a g e

13. Asked question on SUMIF, SUMIFS

-----------------------------------------------------------------------------------------
----
SQL queries that are commonly asked during interviews:
part 8.O

71. Retrieve the customers who have made purchases of

at least three different products in each category:
SELECT customer_id
FROM (SELECT customer_id, category_id, COUNT(DISTINCT
product_id) AS num_products FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY customer_id, category_id
) AS customer_products_per_category
GROUP BY customer_id
HAVING COUNT() = (SELECT COUNT() FROM Categories);

[Link] the top 3 most common words in a text column

excluding common stop words ("and", "the", "is", etc.):
SELECT word, COUNT(*) AS frequency
FROM (
SELECT regexp_split_to_table(LOWER(text_column), '\s+') AS
word
FROM table_name
) AS words
WHERE word NOT IN ('and', 'the', 'is', 'of', 'a', 'to', 'in', 'it')
GROUP BY word
ORDER BY frequency DESC
LIMIT 3;

73. Calculate the average number of orders per month

for each customer:
SELECT customer_id, AVG(num_orders) AS
avg_orders_per_month
FROM (
SELECT customer_id, EXTRACT(MONTH FROM order_date) AS
month, COUNT(*) AS num_orders
FROM Orders
36 | P a g e

GROUP BY customer_id, EXTRACT(MONTH FROM order_date)

) AS monthly_orders
GROUP BY customer_id;

74. Retrieve the products that have been out of stock

for the longest continuous period:
SELECT product_id, MIN(stock_date) AS start_date,
MAX(stock_date) AS end_date,
DATEDIFF(DAY, MIN(stock_date), MAX(stock_date)) AS
days_out_of_stock
FROM (
SELECT product_id, stock_date,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER
BY stock_date) -
ROW_NUMBER() OVER (PARTITION BY product_id,
stock_quantity ORDER BY stock_date) AS grp
FROM Stock
) AS stock_groups
WHERE stock_quantity = 0
GROUP BY product_id, grp
ORDER BY days_out_of_stock DESC
LIMIT 1;

75. List the employees who have worked in all

departments:
SELECT employee_id
FROM (
SELECT employee_id, COUNT(DISTINCT department_id) AS
num_departments
FROM Employee_Departments
GROUP BY employee_id
) AS employee_department_counts
GROUP BY employee_id
HAVING COUNT() = (SELECT COUNT() FROM Departments);

[Link] the products that have experienced a

decrease in sales amount for each consecutive month
for the last three months:
37 | P a g e

SELECT product_id
FROM (
SELECT product_id,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER
BY sale_date) AS rn,
SUM(amount) AS total_amount
FROM Sales
GROUP BY product_id, EXTRACT(YEAR_MONTH FROM
sale_date)
) AS sales_per_month
WHERE rn <= 3
GROUP BY product_id
HAVING COUNT(*) = 3 AND total_amount = MAX(total_amount);

77. Find the average length of time between orders for

each customer:
SELECT customer_id, AVG(DATEDIFF(day, LAG(order_date)
OVER(PARTITION BY customer_id ORDER BY order_date),
order_date)) AS avg_time_between_orders
FROM Orders
GROUP BY customer_id;

-----------------------------------------------------------------------------------------
-----

Here are some some frequently asked question &

answers in SQL & pandas:-

1. Second Highest Salary

SQL-

SELECT MAX(salary) AS second_highest_salary FROM emp

WHERE salary < (SELECT MAX(salary) FROM emp);
38 | P a g e

python-

second_highest_salary = emp['salary'].nlargest(2).iloc[-1]

2. Consecutive Numbers

SQL-

SELECT DISTINCT [Link] FROM table_name a, table_name

b, table_name c WHERE [Link] = [Link] AND
[Link] = [Link] AND [Link] = [Link] - 1 AND [Link] = [Link] - 1;

python-

consecutives =
table_name[table_name['numbers'].eq(table_name['nu
mbers'].shift()) &
table_name['numbers'].eq(table_name['numbers'].shift (-1))]
['numbers'].unique()

3. Higher Temperature Than Previous Day

SQL -

SELECT Days, Temp FROM table_name t1 WHERE Temp >

(SELECT Temp FROM table_name t2 WHERE [Link] = [Link] -
1);

python-

higher_temp = table_name[table_name['Temp'] >

table_name['Temp'].shift(1)]

4. Cumulative Sum of Salary (Jan to July)

SQL-
39 | P a g e

SELECT Emp_id, Month, SUM(Salary) OVER (PARTITION BY

Emp_id ORDER BY Month) AS Cumulative_Salary FROM
salary_table WHERE Month BETWEEN 'January' AND 'July';

python-

salary_table['Cumulative_Salary'] =
salary_table[salary_table['Month'].isin(['January', 'February',
'March', 'April', 'May', 'June', 'July'])].groupby('Emp_id')
['Salary'].cumsum()

5. Year-on-Year Growth for Each Product

SQL -

SELECT EXTRACT (YEAR FROM transaction_date) AS year,

Product_id, (spend - LAG(spend, 1, 0) OVER (PARTITION BY
Product_id ORDER BY transaction_date)) / LAG(spend, 1, 0)
OVER (PARTITION BY Product_id ORDER BY transaction_date) *
100 AS yoy_growth FROM transactions;

python-

transactions['year'] =

transactions['transaction_date'].[Link]

transactions['lag_spend'] = [Link]('Product_id')
['spend'].shift(1) transactions['yoy_growth'] =
(transactions['spend'] - transactions['lag_spend']) /
transactions['lag_spend'] * 100

6. Rolling Average of Posts for Each User

SQL-
40 | P a g e

SELECT user_id, date, ROUND(AVG(post_count) OVER

(PARTITION BY user_id ORDER BY date ROWS BETWEEN 2
PRECEDING AND CURRENT ROW), 2) AS rolling_average FROM
posts_table;

python-

posts_table['rolling_average'] = posts_table.groupby('user_id')
['post_count'].rolling(window=3,
min_periods=1).mean().reset_index(level=0, drop=True)

-----------------------------------------------------------------------------------------
-----