SQL Interview Questions

1. How would you find duplicate records in SQL?
2.What are various types of SQL joins?
3.What is a trigger in SQL?
4.What are different DDL,DML commands in SQL?
5.What is difference between Delete, Drop and Truncate?
6.What is difference between Union and Union all?
7.Which command give Unique values?
8. What is the difference between Where and Having Clause?
9.Give the execution of keywords in SQL?
10. What is difference between IN and BETWEEN Operator?
11. What is primary and Foreign key?
12. What is an aggregate Functions?
13. What is the difference between Rank and Dense Rank?
14. List the ACID Properties and explain what they are?
15. What is the difference between % and _ in like operator?
16. What does CTE stands for?
17. What is database?what is DBMS?What is RDMS?
18.What is Alias in SQL?
19. What is Normalisation?Describe various form?
20. How do you sort the results of a query?
21. Explain the types of Window functions?
22. What is limit and offset?
23. What is candidate key?
24. Describe various types of Alter command?
25. What is Cartesian product?

Like this post if you need more content like this ❤️

Someone asked me today if they need to learn Python & Data Structures to become a data analyst. What's the right time to start applying for data analyst interview?

I think this is the common question which many of the other freshers might think of. So, I think it's better to answer it here for everyone's benefit.

The right time to start applying for data analyst positions depends on a few factors:

1. Skills and Experience: Ensure you have the necessary skills (e.g., SQL, Excel, Python/R, data visualization tools like Power BI or Tableau) and some relevant experience, whether through projects, internships, or previous jobs.

2. Preparation: Make sure your resume and LinkedIn profile are updated, and you have a portfolio showcasing your projects and skills. It's also important to prepare for common interview questions and case studies.

3. Job Market: Pay attention to the job market trends. Certain times of the year, like the beginning and middle of the fiscal year, might have more openings due to budget cycles.

4. Personal Readiness: Consider your current situation, including any existing commitments or obligations. You should be able to dedicate time to the job search process.

Generally, a good time to start applying is around 3-6 months before you aim to start a new job. This gives you ample time to go through the application process, which can include multiple interview rounds and potentially some waiting periods.

Also, if you know SQL & have a decent data portfolio, then you don't need to worry much on Python & Data Structures. It's good if you know these but they are not mandatory. You can still confidently apply for data analyst positions without being an expert in Python or data structures. Focus on highlighting your current skills along with hands-on projects in your resume.

Hope it helps :)

Goldman Sachs senior data analyst interview asked questions

SQL

1 find avg of salaries department wise from table
2 Write a SQL query to see employee name and manager name using a self-join on 'employees' table with columns 'emp_id', 'name', and 'manager_id'.
3 newest joinee for every department (solved using lead lag)

POWER BI

1. What does Filter context in DAX mean?
2. Explain how to implement Row-Level Security (RLS) in Power BI.
3. Describe different types of filters in Power BI.
4. Explain the difference between 'ALL' and 'ALLSELECTED' in DAX.
5. How do you calculate the total sales for a specific product using DAX?

PYTHON

1. Create a dictionary, add elements to it, modify an element, and then print the dictionary in alphabetical order of keys.
2. Find unique values in a list of assorted numbers and print the count of how many times each value is repeated.
3. Find and print duplicate values in a list of assorted numbers, along with the number of times each value is repeated.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Hope this helps you 😊

For a data analytics interview, focusing on key SQL topics can be crucial. Here's a list of last-minute SQL topics to revise:

1. SQL Basics:
• SELECT statements: Syntax, SELECT DISTINCT
• WHERE clause: Conditions and operators (>, <, =, LIKE, IN, BETWEEN)
• ORDER BY clause: Sorting results
• LIMIT clause: Limiting the number of rows returned

2. Joins:
• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN
• Understanding join conditions and scenarios for each type of join

3. Aggregation and Grouping:
• GROUP BY clause
• HAVING clause: Filtering grouped results
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX

4. Subqueries:
• Nested subqueries: Using subqueries in SELECT, FROM, WHERE, and HAVING clauses
• Correlated subqueries

5. Common Table Expressions (CTEs):
• Syntax and use cases for CTEs (WITH clause)

6. Window Functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD() and LAG()
• PARTITION BY clause

7. Data Manipulation:
• INSERT, UPDATE, DELETE statements
• Understanding transaction control with COMMIT and ROLLBACK

8. Data Definition:
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL

9. Indexing:
• Purpose and types of indexes
• How indexing affects query performance

10. Performance Optimization:
• Understanding query execution plans
• Identifying and resolving common performance issues

11. SQL Functions:
• String functions: CONCAT, SUBSTRING, LENGTH
• Date functions: DATEADD, DATEDIFF, GETDATE
• Mathematical functions: ROUND, CEILING, FLOOR

12. Stored Procedures and Triggers:
• Basics of writing and using stored procedures
• Basics of writing and using triggers

13. ETL (Extract, Transform, Load):
• Understanding the process and SQL's role in ETL operations

14. Advanced Topics (if time permits):
• Understanding complex data types (JSON, XML)
• Working with large datasets and big data considerations

Hope it helps :)

Starting as a data analyst is a great first step in your career. As you grow, you might discover new interests:

• If you love working with statistics and machine learning, you could move into Data Science.

• If you're excited by building data systems and pipelines, Data Engineering might be your next step.

• If you're more interested in understanding the business side, you could become a Business Analyst.

Even if you decide to stay in your data analyst role, there's always something new to learn, especially with advancements in AI.

There are many paths to explore, but what's important is taking that first step.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Hope this helps you 😊

Essential Power BI Interview Questions for Data Analysts:

🔹 Basic Power BI Concepts:

Define Power BI and its core components.

Differentiate between Power BI Desktop, Service, and Mobile.


🔹 Data Connectivity and Transformation:

Explain Power Query and its purpose in Power BI.

Describe common data sources that Power BI can connect to.


🔹 Data Modeling:

What is data modeling in Power BI, and why is it important?

Explain relationships in Power BI. How do one-to-many and many-to-many relationships work?


🔹 DAX (Data Analysis Expressions):

Define DAX and its importance in Power BI.

Write a DAX formula to calculate year-over-year growth.

Differentiate between calculated columns and measures.


🔹 Visualization:

Describe the types of visualizations available in Power BI.

How would you use slicers and filters to enhance user interaction?


🔹 Reports and Dashboards:

What is the difference between a Power BI report and a dashboard?

Explain the process of creating a dashboard in Power BI.


🔹 Publishing and Sharing:

How can you publish a Power BI report to the Power BI Service?

What are the options for sharing a report with others?


🔹 Row-Level Security (RLS):

Define Row-Level Security in Power BI and explain how to implement it.


🔹 Power BI Performance Optimization:

What techniques would you use to optimize a slow Power BI report?

Explain the role of aggregations and data reduction strategies.


🔹 Power BI Gateways:

Describe an on-premises data gateway and its purpose in Power BI.

How would you manage data refreshes with a gateway?


🔹 Advanced Power BI:

Explain incremental data refresh and how to set it up.

Discuss Power BI’s AI and Machine Learning capabilities.


🔹 Deployment Pipelines and Version Control:

How would you use deployment pipelines for development, testing, and production?

Explain version control best practices in Power BI.

I have curated the best interview resources to crack Power BI Interviews 👇👇
https://news.1rj.ru/str/DataSimplifier

You can find detailed answers here

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

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.

Top 8 Excel interview questions data analysts 👇👇

1. Advanced Formulas:
   - Can you explain the difference between VLOOKUP and INDEX-MATCH functions? When would you prefer one over the other?
   - How would you use the SUMIFS function to analyze data with multiple criteria?

2. Data Cleaning and Manipulation:
   - Describe a scenario where you had to clean and transform messy data in Excel. What techniques did you use?
   - How do you remove duplicates from a dataset, and what considerations should be taken into account?

3. Pivot Tables:
   - Explain the purpose of a pivot table. Provide an example of when you used a pivot table to derive meaningful insights.
   - What are slicers in a pivot table, and how can they be beneficial in data analysis?

4. Data Visualization:
   - Share your approach to creating effective charts and graphs in Excel to communicate data trends.
   - How would you use conditional formatting to highlight key information in a dataset?

5. Statistical Analysis:
   - Discuss a situation where you applied statistical analysis in Excel to draw conclusions from a dataset.
   - Explain the steps you would take to perform regression analysis in Excel.

6. Macros and Automation:
   - Have you ever used Excel macros to automate a repetitive task? If so, provide an example.
   - What are the potential risks and benefits of using macros in a data analysis workflow?

7. Data Validation:
   - How do you implement data validation in Excel, and why is it important in data analysis?
   - Can you give an example of when you used Excel's data validation to improve data accuracy?

8. Data Linking and External Data Sources:
   - Describe a situation where you had to link data from multiple Excel workbooks. How did you approach this task?
   - How would you import data from an external database into Excel for analysis?

ENJOY LEARNING 👍👍

Today we will preparing ourselves for Excel Interview Questions. Here are questions that we should be knowing as Fresher Data Analyst

1. What are the basic functionalities of Excel, and how are they used in data analysis?

2. Explain the difference between a worksheet and a workbook in Excel.

3. How do you perform basic arithmetic operations in Excel?

4. Discuss the significance of functions like SUM, AVERAGE, and COUNT in data analysis.

5. How do you filter and sort data in Excel?

6. Explain the importance of pivot tables in data summarization and analysis.

7. How do you create and format charts/graphs in Excel for data visualization?

8. Discuss the usage of VLOOKUP and HLOOKUP functions in Excel.

9. Explain the concept of conditional formatting and its application in Excel.

10. How do you handle missing or NaN values in Excel spreadsheets?

Hope this helps you 😊

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

5 key Python Libraries/ Concepts that are particularly important for Data Analysts

1. Pandas: Pandas is a powerful library for data manipulation and analysis in Python. It provides data structures like DataFrames and Series that make it easy to work with structured data. Pandas offers functions for reading and writing data, cleaning and transforming data, and performing data analysis tasks like filtering, grouping, and aggregating.

2. NumPy: NumPy is a fundamental package for scientific computing in Python. It provides support for large, multi-dimensional arrays and matrices, along with a collection of mathematical functions to operate on these arrays efficiently. NumPy is often used in conjunction with Pandas for numerical computations and data manipulation.

3. Matplotlib and Seaborn: Matplotlib is a popular plotting library in Python that allows you to create a wide variety of static, interactive, and animated visualizations. Seaborn is built on top of Matplotlib and provides a higher-level interface for creating attractive and informative statistical graphics. These libraries are essential for data visualization in data analysis projects.

4. Scikit-learn: Scikit-learn is a machine learning library in Python that provides simple and efficient tools for data mining and data analysis tasks. It includes a wide range of algorithms for classification, regression, clustering, dimensionality reduction, and more. Scikit-learn also offers tools for model evaluation, hyperparameter tuning, and model selection.

5. Data Cleaning and Preprocessing: Data cleaning and preprocessing are crucial steps in any data analysis project. Python offers libraries like Pandas and NumPy for handling missing values, removing duplicates, standardizing data types, scaling numerical features, encoding categorical variables, and more. Understanding how to clean and preprocess data effectively is essential for accurate analysis and modeling.

By mastering these Python concepts and libraries, data analysts can efficiently manipulate and analyze data, create insightful visualizations, apply machine learning techniques, and derive valuable insights from their datasets.

Tableau Cheat Sheet ✅

This Tableau cheatsheet is designed to be your quick reference guide for data visualization and analysis using Tableau. Whether you’re a beginner learning the basics or an experienced user looking for a handy resource, this cheatsheet covers essential topics.

1. Connecting to Data
   - Use *Connect* pane to connect to various data sources (Excel, SQL Server, Text files, etc.).

2. Data Preparation
   - Data Interpreter: Clean data automatically using the Data Interpreter.
   - Join Data: Combine data from multiple tables using joins (Inner, Left, Right, Outer).
   - Union Data: Stack data from multiple tables with the same structure.

3. Creating Views
   - Drag & Drop: Drag fields from the Data pane onto Rows, Columns, or Marks to create visualizations.
   - Show Me: Use the *Show Me* panel to select different visualization types.

4. Types of Visualizations
   - Bar Chart: Compare values across categories.
   - Line Chart: Display trends over time.
   - Pie Chart: Show proportions of a whole (use sparingly).
   - Map: Visualize geographic data.
   - Scatter Plot: Show relationships between two variables.

5. Filters
   - Dimension Filters: Filter data based on categorical values.
   - Measure Filters: Filter data based on numerical values.
   - Context Filters: Set a context for other filters to improve performance.

6. Calculated Fields
   - Create calculated fields to derive new data:
     - Example: Sales Growth = SUM([Sales]) - SUM([Previous Sales])

7. Parameters
   - Use parameters to allow user input and control measures dynamically.

8. Formatting
   - Format fonts, colors, borders, and lines using the Format pane for better visual appeal.

9. Dashboards
   - Combine multiple sheets into a dashboard using the *Dashboard* tab.
   - Use dashboard actions (filter, highlight, URL) to create interactivity.

10. Story Points
    - Create a story to guide users through insights with narrative and visualizations.

11. Publishing & Sharing
    - Publish dashboards to Tableau Server or Tableau Online for sharing and collaboration.

12. Export Options
    - Export to PDF or image for offline use.

13. Keyboard Shortcuts
    - Show/Hide Sidebar: Ctrl+Alt+T
    - Duplicate Sheet: Ctrl + D
    - Undo: Ctrl + Z
    - Redo: Ctrl + Y

14. Performance Optimization
    - Use extracts instead of live connections for faster performance.
    - Optimize calculations and filters to improve dashboard loading times.