🖇 Link to the recording
https://tripleten.zoom.us/rec/share/cflomgOseHFFR3EP_6g6sR_HfEV7w4EvdZLi_EX9uoxIrwfEIAK93D_9_gkj8wyo.kI72TnRCkVLN-hho
Passcode: ArLx.BV1
| Skill |
Grade (out of 10) |
| SQL |
|
| Power BI |
|
| Spreadsheets |
|
| Tableau |
|
Overall Impression & Personal Recommendations
Please go through action items and practice materials.
🏆 Went Well
- Put the more technical answers into understandable terms.
🏋️ To Work On
| SQL |
|
|
| Question |
Exemplary Answer |
Student Answer |
| What is the difference between a primary key and a foreign key? |
- A primary key is used to uniquely identify each row in a table. |
|
- A foreign key is used to link tables together, establishing a relationship between them. The data referenced by the foreign key exists as a primary key in another table. | Primary Key is unique, for example every customer has a unique key that cannot be copied or another customer cannot have that |
| What is the difference between inner join and left join? | INNER JOIN returns rows where there is a match on a certain field from both tables, LEFT JOIN returns all rows from the left table and the matched rows from the right table. | Inner Join is only returns matching values |
| What is the purpose of GROUP BY in a SQL statement? | GROUP BY is used to aggregate rows based on certain parameters, often used with aggregate functions like COUNT, MIN, MAX, SUM, AVG | Data that groups data, several different departments |
| Explain what a subquery is and give an example of one. | A subquery is a query nested inside another query. It's used for complex queries where the result of one query depends on another. | COME BACK TO |
| How do you use CASE statements? | A CASE statement in SQL is a fundamental decision structure leveraged in the SELECT clause. | COME BACK TO |
| How would you explain the difference between HAVING and WHERE in SQL? | The WHERE clause is used to filter rows before any groupings are made, while the HAVING clause is used to filter data after it has been grouped (but before it is returned). | WHERE is like finding the values |
| Write me a query that returns the SUM of Salary by Department. | SELECT SUM(Salary)
FROM employees
GROUP BY Department | SELECT
DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID; |
| Write me a query that gives us all of the names of the people who work in the Sales Department that make more than $20,000. | SELECT Name
FROM employees
WHERE Department = ‘Sales’ AND
Salary > 20000 | SELECT
EmployeeName
FROM
Employees
WHERE
DepartmentID = (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'Sales')
AND Salary > 20000; |
| Power BI | | |
| What is DAX and why is it important? | DAX stands for Data Analysis Expressions. It is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return values. DAX is important in Power BI for creating custom calculations and enhancing data models for more detailed and complex data analysis. | DAX is where we write the code to calculate SUM, AVG, Percentage, use it for calculation - Formula language tool that we use in Power BI |
| What is a Model in PowerBI? | A model in Power BI is a logical representation of how data is structured and related within the tool. It is a collection of tables and relationships between them that are used to create reports and visualizations. | REVIEW |
| What are some best practices for creating effective and user-friendly dashboards in Power BI? | Effective Power BI dashboards should be clear, intuitive, and provide valuable insights at a glance. Best practices include:
- Using consistent and meaningful color schemes.
- Choosing the right type of chart for the data.
- Avoiding clutter by not overcrowding the dashboard with too many visuals.
- Ensuring that the most important information is the most prominent.
- Using slicers or filters for interactive exploration.
- Keeping the audience in mind and tailoring the dashboard to their needs and level of expertise. | Define object, design with clarity and simplicity, use the with addition (?) |
| Spreadsheets | | |
| Question | Exemplary Answer | Student Answer |
| What is a VLOOKUP? | VLOOKUP is a function in Excel that allows the user to find data within another table corresponding to a particular row. | |
| Why would you create a PivotTable? | We can use PivotTables to summarize data very quickly for analysis. | |
| Difference between absolute and relative reference? | An absolute reference in a spreadsheet is a fixed point that does not change when the formula is copied to another cell. Absolute references are denoted by the use of dollar signs in the cell address.
A relative reference, on the other hand, changes when the formula is copied to another cell. It is relative to the position of the cell that contains the formula. | |
| What is conditional formatting and why would you use it? | Conditional formatting in Excel allows you to automatically apply formatting—such as colors, icons, or data bars—to cells based on their values. | |
| How do you filter data? What are some use cases? | Filtering data in Excel allows you to display only the rows that meet certain criteria. To apply a filter, click on the Data tab, then select Filter – dropdown arrows will be added to each column header. Use these dropdown menus to filter the data. | |
| How do you remove duplicates? | Select a column —> go to the Data tab —> Data Cleanup —> option to remove duplicates | |
| Tableau | | |
| How can you create a calculated field in Tableau? | To create a calculated field in Tableau, you can right-click in the data pane, select "Create Calculated Field," and then enter the desired formula or expression using Tableau's calculation syntax. | |
| Explain the Difference Between Tableau Worksheet, Dashboard, Story, and a Workbook. | - A worksheet is the space where you create individual visualizations. - A dashboard is a collection of several worksheets. - A story is a sequence of worksheets or dashboards that are presented together. - A workbook is the actual Tableau file that encompasses all 3 above. | |
| Imagine you are a Business Intelligence Analyst and you have been given a dataset containing sales data for the past year. The data includes monthly sales figures, sales by different regions (North, South, East, West), and product categories. Which type of visualization would you choose to best present this data and why? | -- Line charts to visualize sales over the year, showing changes over time. - Showing sales by different regions with a bar chart. - Showing top product categories, also with a bar chart or stacked bar chart. | |
| What is the difference between a Live Data Connection and Data Extract in Tableau? | A live connection in Tableau connects directly to the data source and always reflects current data, which is beneficial for real time analysis but can be slower for large datasets. An extract is a snapshot of the data taken at a specific point in time, stored in Tableau’s fast data engine, which can speed up analysis but won't reflect real-time changes. | |
| Parameters utilized in a dashboard? | Parameters in Tableau are dynamic values that replace constants in calculations, filters, and reference lines. They are user inputs that control some aspect of the dashboard, such as a variable threshold or a switch between different measures. For example, a parameter could allow users to switch between viewing sales data by month, quarter, or year. | |
| What are filters? How can you apply them to multiple sheets? | Filters in Tableau are used to restrict the data that is displayed in the visualization. To apply filters to multiple sheets, you can right click on the pill in the filters section, and then “Apply to Selected Worksheets” | |