๐๐ฒ๐๐ฒ๐น ๐จ๐ฝ ๐ฌ๐ผ๐๐ฟ ๐ฆ๐ธ๐ถ๐น๐น๐ ๐๐ถ๐๐ต ๐ง๐ต๐ฒ๐๐ฒ ๐๐ฎ๐บ๐ฒ-๐๐ต๐ฎ๐ป๐ด๐ถ๐ป๐ด ๐๐ผ๐๐ฟ๐๐ฒ๐!
โ
Looking to learn practical, in-demand skills? These courses cover Generative AI, Cybersecurity, AI tools and Digital Marketing.
๐ซ Learn at your own pace
โกBuild career-relevant skills
๐ฅPractical learning opportunities
๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ต๐ฒ ๐๐ผ๐๐ฟ๐๐ฒ๐ :-
https://pdlink.in/4z3vOYU
Save this post and share with your friends
โ
Looking to learn practical, in-demand skills? These courses cover Generative AI, Cybersecurity, AI tools and Digital Marketing.
๐ซ Learn at your own pace
โกBuild career-relevant skills
๐ฅPractical learning opportunities
๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ต๐ฒ ๐๐ผ๐๐ฟ๐๐ฒ๐ :-
https://pdlink.in/4z3vOYU
Save this post and share with your friends
โ
SQL Interview Questions with Answers
1. What is a window function?
A window function computes results over a group ("window") of rows related to the current row, without collapsing them (like GROUP BY).
Examples: ROW_NUMBER(), RANK(), SUM() OVER(...) for running totals, rankings, or moving averages.
2. What is the difference between RANK() and ROW_NUMBER()?
โข ROW_NUMBER(): assigns unique sequential numbers to all rows, even if values are equal.
โข RANK(): gives same rank to tied values, then skips the next rank (e.g., 1, 1, 3).
3. How do you find the second highest salary?
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) t
WHERE rnk = 2;
This avoids ties if you want exactly the secondโhighest value.
4. What is a recursive CTE?
A recursive CTE refers to itself in its WITH definition, usually in the form "anchor + UNION ALL recursive step". It is used for hierarchical data like managersโemployees, org charts, or tree structures.
5. What is the difference between correlated and non-correlated subquery?
โข Nonโcorrelated: runs once, independent of the outer query.
โข Correlated: references columns from the outer query and runs once per outer row (e.g., SELECT ... FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.id = t1.id)).
6. How do you remove duplicates without DISTINCT?
Use window functions:
DELETE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn
FROM table
) t
WHERE rn > 1;
Or use GROUP BY and keep one row per group.
7. What is an INDEX and when do you use it?
An index speeds up data retrieval on specified columns (used in WHERE, JOIN, ORDER BY). Use it on columns that are frequently filtered or joined; avoid on very small tables or columns updated often.
8. Explain self-join with example.
A selfโjoin joins a table to itself using aliases. Example:
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
Useful for parentโchild relationships.
9. What is the difference between DELETE, DROP, and TRUNCATE?
โข DELETE: removes rows (can be filtered by WHERE), can be rolled back.
โข TRUNCATE: removes all rows quickly, resets storage; often not logged per row.
โข DROP: removes entire table (structure + data); cannot be rolled back.
10. How do you pivot/unpivot data in SQL?
โข Pivot: turns rows into columns (e.g., sales per month as columns) using PIVOT or conditional aggregation (MAX(CASE WHEN ... END)).
โข Unpivot: turns columns into rows (e.g., multiple month columns โ one month column) using UNPIVOT or UNION ALL/VALUES.
11. What is LAG() and LEAD()?
โข LAG(col, n): value of col from n rows before current row.
โข LEAD(col, n): value from n rows after. Used for timeโseries analysis (MoM change, prior/next values).
12. How do you handle NULL in aggregates?
Most aggregates (SUM, AVG, MAX, MIN) ignore NULL.
โข COUNT(col) ignores NULL; COUNT(*) counts all rows.
โข Use COALESCE() or ISNULL() to replace NULL before aggregating.
13. What is the difference between VIEW and MATERIALIZED VIEW?
โข VIEW: virtual table; query runs every time you select.
โข MATERIALIZED VIEW: stores result physically and refreshes periodically; faster reads, slower updates.
14. Explain ACID properties.
โข Atomicity: transaction is "all or nothing".
โข Consistency: valid state before and after.
โข Isolation: concurrent transactions don't interfere.
โข Durability: committed changes survive crashes.
15. How do you optimize a slow query?
โข Add proper indexes on WHERE, JOIN, ORDER BY columns.
โข Remove unnecessary SELECT *, DISTINCT, or functions on indexed columns.
โข Check execution plan and avoid large scans; use LIMIT or partitioning if possible.
16. What is the difference between INNER JOIN and EXISTS?
โข INNER JOIN: returns combined columns from both tables where keys match.
โข EXISTS: checks if a subquery returns any rows; usually faster when you only care about existence (e.g., filtering with WHERE EXISTS).
1. What is a window function?
A window function computes results over a group ("window") of rows related to the current row, without collapsing them (like GROUP BY).
Examples: ROW_NUMBER(), RANK(), SUM() OVER(...) for running totals, rankings, or moving averages.
2. What is the difference between RANK() and ROW_NUMBER()?
โข ROW_NUMBER(): assigns unique sequential numbers to all rows, even if values are equal.
โข RANK(): gives same rank to tied values, then skips the next rank (e.g., 1, 1, 3).
3. How do you find the second highest salary?
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) t
WHERE rnk = 2;
This avoids ties if you want exactly the secondโhighest value.
4. What is a recursive CTE?
A recursive CTE refers to itself in its WITH definition, usually in the form "anchor + UNION ALL recursive step". It is used for hierarchical data like managersโemployees, org charts, or tree structures.
5. What is the difference between correlated and non-correlated subquery?
โข Nonโcorrelated: runs once, independent of the outer query.
โข Correlated: references columns from the outer query and runs once per outer row (e.g., SELECT ... FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.id = t1.id)).
6. How do you remove duplicates without DISTINCT?
Use window functions:
DELETE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn
FROM table
) t
WHERE rn > 1;
Or use GROUP BY and keep one row per group.
7. What is an INDEX and when do you use it?
An index speeds up data retrieval on specified columns (used in WHERE, JOIN, ORDER BY). Use it on columns that are frequently filtered or joined; avoid on very small tables or columns updated often.
8. Explain self-join with example.
A selfโjoin joins a table to itself using aliases. Example:
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
Useful for parentโchild relationships.
9. What is the difference between DELETE, DROP, and TRUNCATE?
โข DELETE: removes rows (can be filtered by WHERE), can be rolled back.
โข TRUNCATE: removes all rows quickly, resets storage; often not logged per row.
โข DROP: removes entire table (structure + data); cannot be rolled back.
10. How do you pivot/unpivot data in SQL?
โข Pivot: turns rows into columns (e.g., sales per month as columns) using PIVOT or conditional aggregation (MAX(CASE WHEN ... END)).
โข Unpivot: turns columns into rows (e.g., multiple month columns โ one month column) using UNPIVOT or UNION ALL/VALUES.
11. What is LAG() and LEAD()?
โข LAG(col, n): value of col from n rows before current row.
โข LEAD(col, n): value from n rows after. Used for timeโseries analysis (MoM change, prior/next values).
12. How do you handle NULL in aggregates?
Most aggregates (SUM, AVG, MAX, MIN) ignore NULL.
โข COUNT(col) ignores NULL; COUNT(*) counts all rows.
โข Use COALESCE() or ISNULL() to replace NULL before aggregating.
13. What is the difference between VIEW and MATERIALIZED VIEW?
โข VIEW: virtual table; query runs every time you select.
โข MATERIALIZED VIEW: stores result physically and refreshes periodically; faster reads, slower updates.
14. Explain ACID properties.
โข Atomicity: transaction is "all or nothing".
โข Consistency: valid state before and after.
โข Isolation: concurrent transactions don't interfere.
โข Durability: committed changes survive crashes.
15. How do you optimize a slow query?
โข Add proper indexes on WHERE, JOIN, ORDER BY columns.
โข Remove unnecessary SELECT *, DISTINCT, or functions on indexed columns.
โข Check execution plan and avoid large scans; use LIMIT or partitioning if possible.
16. What is the difference between INNER JOIN and EXISTS?
โข INNER JOIN: returns combined columns from both tables where keys match.
โข EXISTS: checks if a subquery returns any rows; usually faster when you only care about existence (e.g., filtering with WHERE EXISTS).
โค7
๐ ๐๐๐ฅ๐ฉ๐๐ฅ๐ ๐จ๐ก๐๐ฉ๐๐ฅ๐ฆ๐๐ง๐ฌ ๐๐ฅ๐๐ ๐ข๐ก๐๐๐ก๐ ๐๐ข๐จ๐ฅ๐ฆ๐๐ฆ ๐
Dreaming of learning from one of the worldโs most prestigious universities? Explore Harvardโs online courses and build valuable, career-ready skills from home!
๐ก Beginner-friendly options
โฐ Learn at your own pace
๐ Accessible online worldwide
๐ฏ Ideal for students, freshers and working professionals
๐ ๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ฅ๐๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
https://pdlink.in/4xPUdzU
๐ข Share this valuable opportunity with your friends and classmates!
Dreaming of learning from one of the worldโs most prestigious universities? Explore Harvardโs online courses and build valuable, career-ready skills from home!
๐ก Beginner-friendly options
โฐ Learn at your own pace
๐ Accessible online worldwide
๐ฏ Ideal for students, freshers and working professionals
๐ ๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ฅ๐๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
https://pdlink.in/4xPUdzU
๐ข Share this valuable opportunity with your friends and classmates!
โค1
๐ Data Analyst Roadmap โ Part 30
POWER BI LEVEL 9 โ DAX VARIABLES: VAR, RETURN & CLEANER DAX
As DAX calculations become more complex, writing everything in one expression can make your measures difficult to understand and maintain.
That's where VAR and RETURN become extremely useful.
๐น 1. What is VAR?
VAR allows you to store the result of a calculation in a variable.
Example:
Profit =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
RETURN
Revenue - Cost
Instead of repeating [Total Sales] and [Total Cost], we give them meaningful names. The calculation becomes easier to read.
๐น 2. What does RETURN do?
RETURN tells DAX which final result should be returned.
VAR โ Create temporary values
RETURN โ Give me the final result
Example:
Profit Margin =
VAR Profit = [Total Profit]
VAR Sales = [Total Sales]
RETURN
DIVIDE(Profit, Sales)
๐น 3. Why use Variables?
Without variables:
Profit Margin =
DIVIDE(
[Total Sales] - [Total Cost],
[Total Sales]
)
With variables:
Profit Margin =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
VAR Profit = Sales - Cost
RETURN
DIVIDE(Profit, Sales)
The second version is easier to understand. You can immediately see: Sales, Cost, Profit, Profit Margin
๐น 4. Variables Can Store Numbers
Example:
Sales Target Status =
VAR Sales = [Total Sales]
VAR Target = 1000000
RETURN
IF(
Sales >= Target,
"Target Achieved",
"Below Target"
)
Now the business rule is much easier to read.
๐น 5. Variables Can Store Text
Variables don't have to contain numbers.
Example:
Region Message =
VAR Region =
SELECTEDVALUE(
Sales[Region],
"Multiple Regions"
)
RETURN
"Current Region: " & Region
If West is selected: "Current Region: West"
๐น 6. Variables Can Store Tables
This is where DAX starts becoming more powerful. A variable can also contain a table expression.
Example:
High Value Customers =
VAR Customers =
FILTER(
VALUES(Sales[CustomerID]),
[Total Sales] > 100000
)
RETURN
COUNTROWS(Customers)
Here: "Customers" stores a temporary table. Then COUNTROWS() counts how many customers are in that table.
๐น 7. Variables and FILTER()
Variables make complex filtering easier to understand.
Example:
High Value Sales =
VAR FilteredSales =
FILTER(
Sales,
Sales[SalesAmount] > 10000
)
RETURN
SUMX(
FilteredSales,
Sales[SalesAmount]
)
Instead of putting everything into one long expression, we separate the logic into meaningful steps.
๐น 8. Variables Are Evaluated Once
A useful performance benefit is that variables can avoid repeatedly evaluating the same expression.
For example, instead of repeatedly calculating [Total Sales] you can store it:
VAR Sales = [Total Sales]
and reuse Sales. This can make complex measures cleaner and, in some cases, more efficient.
๐น 9. Variables Improve Debugging
Suppose you have:
Profit Analysis =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
VAR Profit = Sales - Cost
VAR Margin = DIVIDE(Profit, Sales)
RETURN
Margin
If the final result looks incorrect, you can temporarily change the RETURN statement to:
RETURN Profit
or
RETURN Cost
This makes it easier to understand where the calculation is going wrong.
๐น 10. Variables Don't Create Model Columns
This is important. A variable inside a measure VAR Sales = [Total Sales] does NOT create a permanent column in your Power BI model. It exists only while that measure is being evaluated.
So:
Calculated Column โ stored in the model
Measure Variable โ temporary value during calculation
๐น 11. Real Business Example
Suppose management wants to classify performance:
Sales โฅ โน10M โ Excellent
Sales โฅ โน5M โ Good
Sales โฅ โน2M โ Average
Below โน2M โ Needs Attention
You can write:
Sales Performance =
VAR Sales = [Total Sales]
RETURN
SWITCH(
TRUE(),
Sales >= 10000000, "Excellent",
Sales >= 5000000, "Good",
Sales >= 2000000, "Average",
"Needs Attention"
)
POWER BI LEVEL 9 โ DAX VARIABLES: VAR, RETURN & CLEANER DAX
As DAX calculations become more complex, writing everything in one expression can make your measures difficult to understand and maintain.
That's where VAR and RETURN become extremely useful.
๐น 1. What is VAR?
VAR allows you to store the result of a calculation in a variable.
Example:
Profit =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
RETURN
Revenue - Cost
Instead of repeating [Total Sales] and [Total Cost], we give them meaningful names. The calculation becomes easier to read.
๐น 2. What does RETURN do?
RETURN tells DAX which final result should be returned.
VAR โ Create temporary values
RETURN โ Give me the final result
Example:
Profit Margin =
VAR Profit = [Total Profit]
VAR Sales = [Total Sales]
RETURN
DIVIDE(Profit, Sales)
๐น 3. Why use Variables?
Without variables:
Profit Margin =
DIVIDE(
[Total Sales] - [Total Cost],
[Total Sales]
)
With variables:
Profit Margin =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
VAR Profit = Sales - Cost
RETURN
DIVIDE(Profit, Sales)
The second version is easier to understand. You can immediately see: Sales, Cost, Profit, Profit Margin
๐น 4. Variables Can Store Numbers
Example:
Sales Target Status =
VAR Sales = [Total Sales]
VAR Target = 1000000
RETURN
IF(
Sales >= Target,
"Target Achieved",
"Below Target"
)
Now the business rule is much easier to read.
๐น 5. Variables Can Store Text
Variables don't have to contain numbers.
Example:
Region Message =
VAR Region =
SELECTEDVALUE(
Sales[Region],
"Multiple Regions"
)
RETURN
"Current Region: " & Region
If West is selected: "Current Region: West"
๐น 6. Variables Can Store Tables
This is where DAX starts becoming more powerful. A variable can also contain a table expression.
Example:
High Value Customers =
VAR Customers =
FILTER(
VALUES(Sales[CustomerID]),
[Total Sales] > 100000
)
RETURN
COUNTROWS(Customers)
Here: "Customers" stores a temporary table. Then COUNTROWS() counts how many customers are in that table.
๐น 7. Variables and FILTER()
Variables make complex filtering easier to understand.
Example:
High Value Sales =
VAR FilteredSales =
FILTER(
Sales,
Sales[SalesAmount] > 10000
)
RETURN
SUMX(
FilteredSales,
Sales[SalesAmount]
)
Instead of putting everything into one long expression, we separate the logic into meaningful steps.
๐น 8. Variables Are Evaluated Once
A useful performance benefit is that variables can avoid repeatedly evaluating the same expression.
For example, instead of repeatedly calculating [Total Sales] you can store it:
VAR Sales = [Total Sales]
and reuse Sales. This can make complex measures cleaner and, in some cases, more efficient.
๐น 9. Variables Improve Debugging
Suppose you have:
Profit Analysis =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
VAR Profit = Sales - Cost
VAR Margin = DIVIDE(Profit, Sales)
RETURN
Margin
If the final result looks incorrect, you can temporarily change the RETURN statement to:
RETURN Profit
or
RETURN Cost
This makes it easier to understand where the calculation is going wrong.
๐น 10. Variables Don't Create Model Columns
This is important. A variable inside a measure VAR Sales = [Total Sales] does NOT create a permanent column in your Power BI model. It exists only while that measure is being evaluated.
So:
Calculated Column โ stored in the model
Measure Variable โ temporary value during calculation
๐น 11. Real Business Example
Suppose management wants to classify performance:
Sales โฅ โน10M โ Excellent
Sales โฅ โน5M โ Good
Sales โฅ โน2M โ Average
Below โน2M โ Needs Attention
You can write:
Sales Performance =
VAR Sales = [Total Sales]
RETURN
SWITCH(
TRUE(),
Sales >= 10000000, "Excellent",
Sales >= 5000000, "Good",
Sales >= 2000000, "Average",
"Needs Attention"
)
โค3
This is much easier to maintain than repeatedly writing [Total Sales].
๐น 12. Best Practices
When writing complex DAX:
โ Give variables meaningful names
โ Break complicated calculations into logical steps
โ Avoid repeating the same expression
โ Use RETURN for the final result
โ Keep business logic readable
โ Use variables to make debugging easier
Avoid meaningless names such as VAR X =...
Prefer VAR TotalSales =...
Clear names make your DAX easier for another analyst to understand.
๐ฏ Interview Questions
1๏ธโฃ What is VAR in DAX?
VAR creates a temporary variable that stores a value or table expression during calculation.
2๏ธโฃ What does RETURN do?
It specifies the final expression that the measure should return.
3๏ธโฃ Are DAX variables stored permanently in the model?
No. Variables exist only during the evaluation of the expression.
4๏ธโฃ Why should you use variables?
They improve readability, reduce repeated calculations, and make complex DAX easier to debug.
5๏ธโฃ Can a DAX variable contain a table?
Yes. A variable can store either a scalar value or a table expression.
๐งช PRACTICE
Create these measures using VAR:
โ Total Profit
โ Profit Margin
โ Sales Target Status
โ Sales Performance
โ Selected Region Message
Then try to rewrite one of your older complex DAX measures using variables.
๐ก Double Tap โค๏ธ For More
๐น 12. Best Practices
When writing complex DAX:
โ Give variables meaningful names
โ Break complicated calculations into logical steps
โ Avoid repeating the same expression
โ Use RETURN for the final result
โ Keep business logic readable
โ Use variables to make debugging easier
Avoid meaningless names such as VAR X =...
Prefer VAR TotalSales =...
Clear names make your DAX easier for another analyst to understand.
๐ฏ Interview Questions
1๏ธโฃ What is VAR in DAX?
VAR creates a temporary variable that stores a value or table expression during calculation.
2๏ธโฃ What does RETURN do?
It specifies the final expression that the measure should return.
3๏ธโฃ Are DAX variables stored permanently in the model?
No. Variables exist only during the evaluation of the expression.
4๏ธโฃ Why should you use variables?
They improve readability, reduce repeated calculations, and make complex DAX easier to debug.
5๏ธโฃ Can a DAX variable contain a table?
Yes. A variable can store either a scalar value or a table expression.
๐งช PRACTICE
Create these measures using VAR:
โ Total Profit
โ Profit Margin
โ Sales Target Status
โ Sales Performance
โ Selected Region Message
Then try to rewrite one of your older complex DAX measures using variables.
๐ก Double Tap โค๏ธ For More
โค3๐1
๐ Data Analyst Interview Series โ Part 1
Guys, let's start a Data Analyst Interview Series where I'll cover the most important questions that are commonly asked in Data Analyst interviews.
I'll cover SQL, Excel, Power BI, Python, statistics, data cleaning, case studies, business questions, and scenario-based questions.
Let's start with the basics ๐
1๏ธโฃ Tell me about yourself.
Sample Answer:
"I'm a Data Analyst with experience working with SQL, Excel, Power BI, Python, and data visualization. My work involves extracting and transforming data, analyzing business problems, building dashboards, and automating repetitive reporting processes. I focus not just on creating reports, but on understanding the business requirement and converting data into actionable insights."
2๏ธโฃ What does a Data Analyst do?
Sample Answer:
"A Data Analyst collects, cleans, transforms, and analyzes data to help businesses make informed decisions. A typical workflow involves understanding the business requirement, collecting relevant data, cleaning it, performing analysis, identifying trends or patterns, and presenting the findings through reports or dashboards."
3๏ธโฃ What is the difference between Data Analysis and Data Analytics?
Sample Answer:
"Data analysis generally focuses on examining data to understand what happened and why. Data analytics is a broader concept that includes data analysis along with processes such as data collection, preparation, visualization, statistical analysis, and sometimes predictive modeling. In practice, the terms are often used interchangeably depending on the organization."
4๏ธโฃ What is the difference between structured and unstructured data?
Sample Answer:
"Structured data has a predefined format or schema, such as rows and columns in a relational database. Examples include customer IDs, transaction amounts, and dates.
Unstructured data does not follow a predefined tabular structure. Examples include emails, images, videos, documents, and social media posts.
Semi-structured data sits between the two, such as JSON and XML, where the data has some organizational structure but doesn't necessarily follow a relational table format."
5๏ธโฃ What is data cleaning and why is it important?
Sample Answer:
"Data cleaning is the process of identifying and correcting problems in a dataset, such as missing values, duplicates, inconsistent formats, incorrect data types, and invalid values.
It is important because analysis performed on poor-quality data can produce misleading results. Before analyzing data, I would first understand the data quality issues and determine how each issue should be handled based on the business context."
6๏ธโฃ How do you handle missing values?
Sample Answer:
"I first investigate why the values are missing and how much data is affected. The appropriate treatment depends on the business context.
For example, I might remove records if only a very small number are affected and they aren't important to the analysis. For numerical fields, I might use an appropriate statistical value such as median or mean when justified. For categorical fields, I might use a meaningful category such as 'Unknown.'
I avoid blindly replacing missing values because missingness itself can sometimes contain useful information."
7๏ธโฃ How do you identify duplicate records?
Sample Answer:
"I first determine what defines a unique record. Then I compare the relevant columns or business key to identify duplicates.
For example, if Customer_ID and Transaction_ID together uniquely identify a transaction, I can use those fields to identify duplicate combinations.
In SQL, I could use GROUP BY with HAVING COUNT(**) > 1 to identify duplicated keys."
Guys, let's start a Data Analyst Interview Series where I'll cover the most important questions that are commonly asked in Data Analyst interviews.
I'll cover SQL, Excel, Power BI, Python, statistics, data cleaning, case studies, business questions, and scenario-based questions.
Let's start with the basics ๐
1๏ธโฃ Tell me about yourself.
Sample Answer:
"I'm a Data Analyst with experience working with SQL, Excel, Power BI, Python, and data visualization. My work involves extracting and transforming data, analyzing business problems, building dashboards, and automating repetitive reporting processes. I focus not just on creating reports, but on understanding the business requirement and converting data into actionable insights."
2๏ธโฃ What does a Data Analyst do?
Sample Answer:
"A Data Analyst collects, cleans, transforms, and analyzes data to help businesses make informed decisions. A typical workflow involves understanding the business requirement, collecting relevant data, cleaning it, performing analysis, identifying trends or patterns, and presenting the findings through reports or dashboards."
3๏ธโฃ What is the difference between Data Analysis and Data Analytics?
Sample Answer:
"Data analysis generally focuses on examining data to understand what happened and why. Data analytics is a broader concept that includes data analysis along with processes such as data collection, preparation, visualization, statistical analysis, and sometimes predictive modeling. In practice, the terms are often used interchangeably depending on the organization."
4๏ธโฃ What is the difference between structured and unstructured data?
Sample Answer:
"Structured data has a predefined format or schema, such as rows and columns in a relational database. Examples include customer IDs, transaction amounts, and dates.
Unstructured data does not follow a predefined tabular structure. Examples include emails, images, videos, documents, and social media posts.
Semi-structured data sits between the two, such as JSON and XML, where the data has some organizational structure but doesn't necessarily follow a relational table format."
5๏ธโฃ What is data cleaning and why is it important?
Sample Answer:
"Data cleaning is the process of identifying and correcting problems in a dataset, such as missing values, duplicates, inconsistent formats, incorrect data types, and invalid values.
It is important because analysis performed on poor-quality data can produce misleading results. Before analyzing data, I would first understand the data quality issues and determine how each issue should be handled based on the business context."
6๏ธโฃ How do you handle missing values?
Sample Answer:
"I first investigate why the values are missing and how much data is affected. The appropriate treatment depends on the business context.
For example, I might remove records if only a very small number are affected and they aren't important to the analysis. For numerical fields, I might use an appropriate statistical value such as median or mean when justified. For categorical fields, I might use a meaningful category such as 'Unknown.'
I avoid blindly replacing missing values because missingness itself can sometimes contain useful information."
7๏ธโฃ How do you identify duplicate records?
Sample Answer:
"I first determine what defines a unique record. Then I compare the relevant columns or business key to identify duplicates.
For example, if Customer_ID and Transaction_ID together uniquely identify a transaction, I can use those fields to identify duplicate combinations.
In SQL, I could use GROUP BY with HAVING COUNT(**) > 1 to identify duplicated keys."
SELECT Customer_ID, Transaction_ID, COUNT(**) AS duplicate_count
FROM transactions
GROUP BY Customer_ID, Transaction_ID
HAVING COUNT(**) > 1;
โค7
"After identifying duplicates, I investigate whether they are genuine duplicate records or legitimate repeated transactions before removing anything."
8๏ธโฃ What is an outlier? How would you handle it?
Sample Answer:
"An outlier is a value that is significantly different from the typical observations in a dataset.
I wouldn't automatically remove an outlier. First, I would investigate whether it represents a data-quality issue or a genuine business event.
For example, a transaction worth โน10 million might initially look like an outlier, but it could be a legitimate high-value transaction. If it is a data-entry error, I would correct or exclude it according to the business rules."
9๏ธโฃ What is the difference between a dimension and a measure?
Sample Answer:
"A dimension is generally used to categorize or describe data, while a measure is a numerical value that can usually be aggregated.
For example, in a sales dataset:
Dimensions: Customer, Product, Region, Date
Measures: Sales Amount, Quantity, Profit, Discount
In a dashboard, dimensions are commonly used to slice or group the data, while measures are used to calculate KPIs and metrics."
๐ What steps do you follow when solving a data analysis problem?
Sample Answer:
"I generally follow a structured approach:
1. Understand the business problem.
2. Define the required metrics and success criteria.
3. Identify the relevant data sources.
4. Extract and validate the data.
5. Clean and transform the data.
6. Perform exploratory analysis.
7. Identify trends, patterns, and anomalies.
8. Validate the results.
9. Communicate the insights using appropriate visualizations.
10. Recommend actions based on the findings.
The most important step is understanding the business question first, because technically correct analysis can still be useless if it doesn't answer the actual business problem."
๐ Double Tap โค๏ธ For Part-2
8๏ธโฃ What is an outlier? How would you handle it?
Sample Answer:
"An outlier is a value that is significantly different from the typical observations in a dataset.
I wouldn't automatically remove an outlier. First, I would investigate whether it represents a data-quality issue or a genuine business event.
For example, a transaction worth โน10 million might initially look like an outlier, but it could be a legitimate high-value transaction. If it is a data-entry error, I would correct or exclude it according to the business rules."
9๏ธโฃ What is the difference between a dimension and a measure?
Sample Answer:
"A dimension is generally used to categorize or describe data, while a measure is a numerical value that can usually be aggregated.
For example, in a sales dataset:
Dimensions: Customer, Product, Region, Date
Measures: Sales Amount, Quantity, Profit, Discount
In a dashboard, dimensions are commonly used to slice or group the data, while measures are used to calculate KPIs and metrics."
๐ What steps do you follow when solving a data analysis problem?
Sample Answer:
"I generally follow a structured approach:
1. Understand the business problem.
2. Define the required metrics and success criteria.
3. Identify the relevant data sources.
4. Extract and validate the data.
5. Clean and transform the data.
6. Perform exploratory analysis.
7. Identify trends, patterns, and anomalies.
8. Validate the results.
9. Communicate the insights using appropriate visualizations.
10. Recommend actions based on the findings.
The most important step is understanding the business question first, because technically correct analysis can still be useless if it doesn't answer the actual business problem."
๐ Double Tap โค๏ธ For Part-2
โค12
๐๐ฅ๐๐ ๐ฅ๐ฒ๐๐ผ๐๐ฟ๐ฐ๐ฒ๐ ๐ง๐ผ ๐๐ฒ๐ฎ๐ฟ๐ป ๐๐ ๐ถ๐ป ๐ฎ๐ฌ๐ฎ๐ฒ๐
โ
Explore 6 free resources covering AI fundamentals, tools, deep learning, research and real-world applications.
โ 100% Free Learning
โ Beginner-Friendly
โ AI โข ML โข Deep Learning
โ Real-World Applications
๐ ๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ฅ๐๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
https://pdlink.in/4AFHq5R
๐ข Share this valuable opportunity with your friends and classmates!
โ
Explore 6 free resources covering AI fundamentals, tools, deep learning, research and real-world applications.
โ 100% Free Learning
โ Beginner-Friendly
โ AI โข ML โข Deep Learning
โ Real-World Applications
๐ ๐๐ ๐ฝ๐น๐ผ๐ฟ๐ฒ ๐๐ฅ๐๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
https://pdlink.in/4AFHq5R
๐ข Share this valuable opportunity with your friends and classmates!
โค1
๐ Data Analyst Interview Series โ Part 2
Guys, let's continue our Data Analyst Interview Series.
In Part 2, let's move into some important SQL and data-related interview questions that are frequently tested in Data Analyst interviews. ๐
1๏ธโฃ What is SQL and why is it important for a Data Analyst?
Sample Answer:
"SQL stands for Structured Query Language. It is used to interact with relational databases. As a Data Analyst, I use SQL to retrieve, filter, join, aggregate, and analyze data. It is important because a large amount of business data is stored in databases, and SQL allows analysts to efficiently extract the data required for analysis."
2๏ธโฃ What is the difference between WHERE and HAVING?
Sample Answer:
"WHERE filters individual rows before aggregation, whereas HAVING filters groups after aggregation.
For example, if I want to find customers whose total sales exceed โน1 lakh, I would use HAVING because the condition is applied to an aggregated result."
3๏ธโฃ What is the difference between INNER JOIN and LEFT JOIN?
Sample Answer:
"An INNER JOIN returns only the records that have matching values in both tables.
A LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no match, the columns from the right table contain NULL."
For example, if I want all customers, including customers who haven't placed any orders, I would use a LEFT JOIN.
4๏ธโฃ What is a primary key?
Sample Answer:
"A primary key is a column or combination of columns that uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values.
For example, Customer_ID can be a primary key in a Customer table if every customer has a unique ID."
5๏ธโฃ What is a foreign key?
Sample Answer:
"A foreign key is a column that references a primary key or another unique key in another table. It establishes a relationship between tables.
For example, Customer_ID in an Orders table can reference Customer_ID in the Customers table."
6๏ธโฃ What is the difference between UNION and UNION ALL?
Sample Answer:
"Both are used to combine the results of two or more SELECT statements.
UNION removes duplicate records from the combined result, while UNION ALL retains duplicates.
Because UNION performs duplicate elimination, UNION ALL can generally be faster when duplicate removal isn't required."
7๏ธโฃ What is a NULL value in SQL?
Sample Answer:
"NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or a blank value.
We should use IS NULL or IS NOT NULL to check for NULL values rather than using an equals operator."
8๏ธโฃ What is GROUP BY used for?
Sample Answer:
"GROUP BY is used to group rows that have the same values in one or more columns so that aggregate functions can be applied to each group.
For example, to calculate total sales by region:"
9๏ธโฃ What are aggregate functions in SQL?
Sample Answer:
"Aggregate functions perform calculations on multiple rows and return a single result for each group.
Common aggregate functions include:"
โข COUNT() โ counts records
โข SUM() โ calculates the total
โข AVG() โ calculates the average
โข MIN() โ finds the minimum value
โข MAX() โ finds the maximum value
For example:
Guys, let's continue our Data Analyst Interview Series.
In Part 2, let's move into some important SQL and data-related interview questions that are frequently tested in Data Analyst interviews. ๐
1๏ธโฃ What is SQL and why is it important for a Data Analyst?
Sample Answer:
"SQL stands for Structured Query Language. It is used to interact with relational databases. As a Data Analyst, I use SQL to retrieve, filter, join, aggregate, and analyze data. It is important because a large amount of business data is stored in databases, and SQL allows analysts to efficiently extract the data required for analysis."
2๏ธโฃ What is the difference between WHERE and HAVING?
Sample Answer:
"WHERE filters individual rows before aggregation, whereas HAVING filters groups after aggregation.
For example, if I want to find customers whose total sales exceed โน1 lakh, I would use HAVING because the condition is applied to an aggregated result."
SELECT Customer_ID, SUM(Sales) AS Total_Sales
FROM Sales
GROUP BY Customer_ID
HAVING SUM(Sales) > 100000;
3๏ธโฃ What is the difference between INNER JOIN and LEFT JOIN?
Sample Answer:
"An INNER JOIN returns only the records that have matching values in both tables.
A LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no match, the columns from the right table contain NULL."
For example, if I want all customers, including customers who haven't placed any orders, I would use a LEFT JOIN.
4๏ธโฃ What is a primary key?
Sample Answer:
"A primary key is a column or combination of columns that uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values.
For example, Customer_ID can be a primary key in a Customer table if every customer has a unique ID."
5๏ธโฃ What is a foreign key?
Sample Answer:
"A foreign key is a column that references a primary key or another unique key in another table. It establishes a relationship between tables.
For example, Customer_ID in an Orders table can reference Customer_ID in the Customers table."
6๏ธโฃ What is the difference between UNION and UNION ALL?
Sample Answer:
"Both are used to combine the results of two or more SELECT statements.
UNION removes duplicate records from the combined result, while UNION ALL retains duplicates.
Because UNION performs duplicate elimination, UNION ALL can generally be faster when duplicate removal isn't required."
7๏ธโฃ What is a NULL value in SQL?
Sample Answer:
"NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or a blank value.
We should use IS NULL or IS NOT NULL to check for NULL values rather than using an equals operator."
SELECT *
FROM Customers
WHERE Email IS NULL;
8๏ธโฃ What is GROUP BY used for?
Sample Answer:
"GROUP BY is used to group rows that have the same values in one or more columns so that aggregate functions can be applied to each group.
For example, to calculate total sales by region:"
SELECT Region, SUM(Sales) AS Total_Sales
FROM Sales
GROUP BY Region;
9๏ธโฃ What are aggregate functions in SQL?
Sample Answer:
"Aggregate functions perform calculations on multiple rows and return a single result for each group.
Common aggregate functions include:"
โข COUNT() โ counts records
โข SUM() โ calculates the total
โข AVG() โ calculates the average
โข MIN() โ finds the minimum value
โข MAX() โ finds the maximum value
For example:
SELECT
COUNT(*) AS Total_Orders,
SUM(Sales) AS Total_Sales,
AVG(Sales) AS Average_Sales
FROM Sales;
โค4
๐ How would you find duplicate records in SQL?
Sample Answer:
"I would first identify the column or combination of columns that should uniquely identify a record. Then I would use GROUP BY and HAVING COUNT(*) > 1."
"This identifies Customer_ID values that appear more than once. I would then investigate whether those records are genuine duplicates before taking any corrective action."
๐ Double Tap โค๏ธ For Part-3
Sample Answer:
"I would first identify the column or combination of columns that should uniquely identify a record. Then I would use GROUP BY and HAVING COUNT(*) > 1."
SELECT Customer_ID, COUNT(*) AS Count_Records
FROM Customers
GROUP BY Customer_ID
HAVING COUNT(*) > 1;
"This identifies Customer_ID values that appear more than once. I would then investigate whether those records are genuine duplicates before taking any corrective action."
๐ Double Tap โค๏ธ For Part-3
โค15
๐ Data Analyst Interview Series โ Part 3
Guys, let's continue our Data Analyst Interview Series.
Today, let's cover 10 important SQL interview questions that test your practical SQL knowledge. ๐
1๏ธโฃ What is a subquery in SQL?
Sample Answer:
โA subquery is a query written inside another SQL query. It can be used to retrieve intermediate results that are then used by the outer query.
For example, to find employees whose salary is greater than the average salary:โ
2๏ธโฃ What is a CTE?
Sample Answer:
โCTE stands for Common Table Expression. It allows us to define a temporary named result set using the WITH clause, which can then be referenced within the main query.
CTEs make complex queries easier to read, maintain, and debug.โ
3๏ธโฃ What is a window function?
Sample Answer:
โA window function performs a calculation across a set of related rows while still retaining the individual rows in the result.
Unlike GROUP BY, it does not collapse multiple rows into a single row.
Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD().โ
4๏ธโฃ What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
Sample Answer:
โROW_NUMBER() assigns a unique sequential number to every row.
RANK() assigns the same rank to tied values but leaves gaps after a tie.
DENSE_RANK() also assigns the same rank to tied values but does not leave gaps.โ
Example:
Values: 100, 100, 90
ROW_NUMBER: 1, 2, 3
RANK: 1, 1, 3
DENSE_RANK: 1, 1, 2
5๏ธโฃ How would you find the second-highest salary?
Sample Answer:
โOne approach is to use DENSE_RANK(). This also handles duplicate salaries correctly.โ
6๏ธโฃ How would you find the top 3 salaries in each department?
Sample Answer:
โI would use a window function to rank employees within each department.โ
โThe PARTITION BY ensures that ranking starts separately for each department.โ
7๏ธโฃ What is PARTITION BY in SQL?
Sample Answer:
โPARTITION BY divides the result set into groups for a window function without collapsing the rows.
For example, if I want to rank employees separately within each department, I can use PARTITION BY Department.โ
8๏ธโฃ What are LAG() and LEAD() functions?
Sample Answer:
โLAG() allows me to access a value from a previous row, while LEAD() allows me to access a value from a following row.
They are particularly useful for comparing current values with previous or future values, such as month-over-month sales.โ
Guys, let's continue our Data Analyst Interview Series.
Today, let's cover 10 important SQL interview questions that test your practical SQL knowledge. ๐
1๏ธโฃ What is a subquery in SQL?
Sample Answer:
โA subquery is a query written inside another SQL query. It can be used to retrieve intermediate results that are then used by the outer query.
For example, to find employees whose salary is greater than the average salary:โ
SELECT Employee_ID, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
2๏ธโฃ What is a CTE?
Sample Answer:
โCTE stands for Common Table Expression. It allows us to define a temporary named result set using the WITH clause, which can then be referenced within the main query.
CTEs make complex queries easier to read, maintain, and debug.โ
WITH CustomerSales AS (
SELECT Customer_ID,
SUM(Sales) AS Total_Sales
FROM Sales
GROUP BY Customer_ID
)
SELECT *
FROM CustomerSales
WHERE Total_Sales > 100000;
3๏ธโฃ What is a window function?
Sample Answer:
โA window function performs a calculation across a set of related rows while still retaining the individual rows in the result.
Unlike GROUP BY, it does not collapse multiple rows into a single row.
Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD().โ
4๏ธโฃ What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
Sample Answer:
โROW_NUMBER() assigns a unique sequential number to every row.
RANK() assigns the same rank to tied values but leaves gaps after a tie.
DENSE_RANK() also assigns the same rank to tied values but does not leave gaps.โ
Example:
Values: 100, 100, 90
ROW_NUMBER: 1, 2, 3
RANK: 1, 1, 3
DENSE_RANK: 1, 1, 2
5๏ธโฃ How would you find the second-highest salary?
Sample Answer:
โOne approach is to use DENSE_RANK(). This also handles duplicate salaries correctly.โ
WITH RankedEmployees AS (
SELECT Employee_ID,
Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
)
SELECT Employee_ID, Salary
FROM RankedEmployees
WHERE Salary_Rank = 2;
6๏ธโฃ How would you find the top 3 salaries in each department?
Sample Answer:
โI would use a window function to rank employees within each department.โ
WITH RankedEmployees AS (
SELECT Employee_ID,
Department,
Salary,
DENSE_RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Salary_Rank
FROM Employees
)
SELECT *
FROM RankedEmployees
WHERE Salary_Rank <= 3;
โThe PARTITION BY ensures that ranking starts separately for each department.โ
7๏ธโฃ What is PARTITION BY in SQL?
Sample Answer:
โPARTITION BY divides the result set into groups for a window function without collapsing the rows.
For example, if I want to rank employees separately within each department, I can use PARTITION BY Department.โ
SELECT Employee_ID,
Department,
Salary,
RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Salary_Rank
FROM Employees;
8๏ธโฃ What are LAG() and LEAD() functions?
Sample Answer:
โLAG() allows me to access a value from a previous row, while LEAD() allows me to access a value from a following row.
They are particularly useful for comparing current values with previous or future values, such as month-over-month sales.โ
SELECT Month,
Sales,
LAG(Sales) OVER (ORDER BY Month) AS Previous_Month_Sales
FROM Monthly_Sales;
โค2
9๏ธโฃ How would you calculate month-over-month growth?
Sample Answer:
โI would first retrieve the previous month's sales using LAG(), then calculate the percentage change between the current month and previous month.โ
โI would also handle cases where the previous month's value is zero or NULL to avoid incorrect calculations.โ
๐ What is the difference between DELETE, TRUNCATE, and DROP?
Sample Answer:
โDELETE removes selected rows from a table and can be used with a WHERE condition.
TRUNCATE removes all rows from a table while keeping the table structure.
DROP removes the entire table, including its structure and data.
So, the key difference is whether I'm removing specific records, all records, or the entire table itself.โ
๐ Double Tap โค๏ธ For Part-4
Sample Answer:
โI would first retrieve the previous month's sales using LAG(), then calculate the percentage change between the current month and previous month.โ
SELECT Month,
Sales,
LAG(Sales) OVER (ORDER BY Month) AS Previous_Sales,
(Sales - LAG(Sales) OVER (ORDER BY Month))
* 100.0 /
LAG(Sales) OVER (ORDER BY Month) AS MoM_Growth
FROM Monthly_Sales;
โI would also handle cases where the previous month's value is zero or NULL to avoid incorrect calculations.โ
๐ What is the difference between DELETE, TRUNCATE, and DROP?
Sample Answer:
โDELETE removes selected rows from a table and can be used with a WHERE condition.
TRUNCATE removes all rows from a table while keeping the table structure.
DROP removes the entire table, including its structure and data.
So, the key difference is whether I'm removing specific records, all records, or the entire table itself.โ
๐ Double Tap โค๏ธ For Part-4
โค13