Data Analyst Interview Questions with Answers
1. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
5. Define shelves and sets in Tableau?
Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.
React ❤️ for more
1. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
5. Define shelves and sets in Tableau?
Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.
React ❤️ for more
❤2
80% of people who start learning data analytics never land a job.
Not because they lack skill
but because they get stuck in "preparation mode."
I was almost one of them.
I spent months:
-Taking courses.
-Watching YouTube tutorials.
-Practicing SQL and Power BI.
But when it came time to publish a project or apply for jobs
I hesitated.
“I need to learn more first.”
“My portfolio isn’t ready.”
“Maybe next month.”
Sound familiar?
You don’t need more knowledge
you need more execution.
Data analysts who build & share projects are 3X more likely to get hired.
The best analysts aren’t the smartest.
They’re the ones who take action.
-They publish dashboards, even if they aren’t perfect.
-They post case studies, even when they feel like imposters.
-They apply for jobs before they "feel ready"
Stop overthinking.
Pick a dataset, build something, and share it today.
One messy project is worth more than 100 courses you never use.
Not because they lack skill
but because they get stuck in "preparation mode."
I was almost one of them.
I spent months:
-Taking courses.
-Watching YouTube tutorials.
-Practicing SQL and Power BI.
But when it came time to publish a project or apply for jobs
I hesitated.
“I need to learn more first.”
“My portfolio isn’t ready.”
“Maybe next month.”
Sound familiar?
You don’t need more knowledge
you need more execution.
Data analysts who build & share projects are 3X more likely to get hired.
The best analysts aren’t the smartest.
They’re the ones who take action.
-They publish dashboards, even if they aren’t perfect.
-They post case studies, even when they feel like imposters.
-They apply for jobs before they "feel ready"
Stop overthinking.
Pick a dataset, build something, and share it today.
One messy project is worth more than 100 courses you never use.
❤5👍1
1. Explain the concept of transfer learning in the context of deep learning models. How can it be beneficial in practical applications?
Ans- Transfer learning involves leveraging pre-trained models on large datasets and adapting them to new, related tasks with smaller datasets. In deep learning, this is achieved by reusing the knowledge gained during the training of one model on a different, but related, task. This is particularly beneficial when the new task has limited labeled data.
Practical applications include image recognition, where a model pre-trained on a dataset like ImageNet can be fine-tuned for a specific domain. Transfer learning accelerates model convergence, requires less labeled data, and helps overcome the challenges of training deep neural networks from scratch.
2. Given a large dataset, how would you efficiently sample a representative subset for model training? Discuss the trade-offs involved.
Answer- To efficiently sample a representative subset, one can use techniques like random sampling or stratified sampling. For random sampling, simple random sampling or systematic sampling methods can be employed. For stratified sampling, data is divided into strata, and samples are randomly selected from each stratum.
Trade-offs involve the choice between biased and unbiased sampling. Random sampling may not capture rare events, while stratified sampling might introduce complexity but ensures representation. The size of the sample is also crucial; a too-small sample may not be representative, while a too-large sample may incur unnecessary computational costs.
3. How would you approach analyzing A/B test results to determine the effectiveness of a new feature on a platform like Google Search?
Answer: A/B testing involves comparing the performance of two versions (A and B) to determine the impact of a change. To analyze A/B test results:
- Define Metrics: Clearly define key metrics (e.g., click-through rate, user engagement) before the test.
- Random Assignment: Ensure random assignment of users to control (A) and experimental (B) groups.
- Statistical Significance: Use statistical tests (e.g., t-test) to determine if differences between groups are statistically significant.
- Practical Significance: Consider the practical significance of results to assess real-world impact.
- Segmentation: Analyze results across different user segments for nuanced insights.
4. You have access to search query logs. How would you identify and address potential biases in the search results?
Answer: To identify and address biases in search results:
- Analyze Demographics: Examine user demographics to identify biases related to age, gender, or location.
- Query Intent: Understand user query intent and ensure diverse queries are well-represented.
- Evaluate Results: Assess the diversity of results to avoid favoring specific perspectives.
- User Feedback: Gather feedback from users to identify biased or inappropriate results.
- Continuous Monitoring: Implement continuous monitoring and iterate on algorithms to minimize biases.
Ans- Transfer learning involves leveraging pre-trained models on large datasets and adapting them to new, related tasks with smaller datasets. In deep learning, this is achieved by reusing the knowledge gained during the training of one model on a different, but related, task. This is particularly beneficial when the new task has limited labeled data.
Practical applications include image recognition, where a model pre-trained on a dataset like ImageNet can be fine-tuned for a specific domain. Transfer learning accelerates model convergence, requires less labeled data, and helps overcome the challenges of training deep neural networks from scratch.
2. Given a large dataset, how would you efficiently sample a representative subset for model training? Discuss the trade-offs involved.
Answer- To efficiently sample a representative subset, one can use techniques like random sampling or stratified sampling. For random sampling, simple random sampling or systematic sampling methods can be employed. For stratified sampling, data is divided into strata, and samples are randomly selected from each stratum.
Trade-offs involve the choice between biased and unbiased sampling. Random sampling may not capture rare events, while stratified sampling might introduce complexity but ensures representation. The size of the sample is also crucial; a too-small sample may not be representative, while a too-large sample may incur unnecessary computational costs.
3. How would you approach analyzing A/B test results to determine the effectiveness of a new feature on a platform like Google Search?
Answer: A/B testing involves comparing the performance of two versions (A and B) to determine the impact of a change. To analyze A/B test results:
- Define Metrics: Clearly define key metrics (e.g., click-through rate, user engagement) before the test.
- Random Assignment: Ensure random assignment of users to control (A) and experimental (B) groups.
- Statistical Significance: Use statistical tests (e.g., t-test) to determine if differences between groups are statistically significant.
- Practical Significance: Consider the practical significance of results to assess real-world impact.
- Segmentation: Analyze results across different user segments for nuanced insights.
4. You have access to search query logs. How would you identify and address potential biases in the search results?
Answer: To identify and address biases in search results:
- Analyze Demographics: Examine user demographics to identify biases related to age, gender, or location.
- Query Intent: Understand user query intent and ensure diverse queries are well-represented.
- Evaluate Results: Assess the diversity of results to avoid favoring specific perspectives.
- User Feedback: Gather feedback from users to identify biased or inappropriate results.
- Continuous Monitoring: Implement continuous monitoring and iterate on algorithms to minimize biases.
❤2
📊🚀A beginner's roadmap for learning SQL:
🔹Understand Basics:
Learn what SQL is and its purpose in managing relational databases.
Understand basic database concepts like tables, rows, columns, and relationships.
🔹Learn SQL Syntax:
Familiarize yourself with SQL syntax for common commands like SELECT, INSERT, UPDATE, DELETE.
Understand clauses like WHERE, ORDER BY, GROUP BY, and JOIN.
🔹Setup a Database:
Install a relational database management system (RDBMS) like MySQL, SQLite, or PostgreSQL.
Practice creating databases, tables, and inserting data.
🔹Retrieve Data (SELECT):
Learn to retrieve data from a database using SELECT statements.
Practice filtering data using WHERE clause and sorting using ORDER BY.
🔹Modify Data (INSERT, UPDATE, DELETE):
Understand how to insert new records, update existing ones, and delete data.
Be cautious with DELETE to avoid unintentional data loss.
🔹Working with Functions:
Explore SQL functions like COUNT, AVG, SUM, MAX, MIN for data analysis.
Understand string functions, date functions, and mathematical functions.
🔹Data Filtering and Sorting:
Learn advanced filtering techniques using AND, OR, and IN operators.
Practice sorting data using multiple columns.
🔹Table Relationships (JOIN):
Understand the concept of joining tables to retrieve data from multiple tables.
Learn about INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
🔹Grouping and Aggregation:
Explore GROUP BY clause to group data based on specific columns.
Understand aggregate functions for summarizing data (SUM, AVG, COUNT).
🔹Subqueries:
Learn to use subqueries to perform complex queries.
Understand how to use subqueries in SELECT, WHERE, and FROM clauses.
🔹Indexes and Optimization:
Gain knowledge about indexes and their role in optimizing queries.
Understand how to optimize SQL queries for better performance.
🔹Transactions and ACID Properties:
Learn about transactions and the ACID properties (Atomicity, Consistency, Isolation, Durability).
Understand how to use transactions to maintain data integrity.
🔹Normalization:
Understand the basics of database normalization to design efficient databases.
Learn about 1NF, 2NF, 3NF, and BCNF.
🔹Backup and Recovery:
Understand the importance of database backups.
Learn how to perform backups and recovery operations.
🔹Practice and Projects:
Apply your knowledge through hands-on projects.
Practice on platforms like LeetCode, HackerRank, or build your own small database-driven projects.
👀👍Remember to practice regularly and build real-world projects to reinforce your learning. Happy coding!
🔹Understand Basics:
Learn what SQL is and its purpose in managing relational databases.
Understand basic database concepts like tables, rows, columns, and relationships.
🔹Learn SQL Syntax:
Familiarize yourself with SQL syntax for common commands like SELECT, INSERT, UPDATE, DELETE.
Understand clauses like WHERE, ORDER BY, GROUP BY, and JOIN.
🔹Setup a Database:
Install a relational database management system (RDBMS) like MySQL, SQLite, or PostgreSQL.
Practice creating databases, tables, and inserting data.
🔹Retrieve Data (SELECT):
Learn to retrieve data from a database using SELECT statements.
Practice filtering data using WHERE clause and sorting using ORDER BY.
🔹Modify Data (INSERT, UPDATE, DELETE):
Understand how to insert new records, update existing ones, and delete data.
Be cautious with DELETE to avoid unintentional data loss.
🔹Working with Functions:
Explore SQL functions like COUNT, AVG, SUM, MAX, MIN for data analysis.
Understand string functions, date functions, and mathematical functions.
🔹Data Filtering and Sorting:
Learn advanced filtering techniques using AND, OR, and IN operators.
Practice sorting data using multiple columns.
🔹Table Relationships (JOIN):
Understand the concept of joining tables to retrieve data from multiple tables.
Learn about INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
🔹Grouping and Aggregation:
Explore GROUP BY clause to group data based on specific columns.
Understand aggregate functions for summarizing data (SUM, AVG, COUNT).
🔹Subqueries:
Learn to use subqueries to perform complex queries.
Understand how to use subqueries in SELECT, WHERE, and FROM clauses.
🔹Indexes and Optimization:
Gain knowledge about indexes and their role in optimizing queries.
Understand how to optimize SQL queries for better performance.
🔹Transactions and ACID Properties:
Learn about transactions and the ACID properties (Atomicity, Consistency, Isolation, Durability).
Understand how to use transactions to maintain data integrity.
🔹Normalization:
Understand the basics of database normalization to design efficient databases.
Learn about 1NF, 2NF, 3NF, and BCNF.
🔹Backup and Recovery:
Understand the importance of database backups.
Learn how to perform backups and recovery operations.
🔹Practice and Projects:
Apply your knowledge through hands-on projects.
Practice on platforms like LeetCode, HackerRank, or build your own small database-driven projects.
👀👍Remember to practice regularly and build real-world projects to reinforce your learning. Happy coding!
❤1
Data Analyst Interview Questions
[Python, SQL, PowerBI]
1. Is indentation required in python?
Ans: Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.
2. What are Entities and Relationships?
Ans:
Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.
Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.
3. What are Aggregate and Scalar functions?
Ans: An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. A scalar function returns a single value based on the input value.
4. What are Custom Visuals in Power BI?
Ans: Custom Visuals are like any other visualizations, generated using Power BI. The only difference is that it develops the custom visuals using a custom SDK. The languages like JQuery and JavaScript are used to create custom visuals in Power BI
ENJOY LEARNING 👍👍
[Python, SQL, PowerBI]
1. Is indentation required in python?
Ans: Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.
2. What are Entities and Relationships?
Ans:
Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.
Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.
3. What are Aggregate and Scalar functions?
Ans: An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. A scalar function returns a single value based on the input value.
4. What are Custom Visuals in Power BI?
Ans: Custom Visuals are like any other visualizations, generated using Power BI. The only difference is that it develops the custom visuals using a custom SDK. The languages like JQuery and JavaScript are used to create custom visuals in Power BI
ENJOY LEARNING 👍👍
❤3👌1
1. What are the ways to detect outliers?
Outliers are detected using two methods:
Box Plot Method: According to this method, the value is considered an outlier if it exceeds or falls below 1.5*IQR (interquartile range), that is, if it lies above the top quartile (Q3) or below the bottom quartile (Q1).
Standard Deviation Method: According to this method, an outlier is defined as a value that is greater or lower than the mean ± (3*standard deviation).
2. What is a Recursive Stored Procedure?
A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
Outliers are detected using two methods:
Box Plot Method: According to this method, the value is considered an outlier if it exceeds or falls below 1.5*IQR (interquartile range), that is, if it lies above the top quartile (Q3) or below the bottom quartile (Q1).
Standard Deviation Method: According to this method, an outlier is defined as a value that is greater or lower than the mean ± (3*standard deviation).
2. What is a Recursive Stored Procedure?
A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
❤4
SQL Basics for Data Analysts
SQL (Structured Query Language) is used to retrieve, manipulate, and analyze data stored in databases.
1️⃣ Understanding Databases & Tables
Databases store structured data in tables.
Tables contain rows (records) and columns (fields).
Each column has a specific data type (INTEGER, VARCHAR, DATE, etc.).
2️⃣ Basic SQL Commands
Let's start with some fundamental queries:
🔹 SELECT – Retrieve Data
🔹 WHERE – Filter Data
🔹 ORDER BY – Sort Data
🔹 LIMIT – Restrict Number of Results
🔹 DISTINCT – Remove Duplicates
Mini Task for You: Try to write an SQL query to fetch the top 3 highest-paid employees from an "employees" table.
You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata
Like this post if you want me to continue covering all the topics! 👍❤️
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
#sql
SQL (Structured Query Language) is used to retrieve, manipulate, and analyze data stored in databases.
1️⃣ Understanding Databases & Tables
Databases store structured data in tables.
Tables contain rows (records) and columns (fields).
Each column has a specific data type (INTEGER, VARCHAR, DATE, etc.).
2️⃣ Basic SQL Commands
Let's start with some fundamental queries:
🔹 SELECT – Retrieve Data
SELECT * FROM employees; -- Fetch all columns from 'employees' table SELECT name, salary FROM employees; -- Fetch specific columns
🔹 WHERE – Filter Data
SELECT * FROM employees WHERE department = 'Sales'; -- Filter by department SELECT * FROM employees WHERE salary > 50000; -- Filter by salary
🔹 ORDER BY – Sort Data
SELECT * FROM employees ORDER BY salary DESC; -- Sort by salary (highest first) SELECT name, hire_date FROM employees ORDER BY hire_date ASC; -- Sort by hire date (oldest first)
🔹 LIMIT – Restrict Number of Results
SELECT * FROM employees LIMIT 5; -- Fetch only 5 rows SELECT * FROM employees WHERE department = 'HR' LIMIT 10; -- Fetch first 10 HR employees
🔹 DISTINCT – Remove Duplicates
SELECT DISTINCT department FROM employees; -- Show unique departments
Mini Task for You: Try to write an SQL query to fetch the top 3 highest-paid employees from an "employees" table.
You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata
Like this post if you want me to continue covering all the topics! 👍❤️
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
#sql
❤1
𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰:
Join for more: https://news.1rj.ru/str/sqlanalyst
1. Danny’s Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subnoscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: That’s money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8
Join for more: https://news.1rj.ru/str/sqlanalyst
1. Danny’s Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subnoscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: That’s money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8
❤4
Data Analyst Interview Questions 👇
1.How to create filters in Power BI?
Filters are an integral part of Power BI reports. They are used to slice and dice the data as per the dimensions we want. Filters are created in a couple of ways.
Using Slicers: A slicer is a visual under Visualization Pane. This can be added to the design view to filter our reports. When a slicer is added to the design view, it requires a field to be added to it. For example- Slicer can be added for Country fields. Then the data can be filtered based on countries.
Using Filter Pane: The Power BI team has added a filter pane to the reports, which is a single space where we can add different fields as filters. And these fields can be added depending on whether you want to filter only one visual(Visual level filter), or all the visuals in the report page(Page level filters), or applicable to all the pages of the report(report level filters)
2.How to sort data in Power BI?
Sorting is available in multiple formats. In the data view, a common sorting option of alphabetical order is there. Apart from that, we have the option of Sort by column, where one can sort a column based on another column. The sorting option is available in visuals as well. Sort by ascending and descending option by the fields and measure present in the visual is also available.
3.How to convert pdf to excel?
Open the PDF document you want to convert in XLSX format in Acrobat DC.
Go to the right pane and click on the “Export PDF” option.
Choose spreadsheet as the Export format.
Select “Microsoft Excel Workbook.”
Now click “Export.”
Download the converted file or share it.
4. How to enable macros in excel?
Click the file tab and then click “Options.”
A dialog box will appear. In the “Excel Options” dialog box, click on the “Trust Center” and then “Trust Center Settings.”
Go to the “Macro Settings” and select “enable all macros.”
Click OK to apply the macro settings.
1.How to create filters in Power BI?
Filters are an integral part of Power BI reports. They are used to slice and dice the data as per the dimensions we want. Filters are created in a couple of ways.
Using Slicers: A slicer is a visual under Visualization Pane. This can be added to the design view to filter our reports. When a slicer is added to the design view, it requires a field to be added to it. For example- Slicer can be added for Country fields. Then the data can be filtered based on countries.
Using Filter Pane: The Power BI team has added a filter pane to the reports, which is a single space where we can add different fields as filters. And these fields can be added depending on whether you want to filter only one visual(Visual level filter), or all the visuals in the report page(Page level filters), or applicable to all the pages of the report(report level filters)
2.How to sort data in Power BI?
Sorting is available in multiple formats. In the data view, a common sorting option of alphabetical order is there. Apart from that, we have the option of Sort by column, where one can sort a column based on another column. The sorting option is available in visuals as well. Sort by ascending and descending option by the fields and measure present in the visual is also available.
3.How to convert pdf to excel?
Open the PDF document you want to convert in XLSX format in Acrobat DC.
Go to the right pane and click on the “Export PDF” option.
Choose spreadsheet as the Export format.
Select “Microsoft Excel Workbook.”
Now click “Export.”
Download the converted file or share it.
4. How to enable macros in excel?
Click the file tab and then click “Options.”
A dialog box will appear. In the “Excel Options” dialog box, click on the “Trust Center” and then “Trust Center Settings.”
Go to the “Macro Settings” and select “enable all macros.”
Click OK to apply the macro settings.
❤1
Important Excel, Tableau, Statistics, SQL related Questions with answers
1. What are the common problems that data analysts encounter during analysis?
The common problems steps involved in any analytics project are:
Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues
2. Explain the Type I and Type II errors in Statistics?
In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.
A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.
3. How do you make a dropdown list in MS Excel?
First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.
4. How do you subset or filter data in SQL?
To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.
5. What is a Gantt Chart in Tableau?
A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project
1. What are the common problems that data analysts encounter during analysis?
The common problems steps involved in any analytics project are:
Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues
2. Explain the Type I and Type II errors in Statistics?
In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.
A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.
3. How do you make a dropdown list in MS Excel?
First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.
4. How do you subset or filter data in SQL?
To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.
5. What is a Gantt Chart in Tableau?
A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project
❤1
Data Analyst Interview Questions
1. What do Tableau's sets and groups mean?
Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.
2.What in Excel is a macro?
An Excel macro is an algorithm or a group of steps that helps automate an operation by capturing and replaying the steps needed to finish it. Once the steps have been saved, you may construct a Macro that the user can alter and replay as often as they like.
Macro is excellent for routine work because it also gets rid of mistakes. Consider the scenario when an account manager needs to share reports about staff members who owe the company money. If so, it can be automated by utilising a macro and making small adjustments each month as necessary.
3.Gantt chart in Tableau
A Tableau Gantt chart illustrates the duration of events as well as the progression of value across the period. Along with the time axis, it has bars. The Gantt chart is primarily used as a project management tool, with each bar representing a project job.
4.In Microsoft Excel, how do you create a drop-down list?
Start by selecting the Data tab from the ribbon.
Select Data Validation from the Data Tools group.
Go to Settings > Allow > List next.
Choose the source you want to offer in the form of a list array.
1. What do Tableau's sets and groups mean?
Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.
2.What in Excel is a macro?
An Excel macro is an algorithm or a group of steps that helps automate an operation by capturing and replaying the steps needed to finish it. Once the steps have been saved, you may construct a Macro that the user can alter and replay as often as they like.
Macro is excellent for routine work because it also gets rid of mistakes. Consider the scenario when an account manager needs to share reports about staff members who owe the company money. If so, it can be automated by utilising a macro and making small adjustments each month as necessary.
3.Gantt chart in Tableau
A Tableau Gantt chart illustrates the duration of events as well as the progression of value across the period. Along with the time axis, it has bars. The Gantt chart is primarily used as a project management tool, with each bar representing a project job.
4.In Microsoft Excel, how do you create a drop-down list?
Start by selecting the Data tab from the ribbon.
Select Data Validation from the Data Tools group.
Go to Settings > Allow > List next.
Choose the source you want to offer in the form of a list array.
❤1
1. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?
Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.
4. How can you split a column into 2 or more columns?
You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.
5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?
https://news.1rj.ru/str/sqlspecialist/94
Here is a complete roadmap from scratch that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?
Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.
4. How can you split a column into 2 or more columns?
You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.
5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?
https://news.1rj.ru/str/sqlspecialist/94
Here is a complete roadmap from scratch that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.
❤1
Must Study: These are the important Questions for Data Analyst ✅
SQL
1. How do you handle NULL values in SQL queries, and why is it important?
2. What is the difference between INNER JOIN and OUTER JOIN, and when would you use each?
3. How do you implement transaction control in SQL Server?
Excel
1. How do you use pivot tables to analyze large datasets in Excel?
2. What are Excel's built-in functions for statistical analysis, and how do you use them?
3. How do you create interactive dashboards in Excel?
Power BI
1. How do you optimize Power BI reports for performance?
2. What is the role of DAX (Data Analysis Expressions) in Power BI, and how do you use it?
3. How do you handle real-time data streaming in Power BI?
Python
1. How do you use Pandas for data manipulation, and what are some advanced features?
2. How do you implement machine learning models in Python, from data preparation to deployment?
3. What are the best practices for handling large datasets in Python?
Data Visualization
1. How do you choose the right visualization technique for different types of data?
2. What is the importance of color theory in data visualization?
3. How do you use tools like Tableau or Power BI for advanced data storytelling?
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
SQL
1. How do you handle NULL values in SQL queries, and why is it important?
2. What is the difference between INNER JOIN and OUTER JOIN, and when would you use each?
3. How do you implement transaction control in SQL Server?
Excel
1. How do you use pivot tables to analyze large datasets in Excel?
2. What are Excel's built-in functions for statistical analysis, and how do you use them?
3. How do you create interactive dashboards in Excel?
Power BI
1. How do you optimize Power BI reports for performance?
2. What is the role of DAX (Data Analysis Expressions) in Power BI, and how do you use it?
3. How do you handle real-time data streaming in Power BI?
Python
1. How do you use Pandas for data manipulation, and what are some advanced features?
2. How do you implement machine learning models in Python, from data preparation to deployment?
3. What are the best practices for handling large datasets in Python?
Data Visualization
1. How do you choose the right visualization technique for different types of data?
2. What is the importance of color theory in data visualization?
3. How do you use tools like Tableau or Power BI for advanced data storytelling?
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
❤1
Excel Cheat Sheet 📔
This Excel cheatsheet is designed to be your quick reference guide for using Microsoft Excel efficiently.
1. Basic Functions
- SUM:
- AVERAGE:
- COUNT:
- MAX:
- MIN:
2. Text Functions
- CONCATENATE:
- LEFT:
- RIGHT:
- MID:
- TRIM:
3. Logical Functions
- IF:
- AND:
- OR:
- NOT:
4. Lookup Functions
- VLOOKUP:
- HLOOKUP:
- INDEX:
- MATCH:
5. Data Sorting & Filtering
- Sort: *Data > Sort*
- Filter: *Data > Filter*
- Advanced Filter: *Data > Advanced*
6. Conditional Formatting
- Apply Formatting: *Home > Conditional Formatting > New Rule*
- Highlight Cells: *Home > Conditional Formatting > Highlight Cells Rules*
7. Charts and Graphs
- Insert Chart: *Insert > Select Chart Type*
- Customize Chart: *Chart Tools > Design/Format*
8. PivotTables
- Create PivotTable: *Insert > PivotTable*
- Refresh PivotTable: *Right-click on PivotTable > Refresh*
9. Data Validation
- Set Validation: *Data > Data Validation*
- List: *Allow: List > Source: range or items*
10. Protecting Data
- Protect Sheet: *Review > Protect Sheet*
- Protect Workbook: *Review > Protect Workbook*
11. Shortcuts
- Copy:
- Paste:
- Undo:
- Redo:
- Save:
12. Printing Options
- Print Area: *Page Layout > Print Area > Set Print Area*
- Page Setup: *Page Layout > Page Setup*
Checklist for Data Analyst: https://dataanalytics.beehiiv.com/p/data
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier
Like for more Interview Resources ♥️
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
This Excel cheatsheet is designed to be your quick reference guide for using Microsoft Excel efficiently.
1. Basic Functions
- SUM:
=SUM(range)- AVERAGE:
=AVERAGE(range)- COUNT:
=COUNT(range)- MAX:
=MAX(range)- MIN:
=MIN(range)2. Text Functions
- CONCATENATE:
=CONCATENATE(text1, text2, ...) or =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)- LEFT:
=LEFT(text, num_chars)- RIGHT:
=RIGHT(text, num_chars)- MID:
=MID(text, start_num, num_chars)- TRIM:
=TRIM(text)3. Logical Functions
- IF:
=IF(condition, true_value, false_value)- AND:
=AND(condition1, condition2, ...)- OR:
=OR(condition1, condition2, ...)- NOT:
=NOT(condition)4. Lookup Functions
- VLOOKUP:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- HLOOKUP:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])- INDEX:
=INDEX(array, row_num, [column_num])- MATCH:
=MATCH(lookup_value, lookup_array, [match_type])5. Data Sorting & Filtering
- Sort: *Data > Sort*
- Filter: *Data > Filter*
- Advanced Filter: *Data > Advanced*
6. Conditional Formatting
- Apply Formatting: *Home > Conditional Formatting > New Rule*
- Highlight Cells: *Home > Conditional Formatting > Highlight Cells Rules*
7. Charts and Graphs
- Insert Chart: *Insert > Select Chart Type*
- Customize Chart: *Chart Tools > Design/Format*
8. PivotTables
- Create PivotTable: *Insert > PivotTable*
- Refresh PivotTable: *Right-click on PivotTable > Refresh*
9. Data Validation
- Set Validation: *Data > Data Validation*
- List: *Allow: List > Source: range or items*
10. Protecting Data
- Protect Sheet: *Review > Protect Sheet*
- Protect Workbook: *Review > Protect Workbook*
11. Shortcuts
- Copy:
Ctrl + C- Paste:
Ctrl + V- Undo:
Ctrl + Z- Redo:
Ctrl + Y- Save:
Ctrl + S12. Printing Options
- Print Area: *Page Layout > Print Area > Set Print Area*
- Page Setup: *Page Layout > Page Setup*
Checklist for Data Analyst: https://dataanalytics.beehiiv.com/p/data
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier
Like for more Interview Resources ♥️
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
❤4
What seperates a good 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘀𝘁 from a great one?
The journey to becoming an exceptional data analyst requires mastering a blend of technical and soft skills.
☑ Technical skills:
- Querying Data with SQL
- Data Visualization (Tableau/PowerBI)
- Data Storytelling and Reporting
- Data Exploration and Analytics
- Data Modeling
☑ Soft Skills:
- Problem Solving
- Communication
- Business Acumen
- Curiosity
- Critical Thinking
- Learning Mindset
But how do you develop these soft skills?
◆ Tackle real-world data projects or case studies. The more complex, the better.
◆ Practice explaining your analysis to non-technical audiences. If they understand, you’ve nailed it!
◆ Learn how industries use data for decision-making. Align your analysis with business outcomes.
◆ Stay curious, ask 'why,' and dig deeper into your data. Don’t settle for surface-level insights.
◆ Keep evolving. Attend webinars, read books, or engage with industry experts regularly.
The journey to becoming an exceptional data analyst requires mastering a blend of technical and soft skills.
☑ Technical skills:
- Querying Data with SQL
- Data Visualization (Tableau/PowerBI)
- Data Storytelling and Reporting
- Data Exploration and Analytics
- Data Modeling
☑ Soft Skills:
- Problem Solving
- Communication
- Business Acumen
- Curiosity
- Critical Thinking
- Learning Mindset
But how do you develop these soft skills?
◆ Tackle real-world data projects or case studies. The more complex, the better.
◆ Practice explaining your analysis to non-technical audiences. If they understand, you’ve nailed it!
◆ Learn how industries use data for decision-making. Align your analysis with business outcomes.
◆ Stay curious, ask 'why,' and dig deeper into your data. Don’t settle for surface-level insights.
◆ Keep evolving. Attend webinars, read books, or engage with industry experts regularly.
❤2