Data Analyst Interview Resources
Most asked SQL interview questions for Data Analyst/Data Engineer role- 1 - What is SQL and what are its main features? 2 - Order of writing SQL query? 3- Order of execution of SQL query? 4- What are some of the most common SQL commands? 5- Whatβs aβ¦
Answers to above SQL interview questions:
1. SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Its main features include querying and managing data, defining and modifying database structures, and controlling access to data.
2. The order of writing an SQL query typically starts with the SELECT clause to specify columns, followed by the FROM clause to specify tables, then optional clauses like WHERE (for filtering), GROUP BY (for grouping), HAVING (for filtering after grouping), ORDER BY (for sorting), and finally, LIMIT/OFFSET (for pagination).
3. The order of execution of an SQL query is generally: FROM (specify data sources), WHERE (apply conditions), GROUP BY (perform grouping), HAVING (filter grouped data), SELECT (retrieve columns), DISTINCT (remove duplicates), ORDER BY (sort results), and finally, LIMIT/OFFSET (apply result limits).
4. Common SQL commands include SELECT (retrieve data), INSERT (add new records), UPDATE (modify existing records), DELETE (remove records), CREATE TABLE (create a new table), ALTER TABLE (modify existing table structure), and DROP TABLE (delete a table).
5. A primary key uniquely identifies each record in a table and ensures no duplicate values.
A foreign key establishes a link between two tables, referencing the primary key of another table to maintain referential integrity.
6. SQL joins include INNER JOIN (returns rows where there is a match in both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right table), RIGHT JOIN (returns all rows from the right table and matching rows from the left table), and FULL JOIN (returns all rows when there is a match in either table).
7. Window functions (like ROW_NUMBER, RANK, DENSE_RANK, etc.) operate over a window of rows and can perform calculations across rows related to the current row. Differences lie in how they assign ranks or sequence numbers based on specified criteria within the window.
8. A stored procedure is a precompiled collection of SQL statements and procedural logic stored in the database and executed as a unit. It can accept input parameters, perform operations, and return results.
9. The main difference between stored procedures and functions in SQL is that stored procedures can perform DML (Data Manipulation Language) operations, such as INSERT, UPDATE, and DELETE, whereas functions are primarily used to compute values and cannot change data.
10. A trigger in SQL is a special type of stored procedure that automatically executes when a specific event (like INSERT, UPDATE, or DELETE) occurs on a table. Triggers are used to enforce business rules, maintain data integrity, or automate tasks.
11. The WHERE clause is used to filter rows before any groupings are made
(typically used in SELECT, UPDATE, or DELETE statements).
The HAVING clause is used to filter rows after the grouping has been done, based on aggregate values
(typically used in SELECT statements with GROUP BY).
Like β€οΈ this post if you need more data analytics interview Questions with Answers
1. SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Its main features include querying and managing data, defining and modifying database structures, and controlling access to data.
2. The order of writing an SQL query typically starts with the SELECT clause to specify columns, followed by the FROM clause to specify tables, then optional clauses like WHERE (for filtering), GROUP BY (for grouping), HAVING (for filtering after grouping), ORDER BY (for sorting), and finally, LIMIT/OFFSET (for pagination).
3. The order of execution of an SQL query is generally: FROM (specify data sources), WHERE (apply conditions), GROUP BY (perform grouping), HAVING (filter grouped data), SELECT (retrieve columns), DISTINCT (remove duplicates), ORDER BY (sort results), and finally, LIMIT/OFFSET (apply result limits).
4. Common SQL commands include SELECT (retrieve data), INSERT (add new records), UPDATE (modify existing records), DELETE (remove records), CREATE TABLE (create a new table), ALTER TABLE (modify existing table structure), and DROP TABLE (delete a table).
5. A primary key uniquely identifies each record in a table and ensures no duplicate values.
A foreign key establishes a link between two tables, referencing the primary key of another table to maintain referential integrity.
6. SQL joins include INNER JOIN (returns rows where there is a match in both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right table), RIGHT JOIN (returns all rows from the right table and matching rows from the left table), and FULL JOIN (returns all rows when there is a match in either table).
7. Window functions (like ROW_NUMBER, RANK, DENSE_RANK, etc.) operate over a window of rows and can perform calculations across rows related to the current row. Differences lie in how they assign ranks or sequence numbers based on specified criteria within the window.
8. A stored procedure is a precompiled collection of SQL statements and procedural logic stored in the database and executed as a unit. It can accept input parameters, perform operations, and return results.
9. The main difference between stored procedures and functions in SQL is that stored procedures can perform DML (Data Manipulation Language) operations, such as INSERT, UPDATE, and DELETE, whereas functions are primarily used to compute values and cannot change data.
10. A trigger in SQL is a special type of stored procedure that automatically executes when a specific event (like INSERT, UPDATE, or DELETE) occurs on a table. Triggers are used to enforce business rules, maintain data integrity, or automate tasks.
11. The WHERE clause is used to filter rows before any groupings are made
(typically used in SELECT, UPDATE, or DELETE statements).
The HAVING clause is used to filter rows after the grouping has been done, based on aggregate values
(typically used in SELECT statements with GROUP BY).
Like β€οΈ this post if you need more data analytics interview Questions with Answers
π18β€16π€1
Here's a list of commonly asked data analyst interview questions:
1. Tell me about yourself : This is often the opener, allowing you to summarize your background, skills, and experiences.
2. What is the difference between data analytics and data science?: Be ready to explain these terms and how they differ.
3. Describe a typical data analysis process you follow: Walk through steps like data collection, cleaning, analysis, and interpretation.
4. What programming languages are you proficient in?: Typically SQL, Python, R are common; mention any others you're familiar with.
5. How do you handle missing or incomplete data?: Discuss methods like imputation or excluding records based on criteria.
6. Explain a time when you used data to solve a problem: Provide a detailed example showcasing your analytical skills.
7. What data visualization tools have you used?: Tableau, Power BI, or others; discuss your experience.
8. How do you ensure the quality and accuracy of your analytical work?: Mention techniques like validation, peer reviews, or data audits.
9. What is your approach to presenting complex data findings to non-technical stakeholders?: Highlight your communication skills and ability to simplify complex information.
10. Describe a challenging data project you've worked on: Explain the project, challenges faced, and how you overcame them.
11. How do you stay updated with the latest trends in data analytics?: Talk about blogs, courses, or communities you follow.
12. What statistical techniques are you familiar with?: Regression, clustering, hypothesis testing, etc.; explain when you've used them.
13. How would you assess the effectiveness of a new data model?: Discuss metrics like accuracy, precision, recall, etc.
14. Give an example of a time when you dealt with a large dataset: Explain how you managed and processed the data efficiently.
15. Why do you want to work for this company?: Tailor your response to highlight why their industry or culture appeals to you
1. Tell me about yourself : This is often the opener, allowing you to summarize your background, skills, and experiences.
2. What is the difference between data analytics and data science?: Be ready to explain these terms and how they differ.
3. Describe a typical data analysis process you follow: Walk through steps like data collection, cleaning, analysis, and interpretation.
4. What programming languages are you proficient in?: Typically SQL, Python, R are common; mention any others you're familiar with.
5. How do you handle missing or incomplete data?: Discuss methods like imputation or excluding records based on criteria.
6. Explain a time when you used data to solve a problem: Provide a detailed example showcasing your analytical skills.
7. What data visualization tools have you used?: Tableau, Power BI, or others; discuss your experience.
8. How do you ensure the quality and accuracy of your analytical work?: Mention techniques like validation, peer reviews, or data audits.
9. What is your approach to presenting complex data findings to non-technical stakeholders?: Highlight your communication skills and ability to simplify complex information.
10. Describe a challenging data project you've worked on: Explain the project, challenges faced, and how you overcame them.
11. How do you stay updated with the latest trends in data analytics?: Talk about blogs, courses, or communities you follow.
12. What statistical techniques are you familiar with?: Regression, clustering, hypothesis testing, etc.; explain when you've used them.
13. How would you assess the effectiveness of a new data model?: Discuss metrics like accuracy, precision, recall, etc.
14. Give an example of a time when you dealt with a large dataset: Explain how you managed and processed the data efficiently.
15. Why do you want to work for this company?: Tailor your response to highlight why their industry or culture appeals to you
π23β€10π€1π1π1
1. What do you understand about the E-R model?
Answer: E-R model is an Entity-Relationship model which defines the conceptual view of the database.
The E-R model basically shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.
2. Explain the terms βAttributeβ and βRelationsβ
Answer:
Attribute is described as the properties or characteristics of an entity. For Example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.
Relation is a two-dimensional table containing a number of rows and columns where every row represents a record of the relation. Here, rows are also known as βTuplesβ and columns are known as βAttributesβ.
3. What is the Database transaction?
Answer: Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.
4. What do you understand about βAtomicityβ and βAggregationβ?
Answer: Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects done by the incomplete transaction.
Aggregation is the concept of expressing the relationship with the collection of entities and their relationships.
Answer: E-R model is an Entity-Relationship model which defines the conceptual view of the database.
The E-R model basically shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.
2. Explain the terms βAttributeβ and βRelationsβ
Answer:
Attribute is described as the properties or characteristics of an entity. For Example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.
Relation is a two-dimensional table containing a number of rows and columns where every row represents a record of the relation. Here, rows are also known as βTuplesβ and columns are known as βAttributesβ.
3. What is the Database transaction?
Answer: Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.
4. What do you understand about βAtomicityβ and βAggregationβ?
Answer: Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects done by the incomplete transaction.
Aggregation is the concept of expressing the relationship with the collection of entities and their relationships.
π18β€5π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.
π33β€8π₯°1π1
1. Define the term 'Data Wrangling.
Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format.
2. What are the best methods for data cleaning?
Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry.
3. Explain 4 steps to use CTE in sql.
All CTE starts with "with" clause.
After with you need to define CTE name and the field names. For instance in the below code snippet I have 3 fields Count,Column and Id. The name of CTE is "MyTemp".
Once you have defined CTE we need to specify the SQL which will give the result for the CTE.
Finally you can use the CTE in your SQL query.
Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format.
2. What are the best methods for data cleaning?
Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry.
3. Explain 4 steps to use CTE in sql.
All CTE starts with "with" clause.
After with you need to define CTE name and the field names. For instance in the below code snippet I have 3 fields Count,Column and Id. The name of CTE is "MyTemp".
Once you have defined CTE we need to specify the SQL which will give the result for the CTE.
Finally you can use the CTE in your SQL query.
π25β€5π€1π1
π°ππππππππ πππππππππ πππ πππππππ πππ
πππ
-πππππ π©πππππππ π¨ππππππ πππππππππ ππ ππππππππ.
ππ
https://t.iss.one/analystcommunity/13
ππ
https://t.iss.one/analystcommunity/13
π8β€1
5β£ Important data analysis interview questions
Explain the Data Analysis Process:
Walk me through the typical steps you follow when conducting a data analysis project.
What is the Difference Between Descriptive and Inferential Statistics?:
Can you explain the distinction between descriptive statistics and inferential statistics and provide examples of when each is used?
How Do You Handle Missing Data in a Dataset?:
What strategies and techniques do you use to deal with missing or incomplete data in a dataset?
What is Exploratory Data Analysis (EDA)?:
Describe what EDA is and the various methods and visualizations you employ during this phase of data analysis.
Give an Example of a Time When You Used Data Analysis to Solve a Real-World Problem:
Share a specific project or scenario where you applied data analysis techniques to address a practical problem. What was the outcome, and what tools or methodologies did you use?
Like this post if you also need the answers for the above questions β€οΈπ
Explain the Data Analysis Process:
Walk me through the typical steps you follow when conducting a data analysis project.
What is the Difference Between Descriptive and Inferential Statistics?:
Can you explain the distinction between descriptive statistics and inferential statistics and provide examples of when each is used?
How Do You Handle Missing Data in a Dataset?:
What strategies and techniques do you use to deal with missing or incomplete data in a dataset?
What is Exploratory Data Analysis (EDA)?:
Describe what EDA is and the various methods and visualizations you employ during this phase of data analysis.
Give an Example of a Time When You Used Data Analysis to Solve a Real-World Problem:
Share a specific project or scenario where you applied data analysis techniques to address a practical problem. What was the outcome, and what tools or methodologies did you use?
Like this post if you also need the answers for the above questions β€οΈπ
π40β€9π1
Glad to see the amazing response from you guys π
Here are the answers to these questions
Explain the Data Analysis Process:
The data analysis process typically involves several key steps. These steps include:
Data Collection: Gathering the relevant data from various sources.
Data Cleaning: Removing inconsistencies, handling missing values, and ensuring data quality.
Data Exploration: Using descriptive statistics, visualizations, and initial insights to understand the data.
Data Transformation: Preprocessing, feature engineering, and data formatting.
Data Modeling: Applying statistical or machine learning models to extract patterns or make predictions.
Evaluation: Assessing the model's performance and validity.
Interpretation: Drawing meaningful conclusions from the analysis.
Communication: Presenting findings to stakeholders effectively.
What is the Difference Between Descriptive and Inferential Statistics?:
Descriptive statistics summarize and describe data, providing insights into its main characteristics. Examples include measures like mean, median, and standard deviation.
Inferential statistics, on the other hand, involve making predictions or drawing conclusions about a population based on a sample of data. Hypothesis testing and confidence intervals are common inferential statistical techniques.
How Do You Handle Missing Data in a Dataset?:
Handling missing data is crucial for accurate analysis:
I start by identifying the extent of missing data.
For numerical data, I might impute missing values with the mean, median, or a predictive model.
For categorical data, I often use mode imputation.
If appropriate, I consider removing rows with too much missing data.
I also explore if the missingness pattern itself holds valuable information.
What is Exploratory Data Analysis (EDA)?:
EDA is the process of visually and statistically exploring a dataset to understand its characteristics:
I begin with summary statistics, histograms, and box plots to identify data trends.
I create scatterplots and correlation matrices to understand relationships.
Outlier detection and data distribution analysis are also part of EDA.
The goal is to gain insights, identify patterns, and inform subsequent analysis steps.
Give an Example of a Time When You Used Data Analysis to Solve a Real-World Problem:
In a previous role, I worked for an e-commerce company, and we wanted to reduce shopping cart abandonment rates. I conducted a data analysis project:
Collected user data, including browsing behavior, demographics, and purchase history.
Cleaned and preprocessed the data.
Explored the data through visualizations and statistical tests.
Built a predictive model to identify factors contributing to cart abandonment.
Found that longer page load times were a significant factor.
Proposed optimizations to reduce load times, resulting in a 15% decrease in cart abandonment rates over a quarter.
Hope it helps :)
Here are the answers to these questions
Explain the Data Analysis Process:
The data analysis process typically involves several key steps. These steps include:
Data Collection: Gathering the relevant data from various sources.
Data Cleaning: Removing inconsistencies, handling missing values, and ensuring data quality.
Data Exploration: Using descriptive statistics, visualizations, and initial insights to understand the data.
Data Transformation: Preprocessing, feature engineering, and data formatting.
Data Modeling: Applying statistical or machine learning models to extract patterns or make predictions.
Evaluation: Assessing the model's performance and validity.
Interpretation: Drawing meaningful conclusions from the analysis.
Communication: Presenting findings to stakeholders effectively.
What is the Difference Between Descriptive and Inferential Statistics?:
Descriptive statistics summarize and describe data, providing insights into its main characteristics. Examples include measures like mean, median, and standard deviation.
Inferential statistics, on the other hand, involve making predictions or drawing conclusions about a population based on a sample of data. Hypothesis testing and confidence intervals are common inferential statistical techniques.
How Do You Handle Missing Data in a Dataset?:
Handling missing data is crucial for accurate analysis:
I start by identifying the extent of missing data.
For numerical data, I might impute missing values with the mean, median, or a predictive model.
For categorical data, I often use mode imputation.
If appropriate, I consider removing rows with too much missing data.
I also explore if the missingness pattern itself holds valuable information.
What is Exploratory Data Analysis (EDA)?:
EDA is the process of visually and statistically exploring a dataset to understand its characteristics:
I begin with summary statistics, histograms, and box plots to identify data trends.
I create scatterplots and correlation matrices to understand relationships.
Outlier detection and data distribution analysis are also part of EDA.
The goal is to gain insights, identify patterns, and inform subsequent analysis steps.
Give an Example of a Time When You Used Data Analysis to Solve a Real-World Problem:
In a previous role, I worked for an e-commerce company, and we wanted to reduce shopping cart abandonment rates. I conducted a data analysis project:
Collected user data, including browsing behavior, demographics, and purchase history.
Cleaned and preprocessed the data.
Explored the data through visualizations and statistical tests.
Built a predictive model to identify factors contributing to cart abandonment.
Found that longer page load times were a significant factor.
Proposed optimizations to reduce load times, resulting in a 15% decrease in cart abandonment rates over a quarter.
Hope it helps :)
π40β€8π€1
How to solve Guesstimate ππ
https://t.iss.one/caseinterviewscracked/17
https://t.iss.one/caseinterviewscracked/17
If you're looking to build a career in Data Analytics but feel unsure about where to start, this post is for you.
It's important to know that you don't need to spend money on expensive courses to succeed in this field.
Many posts you see on LinkedIn promoting paid courses are often shared by individuals who are either trying to sell their own products or are being compensated to endorse these courses.
Through this post, I will share with you everything you need to start your data journey absolutely free.
π Source
Hope it helps :)
It's important to know that you don't need to spend money on expensive courses to succeed in this field.
Many posts you see on LinkedIn promoting paid courses are often shared by individuals who are either trying to sell their own products or are being compensated to endorse these courses.
Through this post, I will share with you everything you need to start your data journey absolutely free.
π Source
Hope it helps :)
π14β€6π1π1
1. Define the term 'Data Wrangling.
Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format.
2. What are the best methods for data cleaning?
Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry.
3. 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.
4. 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.
5. State some ways to improve the performance of Tableau?
Use an Extract to make workbooks run faster.
Reduce the scope of data to decrease the volume of data.
Reduce the number of marks on the view to avoid information overload.
Hide unused fields.
Use Context filters.
Use indexing in tables and use the same fields for filtering.
Remove unnecessary calculations and sheets.
Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format.
2. What are the best methods for data cleaning?
Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry.
3. 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.
4. 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.
5. State some ways to improve the performance of Tableau?
Use an Extract to make workbooks run faster.
Reduce the scope of data to decrease the volume of data.
Reduce the number of marks on the view to avoid information overload.
Hide unused fields.
Use Context filters.
Use indexing in tables and use the same fields for filtering.
Remove unnecessary calculations and sheets.
π10β€7π€2π1
1. Give an overview of the fact and dimensions of the table?
Facts are numeric measures of data. They are stored in fact tables. Fact tables store that type of data that will be analyzed by dimension tables. Fact tables have foreign keys associating with dimension tables.
Dimensions are descriptive attributes of data. Those will be stored in the dimensions table. For example, customerβs information like name, number, and email will be stored in the dimension table.
2. Explain the limitation of context filters in Tableau?
Whenever we set a context filter, Tableau generates a temp table that needs to refresh each and every time, whenever the view is triggered. So, if the context filter is changed in the database, it needs to recompute the temp table, so the performance will be decreased.
3. What is the difference between published data and embedded data sources?
The published data source contains connection information that is independent of workbooks and can be used by multiple workbooks.
The embedded data source contains connection information but it is associated with the workbooks.
4. Explain the disaggregation and aggregation of data in Tableau?
Aggregation β The process of summarizing the data and viewing a single numeric value is called aggregation. Example β sum/avg of salary for each employee
Disaggregation βThe process of viewing each transaction for analyzing all the measures both dependently and independently. Example β individual salary transactions for each employee.
Facts are numeric measures of data. They are stored in fact tables. Fact tables store that type of data that will be analyzed by dimension tables. Fact tables have foreign keys associating with dimension tables.
Dimensions are descriptive attributes of data. Those will be stored in the dimensions table. For example, customerβs information like name, number, and email will be stored in the dimension table.
2. Explain the limitation of context filters in Tableau?
Whenever we set a context filter, Tableau generates a temp table that needs to refresh each and every time, whenever the view is triggered. So, if the context filter is changed in the database, it needs to recompute the temp table, so the performance will be decreased.
3. What is the difference between published data and embedded data sources?
The published data source contains connection information that is independent of workbooks and can be used by multiple workbooks.
The embedded data source contains connection information but it is associated with the workbooks.
4. Explain the disaggregation and aggregation of data in Tableau?
Aggregation β The process of summarizing the data and viewing a single numeric value is called aggregation. Example β sum/avg of salary for each employee
Disaggregation βThe process of viewing each transaction for analyzing all the measures both dependently and independently. Example β individual salary transactions for each employee.
π17β€3
SQL Zero to Hero
ππ
https://www.linkedin.com/posts/sql-analysts_many-people-pay-too-much-to-learn-sql-but-activity-7201219129310138368-2U6a
Like for more
ππ
https://www.linkedin.com/posts/sql-analysts_many-people-pay-too-much-to-learn-sql-but-activity-7201219129310138368-2U6a
Like for more
π13
Good way to Prepare for a Data Analyst Interview?
A mock interview is a practice interview that closely mimics a real one. In a mock interview, an experienced data analyst tests a less experienced person's knowledge and skills.
π Practice with Google for free
Google offers Interview Warmup. You can practice answering questions and get quick feedback on your answers. This helps you get better and feel more confident for real interviews.
Like for more π
A mock interview is a practice interview that closely mimics a real one. In a mock interview, an experienced data analyst tests a less experienced person's knowledge and skills.
π Practice with Google for free
Google offers Interview Warmup. You can practice answering questions and get quick feedback on your answers. This helps you get better and feel more confident for real interviews.
Like for more π
π30β€5π₯°3
Python Most Important Interview Questions
Question 1: Calculate the average stock price for Company X over the last 6 months.
Question 2: Identify the month with the highest total sales for Company Y using their monthly sales data.
Question 3: Find the maximum and minimum stock price for Company Z on any given day in the last year.
Question 4: Create a column in the DataFrame showing the percentage change in stock price from the previous day for Company X.
Question 5: Determine the number of days when the stock price of Company Y was above its 30-day moving average. Question
6: Compare the average stock price of Companies X and Z in the first quarter of the year.
#Data#
----------------------------------------------
import pandas as pd
data = { 'Date': pd.date_range(start='2023-01-01', periods=180, freq='D'), 'CompanyX_StockPrice': pd.np.random.randint(50, 150, 180), 'CompanyY_Sales': pd.np.random.randint(20000, 50000, 180), 'CompanyZ_StockPrice': pd.np.random.randint(70, 200, 180) }
df = pd.DataFrame(data)
Question 1: Calculate the average stock price for Company X over the last 6 months.
Question 2: Identify the month with the highest total sales for Company Y using their monthly sales data.
Question 3: Find the maximum and minimum stock price for Company Z on any given day in the last year.
Question 4: Create a column in the DataFrame showing the percentage change in stock price from the previous day for Company X.
Question 5: Determine the number of days when the stock price of Company Y was above its 30-day moving average. Question
6: Compare the average stock price of Companies X and Z in the first quarter of the year.
#Data#
----------------------------------------------
import pandas as pd
data = { 'Date': pd.date_range(start='2023-01-01', periods=180, freq='D'), 'CompanyX_StockPrice': pd.np.random.randint(50, 150, 180), 'CompanyY_Sales': pd.np.random.randint(20000, 50000, 180), 'CompanyZ_StockPrice': pd.np.random.randint(70, 200, 180) }
df = pd.DataFrame(data)
π16π1π1
Excel Interview Questions
Topics to Cover:
β’ Data manipulation
β’ Formulas and functions
β’ Pivot tables
β’ Data visualization
Questions and Answers:
1 Q: How do you use VLOOKUP in Excel?
A: VLOOKUP (Vertical Lookup) searches for a value in the first column of a range and returns a value in the same row from a specified column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
2 Q: What is a Pivot Table and how is it useful?
A: A Pivot Table is a data summarization tool that is used in Excel. It allows you to automatically sort, count, and total data stored in one table and display the results in a second table showing the summarized data.
3 Q: How can you remove duplicates from a dataset in Excel?
A: You can remove duplicates by selecting the data range, going to the Data tab, and clicking on "Remove Duplicates". Excel will prompt you to select columns where duplicates should be checked.
4 Q: What is the use of the IF function in Excel?
A: The IF function checks a condition and returns one value if true and another value if false.
Syntax: =IF(logical_test, value_if_true, value_if_false).
5 Q: Explain how to create a chart in Excel.
A: To create a chart, select the data range, go to the Insert tab, choose the desired chart type (e.g., bar, line, pie), and customize the chart as needed using the Chart Tools.
Join for more: https://t.iss.one/excel_analyst
Topics to Cover:
β’ Data manipulation
β’ Formulas and functions
β’ Pivot tables
β’ Data visualization
Questions and Answers:
1 Q: How do you use VLOOKUP in Excel?
A: VLOOKUP (Vertical Lookup) searches for a value in the first column of a range and returns a value in the same row from a specified column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
2 Q: What is a Pivot Table and how is it useful?
A: A Pivot Table is a data summarization tool that is used in Excel. It allows you to automatically sort, count, and total data stored in one table and display the results in a second table showing the summarized data.
3 Q: How can you remove duplicates from a dataset in Excel?
A: You can remove duplicates by selecting the data range, going to the Data tab, and clicking on "Remove Duplicates". Excel will prompt you to select columns where duplicates should be checked.
4 Q: What is the use of the IF function in Excel?
A: The IF function checks a condition and returns one value if true and another value if false.
Syntax: =IF(logical_test, value_if_true, value_if_false).
5 Q: Explain how to create a chart in Excel.
A: To create a chart, select the data range, go to the Insert tab, choose the desired chart type (e.g., bar, line, pie), and customize the chart as needed using the Chart Tools.
Join for more: https://t.iss.one/excel_analyst
π21β€6π€2
1. What are Query and Query language?
A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.
Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language.
2. What are Superkey and candidate key?
A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.
A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.
3. What do you mean by buffer pool and mention its benefits?
A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server.
The following are the benefits of a buffer pool:
Increase in I/O performance
Reduction in I/O latency
Increase in transaction throughput
Increase in reading performance
4. What is the difference between Zero and NULL values in SQL?
When a field in a column doesnβt have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can cancel be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.
A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.
Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language.
2. What are Superkey and candidate key?
A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.
A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.
3. What do you mean by buffer pool and mention its benefits?
A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server.
The following are the benefits of a buffer pool:
Increase in I/O performance
Reduction in I/O latency
Increase in transaction throughput
Increase in reading performance
4. What is the difference between Zero and NULL values in SQL?
When a field in a column doesnβt have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can cancel be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.
π13β€3π1
Forwarded from MS Excel for Data Analysis
These 10 tips will make you feel like an expert and increase your productivity 100X:
1. Excel Keyboard Shortcuts:
These save a lot of time.
For example, you can press "Ctrl+C" to copy, "Ctrl+V" to paste, and "Ctrl+Z" to undo.
There are many more, so check out this cheatsheet:
Excel for Data Analysis
1. Excel Keyboard Shortcuts:
These save a lot of time.
For example, you can press "Ctrl+C" to copy, "Ctrl+V" to paste, and "Ctrl+Z" to undo.
There are many more, so check out this cheatsheet:
Excel for Data Analysis
π6
Want to become a data analyst?
Stage 1 β Excel
Stage 2 β SQL + Project
Stage 3 β Python (Pandas, NumPy) + Project
Stage 4 β Data Visualization (Matplotlib, Seaborn) + Project
Stage 5 β Statistics + Project
Stage 6 β Machine Learning (Scikit-learn) + Project
Stage 7 β Big Data Tools (Hadoop, Spark) + Project
π β DataAnalytics
Stage 1 β Excel
Stage 2 β SQL + Project
Stage 3 β Python (Pandas, NumPy) + Project
Stage 4 β Data Visualization (Matplotlib, Seaborn) + Project
Stage 5 β Statistics + Project
Stage 6 β Machine Learning (Scikit-learn) + Project
Stage 7 β Big Data Tools (Hadoop, Spark) + Project
π β DataAnalytics
π36π6β€4π€3π1