2 Flashcards
1
Q
A
2
Q
What is a VLOOKUP function in Excel used for?
A
To search for a value in the first column of a range and return a value in the same row from another column.
3
Q
What is the difference between VLOOKUP and INDEX-MATCH?
A
INDEX-MATCH is more flexible, can search left or right, and is faster on large datasets.
4
Q
What does the IF function do in Excel?
A
It performs a logical test and returns one value if TRUE, another if FALSE.
5
Q
What is conditional formatting?
A
It visually highlights cells based on rules (e.g., color for values above a threshold).
6
Q
What is the purpose of Pivot Tables?
A
To quickly summarize, group, and analyze large datasets.
7
Q
What is the difference between Absolute and Relative cell references?
A
Absolute ($A$1) does not change when copied; Relative (A1) adjusts based on position.
8
Q
What is the CONCATENATE function used for?
A
To join two or more text strings into one.
9
Q
How can you remove duplicate values in Excel?
A
Using the ‘Remove Duplicates’ option under the Data tab.
10
Q
What is the COUNTIF function used for?
A
To count the number of cells that meet a specific condition.
11
Q
How can you protect a worksheet in Excel?
A
By using the ‘Protect Sheet’ option and setting a password.
12
Q
What is Tableau used for?
A
For interactive data visualization and dashboard creation.
13
Q
What is a calculated field in Tableau?
A
A custom field created by applying a formula to existing fields.
14
Q
What is the difference between Dimensions and Measures in Tableau?
A
Dimensions are qualitative (categorical), Measures are quantitative (numeric).
15
Q
What is a Dashboard in Tableau?
A
A collection of visualizations displayed together on one screen.
16
Q
What are Filters in Tableau used for?
A
To limit the data shown in a visualization.
17
Q
What is a Parameter in Tableau?
A
A dynamic value used to control visualizations, calculations, or filters.
18
Q
What is the difference between LIVE and EXTRACT connections in Tableau?
A
LIVE fetches data in real-time; EXTRACT stores a snapshot of data.
19
Q
What is a Dual Axis chart in Tableau?
A
A chart that shows two different measures on the same chart with two y-axes.
20
Q
What is the purpose of a Story in Tableau?
A
To create a sequence of dashboards and worksheets to tell a data story.
21
Q
What is Data Blending in Tableau?
A
Combining data from different data sources based on a common field.
22
Q
What is Power BI?
A
A Microsoft tool for data visualization, business intelligence, and interactive reporting.
23
Q
What language does Power BI use for data manipulation?
A
DAX (Data Analysis Expressions).
24
Q
What is a Power BI Dashboard?
A
A single-page, real-time view of important metrics and visualizations.
25
What is the difference between Report and Dashboard in Power BI?
Reports can have multiple pages; Dashboards are single-page summaries.
26
What are Slicers in Power BI?
Visual filter elements used to filter data in reports interactively.
27
What is a Relationship in Power BI?
A link between two tables based on a common column.
28
What is the Query Editor in Power BI used for?
To clean, transform, and shape data before loading into the model.
29
What is Row-Level Security in Power BI?
Restricting access to specific rows of data based on user roles.
30
What is Power Query?
A data connection and transformation tool in Power BI.
31
What file format does Power BI use to save reports?
.pbix
32
What is supervised learning?
Learning from labeled data to make predictions.
33
What is unsupervised learning?
Learning patterns from unlabeled data (e.g., clustering).
34
What is overfitting in machine learning?
When a model learns noise in the training data and performs poorly on new data.
35
What is the purpose of train-test split?
To evaluate model performance on unseen data.
36
What is regression used for?
To predict continuous numeric values.
37
What is classification used for?
To predict discrete categories.
38
What is a confusion matrix?
A table used to evaluate the performance of a classification model.
39
What is accuracy in classification?
(True Positives + True Negatives) / Total samples.
40
What is cross-validation?
A method to assess how a model performs on different subsets of the data.
41
What is feature scaling?
Standardizing or normalizing features to improve model performance.
42
What is the difference between COUNTIF and SUMIF in Excel?
COUNTIF counts cells meeting a condition; SUMIF sums values meeting a condition.
43
What does the TEXT function do in Excel?
Converts a number to text in a specific format.
44
What is the purpose of the OFFSET function?
Returns a cell reference offset from a starting point.
45
How do you create a dynamic named range in Excel?
By using OFFSET and COUNTA functions.
46
What is Goal Seek in Excel?
A tool to find the input value needed to achieve a specific goal.
47
What is the purpose of Data Validation in Excel?
To restrict the type of data entered in a cell.
48
How can you create a drop-down list in Excel?
Using Data Validation > List.
49
What is a Macro in Excel?
A set of recorded actions to automate tasks.
50
What is the difference between SUMPRODUCT and SUMIF?
SUMPRODUCT multiplies arrays and sums them; SUMIF sums based on a condition.
51
What is the purpose of the RAND() and RANDBETWEEN() functions?
To generate random numbers.
52
What is Level of Detail (LOD) Expression in Tableau?
An expression to control data aggregation levels.
53
What is a Gantt Chart used for in Tableau?
To visualize project timelines and task durations.
54
What is a Heat Map in Tableau?
A graphical representation where values are represented by color.
55
What is a TreeMap in Tableau?
A chart displaying hierarchical data as nested rectangles.
56
What is a Context Filter in Tableau?
A filter that affects other filters’ performance and results.
57
What is a Hierarchy in Tableau?
A set of fields organized from higher-level to lower-level categories.
58
What is the difference between Inner Join and Left Join in Tableau?
Inner Join keeps matching records; Left Join keeps all records from the left table.
59
What is the Show Me panel in Tableau?
A panel suggesting suitable visualizations based on selected data.
60
What are Extract Filters in Tableau?
Filters applied while creating a data extract to limit data volume.
61
What is the difference between Workbook and Worksheet in Tableau?
Workbook contains worksheets, dashboards, and stories; Worksheet is a single view.
62
What is DAX in Power BI?
A formula language for data modeling and calculations.
63
What is the CALCULATE function in DAX?
Modifies the filter context of a calculation.
64
What is the difference between Star Schema and Snowflake Schema?
Star Schema has denormalized tables; Snowflake Schema has normalized tables.
65
What is a Measure in Power BI?
A calculated field used in data analysis.
66
What is a Calculated Column in Power BI?
A new column added using DAX formulas.
67
What is the purpose of Power BI Gateway?
To connect on-premises data to Power BI service.
68
What is a Bookmark in Power BI?
A saved view of a report page for navigation or storytelling.
69
What is Drillthrough in Power BI?
A feature to navigate to a detailed report page based on selected data.
70
What is Q&A in Power BI?
A feature allowing users to query data using natural language.
71
What is the difference between Import and DirectQuery in Power BI?
Import loads data into Power BI; DirectQuery queries data in real-time.
72
What is the difference between precision and recall?
Precision = TP / (TP + FP); Recall = TP / (TP + FN).
73
What is A/B testing?
A statistical experiment comparing two versions to determine which performs better.
74
What is the difference between Parametric and Non-Parametric models?
Parametric assumes data distribution; Non-Parametric makes no assumptions.
75
What is regularization in machine learning?
A technique to prevent overfitting by adding a penalty term to the loss function.
76
What is the purpose of the ROC curve?
To show the tradeoff between True Positive Rate and False Positive Rate.
77
What is feature engineering?
Creating new input features to improve model performance.
78
What is one-hot encoding?
Converting categorical variables into binary columns.
79
What is the difference between Label Encoding and One-Hot Encoding?
Label Encoding assigns numeric labels; One-Hot creates binary columns.
80
What is the difference between Bagging and Boosting?
Bagging reduces variance by parallel models; Boosting reduces bias by sequential models.
81
What is the purpose of Principal Component Analysis (PCA)?
To reduce dimensionality while retaining variance.
82
What is the difference between VLOOKUP and INDEX-MATCH?
VLOOKUP looks up a value vertically; INDEX-MATCH is more flexible and faster.
83
What is the purpose of the INDIRECT function?
Returns a cell reference from a text string.
84
How can you remove duplicates in Excel?
Using Data > Remove Duplicates.
85
What is a Pivot Table in Excel?
A tool to summarize and analyze data interactively.
86
What is the difference between Absolute and Relative cell references?
Absolute ($A$1) doesn’t change when copied; Relative (A1) changes.
87
What is a Dual Axis chart in Tableau?
A chart combining two axes for different measures.
88
What is Data Blending in Tableau?
Combining data from different sources based on a common key.
89
What is a Dashboard Action in Tableau?
An interactive element (filter, highlight, URL) added to dashboards.
90
What is the difference between Extract and Live Connection in Tableau?
Extract stores a static snapshot; Live connects to real-time data.
91
What are Parameters in Tableau?
Dynamic values used to replace a constant in a calculation or filter.
92
What is Row-Level Security (RLS) in Power BI?
Restricts data access for specific users.
93
What is a Relationship in Power BI?
A connection between two tables based on a common column.
94
What is the difference between SUM and SUMX in DAX?
SUM sums a column; SUMX evaluates row by row and sums the result.
95
What is a KPI visual in Power BI?
Shows key metrics against a target value.
96
What is Data Modeling in Power BI?
Structuring data relationships and calculations to support reporting.
97
What is Multicollinearity?
High correlation between independent variables, affecting model performance.
98
What is the difference between Linear Regression and Logistic Regression?
Linear predicts continuous values; Logistic predicts binary classes.
99
What is the Curse of Dimensionality?
As features increase, data becomes sparse and models degrade.
100
What is Cross-Validation used for?
To evaluate model performance and avoid overfitting.
101
What is the difference between Classification and Regression problems?
Classification predicts categories; Regression predicts continuous values.
102
What is an OKR?
Objective and Key Results — a goal-setting framework.
103
What is the difference between KPI and Metric?
KPI is a critical success indicator; Metric is a measurable value.
104
What is Funnel Analysis?
Analyzing the steps users take toward conversion.
105
What is Cohort Analysis?
Analyzing groups of users over time.
106
What is Churn Rate?
The percentage of users/customers who stop using a product.
107
Write SQL to find duplicate rows in a table.
SELECT column1, COUNT(*) FROM table GROUP BY column1 HAVING COUNT(*) > 1;
108
How do you find the 2nd highest salary in SQL?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
109
What is a CTE (Common Table Expression) in SQL?
A temporary result set used within a query.
110
How do you merge two DataFrames in pandas on a key?
pd.merge(df1, df2, on='key')
111
How do you handle missing values in pandas?
Using .fillna() or .dropna().
112
How would you analyze a drop in website traffic?
Check traffic sources, device types, bounce rates, page load times, and recent changes.
113
How would you measure the success of a new product feature?
Define success metrics, compare before and after, run A/B test.
114
What is Statistical Significance?
The likelihood that a result is not due to chance.
115
How would you detect anomalies in sales data?
Time series analysis, Z-score, or IQR method.
116
What is a Null Hypothesis?
A default assumption that there is no relationship between variables.
117
What is the best chart for showing parts of a whole?
Pie chart or stacked bar chart.
118
What is the best chart for showing trend over time?
Line chart.
119
How do you avoid clutter in dashboards?
Use white space, limit colors, remove unnecessary visuals.
120
What is the difference between Story and Dashboard in Tableau?
Story is a sequence of views; Dashboard is a collection of views on one page.
121
What are the best practices for presenting data insights?
Know your audience, keep it simple, focus on key metrics, and tell a clear story.
122
What is a Window Function in SQL?
A function that performs a calculation across a set of rows related to the current row without collapsing rows (e.g., `ROW_NUMBER()`, `RANK()`, `SUM() OVER()`).
123
Write SQL to calculate a running total of sales.
```sql
SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total
FROM sales_table;
```
124
What is the difference between RANK() and DENSE_RANK()?
RANK() skips ranks after duplicates; DENSE_RANK() doesn’t skip.
125
What is the purpose of COALESCE() in SQL?
Returns the first non-null value in a list.
126
What is the difference between INNER JOIN and CROSS JOIN?
INNER JOIN matches rows based on condition; CROSS JOIN returns Cartesian product.
127
What is the difference between .loc[] and .iloc[] in pandas?
.loc[] uses labels; .iloc[] uses integer positions.
128
How do you remove duplicates from a pandas DataFrame?
df.drop_duplicates()
129
What is a lambda function in Python?
An anonymous, one-line function defined using `lambda`.
130
How do you group data and calculate aggregates in pandas?
```python
df.groupby('column').agg({'col2':'sum'})
```
131
How can you filter rows in pandas where column A > 10?
df[df['A'] > 10]
132
What is the difference between Calculated Column and Measure in Power BI?
Calculated Columns are row-wise; Measures are evaluated based on context (aggregation).
133
What is the purpose of ALL() function in DAX?
Removes filters from a table or column.
134
How do you create a Many-to-Many relationship in Power BI?
Using a bridge table.
135
What is the purpose of LOOKUPVALUE() in DAX?
Retrieves a value from a table based on a condition.
136
How do you optimize Power BI report performance?
Limit visuals, reduce cardinality, use star schema, avoid unnecessary columns.
137
What is Level of Detail (LOD) Expression in Tableau?
Allows you to control the granularity of aggregations.
138
Difference between FIXED, INCLUDE, and EXCLUDE in LOD?
- FIXED: Ignores view filters.
- INCLUDE: Adds dimensions to calculation.
- EXCLUDE: Removes dimensions.
139
How do you optimize Tableau dashboards?
Reduce number of quick filters, use extracts, hide unused fields.
140
How do you create a parameterized filter in Tableau?
Create a parameter → Create a calculated field → Use in filter.
141
What is the difference between a Filter and a Parameter in Tableau?
Filter limits data shown; Parameter is a dynamic input not tied to data.
142
What is the difference between SUMIF and SUMIFS?
SUMIF is for a single condition; SUMIFS is for multiple conditions.
143
What does the XLOOKUP function do in Excel?
It replaces VLOOKUP & HLOOKUP — looks up data in a range.
144
What is Goal Seek in Excel?
Finds the input value needed to achieve a desired output.
145
What is a Dynamic Named Range?
A range that automatically adjusts as data is added/removed.
146
What is the purpose of the TEXT function in Excel?
Formats numbers as text in a specific format.
147
How would you estimate the number of Uber rides per day in New York?
Break down population, penetration rate, frequency, and multiply.
148
What is a North Star Metric?
The key metric that captures the core value your product delivers.
149
How would you detect if a drop in sales is seasonal or problematic?
Compare YoY data, trend analysis, seasonality decomposition.
150
What is Attribution Modeling?
Assigning credit to different touchpoints in a customer journey.
151
How would you measure user engagement on an app?
Daily Active Users (DAU), Session Duration, Retention Rate.
152
What is A/B/n Testing?
Comparing more than two variants to test performance.
153
What is a P-Value?
The probability that the observed result occurred by chance.
154
What is Heteroskedasticity?
Non-constant variance of errors in regression.
155
What is R-Squared?
Proportion of variance explained by the model.
156
What is Time Series Decomposition?
Breaking time series into trend, seasonality, and residuals.
157
What is the difference between Correlation and Causation?
Correlation is a relationship; causation implies one variable affects another.
158
What are Leading and Lagging Indicators?
Leading predicts future events; Lagging reflects past performance.
159
What is the difference between Aggregation and Granularity?
Aggregation summarizes data; Granularity is the level of detail.
160
What is a Waterfall Chart used for?
Shows how sequential positive/negative values affect a total.
161
What is Exploratory Data Analysis (EDA)?
Process of summarizing datasets' main characteristics using visual and quantitative methods.
162
What is the difference between VLOOKUP and INDEX-MATCH?
VLOOKUP searches vertically and is less flexible. INDEX-MATCH is faster, more versatile, and works both horizontally and vertically.
163
What is an array formula in Excel?
A formula that can perform multiple calculations on one or more items in an array; entered using Ctrl+Shift+Enter.
164
How do you remove duplicates in Excel?
Use `Data` → `Remove Duplicates` or use `UNIQUE()` formula in modern Excel.
165
What is the purpose of the INDIRECT() function?
Returns a cell reference specified by a text string.
166
How do you apply conditional formatting based on another cell?
Use a formula rule in conditional formatting, e.g., `=$B2="Yes"`.
167
What is the difference between a join and a blend in Tableau?
Join combines data at the row level within Tableau; Blend combines aggregated data from different sources.
168
What is a calculated field in Tableau?
A new field created by applying formulas to existing data.
169
What is a parameter in Tableau?
A dynamic input control that can replace a constant value in calculations, filters, or reference lines.
170
What are measures and dimensions?
Dimensions are categorical fields (e.g., Date, City); Measures are numerical fields used for analysis.
171
What is DAX in Power BI?
Data Analysis Expressions, a formula language used to create custom calculations.
172
How to create a relationship between tables in Power BI?
Use the `Model` view → drag and drop fields to connect tables.
173
What is the difference between Direct Query and Import mode in Power BI?
Import mode loads data into Power BI; Direct Query queries the database in real-time.
174
What is a drill-down in Power BI?
Allows users to explore data hierarchies by clicking to see detailed levels.
175
What is correlation?
A statistical measure showing the strength and direction of a relationship between two variables.
176
What is linear regression?
A model that predicts a continuous target variable based on one or more predictors.
177
What is classification in machine learning?
Predicting discrete labels (e.g., Yes/No, Category A/B).
178
What is the difference between correlation and causation?
Correlation shows a relationship; causation means one variable directly affects another.
179
What is A/B testing?
A statistical test comparing two versions of a variable to determine which performs better.
180
What is multicollinearity?
When independent variables in a regression model are highly correlated, leading to unreliable estimates.
181
What is a confusion matrix?
A table showing true positives, true negatives, false positives, and false negatives in classification.
182
What is precision vs. recall?
Precision = TP / (TP + FP); Recall = TP / (TP + FN).
183
What is an ROC Curve?
A graphical plot showing the performance of a classification model at all classification thresholds.
