Data Analyst Interview Questions (0-3 Years Experience) | 18 LPA Salary

Securing a role as a data analyst, especially with an attractive package like 18 LPA, requires a blend of technical expertise, problem-solving ability, and a deep understanding of data analysis principles. The role of a data analyst involves working with various tools and technologies to extract, process, analyze, and visualize data in a way that helps businesses make informed decisions. Among the most sought-after skills are SQL, Power BI, and a thorough understanding of data analysis. This article provides an in-depth exploration of common interview questions for data analysts and their corresponding answers, designed to help you navigate challenges and stand out during interviews.

Table Of Contents

SQL Questions

SQL (Structured Query Language) is the backbone of data analysis, especially when it comes to querying large databases and performing complex data manipulations. Below are some common SQL interview questions that are commonly asked for data analyst roles.

1. How do you find the second-highest salary in a department?

Answer:

Finding the second-highest salary within a department is a common SQL interview question. You can use advanced SQL functions like DENSE_RANK() or ROW_NUMBER() to achieve this. These functions assign a rank to rows based on a specific order and criteria, which can be used to identify the second-highest salary.

Example

DepartmentID	EmployeeName	Salary
1	Alice	10000
1	Bob	8000
1	Charlie	8000
2	Dave	15000
2	Eve	14000

WITH RankedSalaries AS (
    SELECT 
        DepartmentID,
        EmployeeName,
        Salary,
        DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT 
    DepartmentID, 
    EmployeeName, 
    Salary
FROM RankedSalaries
WHERE Rank = 2;

Explanation:

• DENSE_RANK() Function: This ranks salaries within each department (using PARTITION BY DepartmentID). Salaries are ranked in descending order (ORDER BY Salary DESC), ensuring the highest salary receives rank 1.
• Filtering for the Second-Highest Salary: By selecting WHERE Rank = 2, the query returns rows corresponding to the second-highest salary in each department.

Output:

DepartmentID	EmployeeName	Salary
1	Bob	8000
2	Eve	14000

This ensures that if two employees have the same salary, they are both ranked fairly.

2. How do you calculate the total number of transactions per user per day?

Answer:

Calculating the total number of transactions per user per day is a common requirement for data analysts. This can be achieved by using the GROUP BY clause along with aggregation functions like COUNT().

Example:

UserID	TransactionDate	TransactionID
1	2024-12-11	101
1	2024-12-11	102
2	2024-12-12	103
1	2024-12-12	104

Explanation:

• GROUP BY Clause: Groups the data by UserID and TransactionDate, ensuring the results are aggregated by these two columns.
• COUNT(TransactionID): Counts the total number of transactions for each user on each day.

Output:

UserID	TransactionDate	TotalTransactions
1	2024-12-11	2
2	2024-12-12	1
1	2024-12-12	1

This query provides a clear view of user activity on a daily basis.

3. How do you find projects with the highest budget-per-employee ratio?

Answer:

Finding projects with the highest budget-per-employee ratio involves calculating a ratio by dividing the project budget by the number of employees assigned to the project. SQL allows us to use JOIN and aggregation functions for this calculation.

Example Scenario:

ProjectID	ProjectName	Budget	EmployeeID
1	Alpha	50000	101
1	Alpha	50000	102
2	Beta	30000	103

Explanation:

• JOIN Clause: Combines the Projects and Employees tables using ProjectID.
• COUNT(e.EmployeeID): Counts the number of employees in each project.
• Calculating the Ratio: The budget is divided by the employee count to calculate the budget-per-employee ratio.
• Sorting by Ratio: The query is sorted by BudgetPerEmployee DESC, ensuring the highest ratios appear at the top.

Output:

ProjectID	ProjectName	BudgetPerEmployee
2	Beta	30000
1	Alpha	25000

This query helps in identifying which projects have the best budget efficiency in terms of employees.

---

Power BI Questions

Power BI is a leading tool in data visualization, offering a wide range of functionalities for transforming and presenting data. Below are some common Power BI-related interview questions and answers that will help you prepare for interviews.

1. What is the difference between Import and Direct Query modes?

Answer:

Power BI offers two modes for handling data: Import Mode and Direct Query Mode. Each mode has its benefits and is suited to different scenarios.

• Import Mode:
  • Data is imported and stored in Power BI’s internal model.
  • Provides better performance and faster loading times.
  • However, the data is static, meaning it doesn’t reflect real-time updates unless refreshed.
• Direct Query Mode:
  • Data remains in the source database, and queries are run in real-time.
  • Ideal for scenarios where up-to-date data is essential, like real-time dashboards.
  • May experience slower performance, especially with large datasets.

Scenario:

• Use Import Mode for historical data analysis where speed is a priority.
• Use Direct Query Mode when dashboards need live data updates, but expect slower performance with large datasets.

2. What are slicers, and how do they differ from visual-level filters?

Answer:

Both slicers and visual-level filters help control the data displayed in Power BI reports. However, their usage and functionality differ slightly.

Slicers:

• Slicers are interactive filters that allow users to select values dynamically.
• They are placed on the report page and can control multiple visuals at once.
• Often used for providing users with the ability to filter data based on a specific field, such as selecting a year or region.

Visual-Level Filters:

• These filters are applied to individual visualizations.
• Operate in the background and cannot be directly interacted with by the user.
• Useful for limiting the scope of data in a specific visual, like showing only the top 5 products in a sales chart.

Example Scenario:

• A slicer allows users to choose a specific year, and all visuals on the page update based on that year.
• A visual-level filter may limit a chart to display only the top-performing products.

3. How do you implement Row-Level Security (RLS) in Power BI?

Answer:

RLS allows you to restrict data visibility based on user roles, ensuring that users only see the data relevant to them.

1. Define Roles: Create roles in Power BI Desktop (e.g., “Regional Manager”).
2. Apply DAX Filters: Use expressions like USERPRINCIPALNAME() to filter data for each role.
3. Assign Roles: In Power BI Service, assign users to the appropriate roles.

Example Scenario:

A sales manager in Region A will only see data for Region A, while another manager in Region B will only have access to Region B data.

4. What are paginated reports, and when should you use them?

Answer:

Paginated reports are designed for printing or exporting detailed, multi-page reports. Unlike interactive reports, paginated reports are static and have fixed layouts.

They are ideal for formal documents such as invoices, financial statements, or long-form reports. Fixed layouts ensure that the report looks consistent across pages, even when exported. We should use paginated reports when precise formatting and detailed output are required, especially for documents that will be printed or exported.

---

Mastering SQL and Power BI is essential for a successful data analyst interview, especially when applying for roles with a lucrative package like 18 LPA. By thoroughly understanding SQL queries, Power BI functionalities, and data analysis principles, you can confidently approach interviews and stand out as a strong candidate. Remember that interviewers not only look for technical knowledge but also for problem-solving skills, analytical thinking, and the ability to communicate complex data insights effectively. Preparing for the common questions discussed in this article will help you excel in interviews and advance in your career as a data analyst.