Best Data Analytics Interview | Data Analyst Live Mock Interview | Must Watch - 2024 !!!

Summary

In this interview, Chris, an electronics and telecommunication engineering student, expresses his passion for data analytics. He shares his proficiency in SQL, Python, and Excel, and discusses his understanding of database transactions and their ACID properties. Chris also touches on the differences between OLTP and OLAP systems and his familiarity with Power BI, including calculated columns, measures, and DAX. He admits to being less versed in certain areas like broadcasting in AI and pandas' Group by function but shows eagerness to learn. The interview concludes with Chris highlighting his strengths, such as a strong drive for learning, and a weakness of perfectionism that can lead to delays.

Takeaways

• 👨‍💻 Chris is a final-year electronics and telecommunication engineering student with a strong interest in data analytics.
• 📈 He has a solid foundation in technical skills such as SQL, Python, Excel, and Power BI, which he has applied during internships and projects.
• 🔍 Chris discovered his passion for data analytics through internet research and a childhood fascination with numbers and patterns.
• 💡 He views data analytics as a career path that involves working with data to uncover trends and patterns, which aligns with his interests.
• 📊 Chris rates his proficiency in SQL as an 8 and has experience with window functions, although he admits to being less familiar with specific functions like 'RANK'.
• 💼 He understands the ACID properties of database transactions, emphasizing the importance of atomicity, consistency, integrity, and durability.
• 📊 Chris is less confident about the differences between OLTP and OLAP systems, acknowledging that he has heard of them but hasn't studied them in-depth.
• 📈 He rates his Excel skills at 7.5, with knowledge of functions like SUMPRODUCT, but is not well-versed in advanced topics like array formulas.
• 📊 In Power BI, Chris differentiates between calculated columns, which create new columns, and measures, which are aggregate calculations based on existing data.
• 🔐 He has a basic understanding of Row Level Security in Power BI, mentioning its connection to third-party involvement and data visualization.
• 🛠 Chris suggests optimizing Power BI reports by cleaning the data, using Power Query for ETL operations, and sharing the reports through the Power BI service.

Q & A

What is Chris's educational background?

Chris is in the last year of his electronics and telecommunication engineering program.

Why is Chris interested in data analytics?

Chris has always been passionate about numbers and problem-solving, which led him to develop an interest in data analytics.

What technical skills has Chris acquired during his studies?

Chris has acquired skills in SQL, Python, Excel, and Power BI.

What is Chris's proficiency level in SQL?

Chris rates his proficiency in SQL as a solid 8 on a scale.

Can you explain the concept of window functions in SQL as described by Chris?

Window functions in SQL are used to apply aggregate, ranking, and analytic functions over a set of rows, partitioned and ordered as specified.

How did Chris approach the task of finding the second highest salary in a table using SQL?

Chris mentioned using two methods: the LIMIT function and subqueries to find the second highest salary.

What is the concept of ACID properties in database transactions as understood by Chris?

Chris understands ACID properties as Atomicity, Consistency, Integrity, and Durability, which ensure the reliability and accuracy of database transactions.

What is the difference between OLTP and OLAP systems according to Chris?

Chris is not very well-versed in the difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems.

How proficient is Chris in Excel, and what functions is he familiar with?

Chris rates his proficiency in Excel at around 7.5. He is familiar with the SUM function but is not very well-versed with other functions like SUMPRODUCT.

What is the difference between calculated columns and measures in Power BI as described by Chris?

Chris explains that measures are like aggregated facts, such as the count of sales, while calculated columns create new columns based on existing data.

What is Chris's understanding of RLS (Row Level Security) in Power BI?

Chris has a basic understanding of RLS as a security feature that prevents unauthorized access to data in visualizations and dashboards.

How does Chris approach optimizing Power BI reports for performance?

Chris would start by cleaning the data, removing null values, and then use Power Query and various visualizations in Power BI to optimize the report.

What is Chris's experience with Python and pandas, particularly with the Group by function?

Chris is not very familiar with the Group by function in pandas, indicating a limited experience with Python for data manipulation.

What is Chris's perspective on the difference between a data scientist and a data analyst?

Chris sees the key difference as data scientists using machine learning to predict future outcomes, while data analysts work with existing data to create reports and draw conclusions.

What are Chris's strengths and weaknesses as he sees them?

Chris's strength is his strong enthusiasm and hunger for learning in the field of data analytics. His weakness is a tendency to focus excessively on making tasks perfect, which can lead to delays.

What projects has Chris worked on related to data analytics?

Chris has created various dashboards using Power BI and has worked on a patient monitoring system that involved storing data using SQL. He also conducted A/B testing during an internship.

Outlines

00:00

😀 Interview Introduction and Resume Discussion

The paragraph introduces an interview setting where Chris, a candidate for a data analyst position, is asked to introduce himself. Chris explains his background in electronics and telecommunication engineering and his passion for numbers and problem-solving, which led him to data analytics. He mentions his technical skills in SQL, Python, Excel, and Power BI, and how he applied these during an internship. Chris also discusses his childhood fascination with numbers and how it influenced his career choice in data analytics.

05:00

🧠 Technical Interview: SQL and Database Concepts

In this segment, the interviewer quizzes Chris on his SQL proficiency, specifically about window functions. Chris explains the concept and use cases of window functions in SQL, including their application in aggregate functions and partitioning data. He is then asked to write a SQL query to find the second-highest salary in a table, suggesting both the LIMIT function and subqueries as methods. The paragraph also covers a discussion on ACID properties in database transactions, where Chris provides an example of UPI transactions to explain atomicity, consistency, integrity, and durability.

10:04

📊 Excel and Power BI Proficiency

The conversation shifts to Chris's skills in Excel and Power BI. Chris rates his Excel proficiency and is asked to explain the use of the SUMPRODUCT function, which he struggles with. He then discusses his ability to create dynamic charts in Excel using named ranges and pivot tables. The paragraph also includes a discussion on the difference between calculated columns and measures in Power BI, with Chris explaining that measures are aggregate functions while calculated columns create new columns based on existing data.

15:05

📈 Power BI and Data Analysis Techniques

Chris is questioned about his understanding of row-level security in Power BI and his approach to optimizing Power BI reports for performance. He mentions cleaning the database, using Power Query for visualization, and sharing reports via the Power BI service. The paragraph also covers Chris's knowledge of the Query Editor in Power BI, its relevance to ETL processes, and his familiarity with pandas' Group By function in Python. Chris admits he is not very familiar with Group By and broadcasting in AI.

🎓 Academic Projects and Closing Remarks

Chris discusses his academic projects, including creating dashboards in Power BI and a patient monitoring system using SQL. He also talks about his internship experience, where he conducted A/B testing for email campaigns. The paragraph concludes with Chris expressing his strengths, such as his enthusiasm for learning and applying skills, and his weakness of sometimes focusing too much on perfection. The interviewer assures feedback will be provided, and Chris is thanked for his participation.

Keywords

💡Data Analyst

A Data Analyst is a professional who collects, processes, and interprets complex digital data to help organizations make decisions. In the script, Chris is applying for a position as a Data Analyst, emphasizing his passion for numbers and problem-solving, which are key traits for this role. His interest in data analytics is sparked by his childhood fascination with numbers and patterns, which aligns with the analytical nature of the job.

💡SQL

SQL (Structured Query Language) is a programming language used to manage and manipulate databases. Chris rates his proficiency in SQL as an 8, indicating a high level of skill. The script mentions SQL in the context of Chris being asked to write a query to find the second-highest salary in a table, showcasing the practical application of SQL in data analysis tasks.

💡Window Function

A Window Function in SQL is used to perform calculations across sets of rows that are related to the current row. Chris explains that window functions apply aggregate, ranking, and analytic functions over a set of rows, which is crucial for data analysis as it allows for complex data manipulation and analysis. This concept is tested when Chris is asked about its use case during the interview.

💡ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties of database transactions intended to ensure reliability. Chris discusses ACID properties in the context of a UPI transaction, explaining how they ensure that transactions are processed reliably and accurately, which is fundamental to maintaining data integrity in databases.

💡OLTP vs. OLAP

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are two different approaches to database management. OLTP focuses on transaction processing, while OLAP is geared towards complex analysis and reporting. Chris is asked to differentiate between the two, indicating the importance of understanding these systems in the field of data analytics.

💡Excel

Microsoft Excel is a widely used spreadsheet program for data organization, analysis, and visualization. Chris rates his proficiency in Excel as 7.5, highlighting its importance in data analysis. The script mentions Excel in the context of using functions like SUMPRODUCT and creating dynamic charts, which are common tasks for a data analyst.

💡Power BI

Power BI is a business analytics service that enables users to visualize data, create reports, and gain business insights. Chris is asked about the difference between calculated columns and measures in Power BI, which are essential concepts for creating meaningful visualizations and reports. His response reflects the need to understand these tools in the data analytics field.

💡Ranking Functions

Ranking functions in SQL, such as RANK and DENSE_RANK, are used to assign a rank to each row within a result set based on the values of a specified column. Chris mentions these functions when discussing window functions, indicating their use in ordering and ranking data, which is a common requirement in data analysis.

💡AB Testing

AB Testing is a method of comparing two versions of a webpage or other user experience to see which performs better. Chris discusses his experience with AB testing during his internship, where he changed email campaigns to see which slogan attracted more attention. This example illustrates the practical application of data analysis in marketing and decision-making.

💡Machine Learning

Machine Learning is a subset of artificial intelligence that provides systems the ability to learn and improve from experience without being explicitly programmed. Chris differentiates between data scientists, who use machine learning to predict future outcomes, and data analysts, who work with existing data. This distinction is important for understanding the broader scope of data-related roles.

Highlights

Chris introduces himself as a final-year electronics and telecommunication engineering student with a passion for numbers and problem-solving.

Chris has laid a strong foundation in technical skills like SQL, Python, Excel, and Power BI during his studies.

Chris had an internship where he applied his data analytics skills, enhancing his practical experience.

Chris explains his interest in data analytics, stemming from a childhood fascination with numbers and patterns.

Chris rates his proficiency in SQL as an 8, demonstrating confidence in his technical abilities.

Chris provides a clear explanation of window functions in SQL, showcasing his understanding of database operations.

Chris is asked to write a SQL query to find the second highest salary, indicating an assessment of his practical SQL skills.

Chris discusses the ACID properties in database transactions, reflecting his knowledge of database integrity and reliability.

Chris admits to being less familiar with OLAP and OLTP systems, showing honesty about his areas for improvement.

Chris rates his proficiency in Excel as 7.5, indicating a good level of skill with this widely-used tool.

Chris is unable to explain the SUMPRODUCT function in Excel, revealing a gap in his knowledge.

Chris demonstrates knowledge of creating dynamic charts in Excel using named ranges and pivot tables.

Chris differentiates between calculated columns and measures in Power BI, showing his understanding of data modeling.

Chris has a basic understanding of Row Level Security in Power BI, acknowledging its role in data visualization.

Chris outlines steps for optimizing Power BI reports for performance, including data cleaning and visualization techniques.

Chris is familiar with the Query Editor in Power BI, recognizing its relevance to ETL processes.

Chris admits to being unfamiliar with the Group By function in pandas, indicating a potential area for learning.

Chris defines the roles of data scientists and data analysts, highlighting the predictive aspect of data science.

Chris identifies his strengths as enthusiasm for the field and a desire for continuous learning, along with a tendency to over-focus on perfection.

Chris discusses his academic and internship projects, including a patient monitoring system and AB testing in digital marketing.

Chris seeks feedback on areas for improvement, showing a proactive approach to professional development.

Transcripts

hey hi uh how are

you uh I'm good sir how are you yes all

good uh thank you uh so I have received

your resume for the position of data

analyst so chrish can you please walk me

through your

profile yes sir thank you for giving me

a chance to introduce myself I'm Chris

and I'm currently in the last year of my

electronics and telecommunication

engineering uh since the beginning I've

been always I've always been passionate

about numbers and problem solving which

uh made me interested in the field of

data analytics M during my studies I

laid a strong foundation in uh in the

technical skills like

SQL python Excel power

Etc and uh I am I was generous enough I

was lucky enough to use the skills

during my last internship Ive also had

certifications related to this doain to

uh help me improve my

skills well so uh you said that you were

for uh telecommunication right yes sirch

okay so uh I mean where did the concept

came of data analytics all of a

sudden uh so I was u i I was searching I

was surfing the internet for my options

and when I got to know about the data

analytics it it just I was very

interested in it and during during my

childhood I've always been found of

numbers and I always used to find the

cars number plates very interesting I

always used to look for a specific

number plate a specific color so when I

found out about data analytics I was

like this is what we will be doing in

our career we will be we will be having

the data and we will be finding the

patterns Trends the underlying patterns

and it immediately struck me that this

is something which I want to pursue in

my career that's when I started to learn

my learn the skills and have a

foundation on

it well so with that uh let's start with

the you know podcast and uh so uh Krish

just let me know how proficient are you

in

SQL uh I would rate myself a solid 8 on

so chish tell me what is the concept and

the use case of window function in

SQL uh window function is uh basically

used to uh basically used in agregate

function then we have different we

basically use to row row it over we we

over it by different

partitions and uh it is basically we can

partition by we can partition it or we

can order it and there are different

aggregate function used so uh it

basically applies aggregate ranking and

analytic function over a particular set

of

rowes okay are you sure on that uh yes

sir okay what apart from you know the

rank yeah sir it it also helps to uh

like

uh do it in rank and dense rank

functions and RO number what about

enti uh sir I'm not very very H not an

issue fine so uh chrish just uh write in

the chat box okay uh just write a query

okay how would you write a query to find

the second highest salary in a table

okay just mention out the query itself

in the chat box okay sir done

the second

highest uh second highest salary in a

table salary

that

uh wait a second

sir so we can use two methods first is

limit and second is uh the

subqueries I sir should I have written

the query for using the limit function

should I also write the query using the

subqueries yes

proceed okay sir just a

minute e

yes sir

okay sure on

that uh yes sir well so coming to the

next question okay uh chrish just let me

know what do you understand by asset

properties in a database

transaction okay so I would like to give

you the answer based on an example so

whenever we are having a transaction

during the UPI so that's when the asset

property comes in the abbreviation of

acid is atomicity consistency

integrity and durability atomicity means

the like let's take an example of a

transaction so atomicity means either

the transaction has taken place but it

hasn't taken place there is no in

between consist consistency means

that uh the database have been updated

updated from both the tables like the

sender and the receiver U integrity

means that even if there is a power loss

or anything still the table is updated

like if should the table table in the

sense

the sender table and the receiver's

table during a

transaction and durability means uh uh

so sorry uh consistency means that even

if multiple senders are uh sending money

to a specific receiver it makes sure

that each one is an individual this and

durability is the is where even if there

is a power loss during the database

still the transaction happens and it is

successfully done okay well I hope there

is no confusion again right yes sure on

that oh yes sir let's get back to the

next question okay so uh what do you

understand or what can you how can you

differentiate the key point points

between olp and oap

systems uh sir I'm not very well ver on

it but I have just I've just heard it

but I have not gone through it okay

online transaction processing yes and

online analytical processing okay it's

for quering online analytical is for you

know again quering and Reporting but

it's I mean oot olp focuses on trans

action oriented applications okay yes so

okay with that chrish just let me know

how proficient are you in

Excel uh so I would rate myself around 7

7.5 on there

right so explain how to use you know the

sum products function and its

application so we can some some like the

so can you repeat the sentence

yes yes yes so my question is I mean

explain how to use the sum product

function and its

application um I'm not very well so some

means the addition of the that's okay

some product multiplies you know

corresponding ranges and Returns the sum

of those products

right okay okay fine not an issue so uh

Chris just let me know how would you use

array formulas in

Excel um

no okay

fine uh do you know how to create

Dynamic charts in Excel using uh named

ranges yes sir we can use uh for the

charts we

can select the table and we can use the

option of pivot

tables

and explain it a bit more

so a pivot table it summarizes the data

and the uh it lets you easily compare

patters and it confirms the data the

trends of the data and it can pivot

table can also analyze large amount of

data okay okay well uh so coming back to

powerbi what is the difference between

calculated columns and measures in power

ba uh sir uh calul um so measures is

like Aur fact column where uh there is a

count based on a data like for

example uh number of sales number of Sal

the salary number or the marks of a

student and so calculated calculated

column it basically creates a new column

based on the existing

data

okay so uh uh Chris just let me know

what do you understand by the concept of

Ro level security in

powerb uh Ro level

security uh

it it basically makes your that uh like

no the third parties are involved in

during the uh making of a visualization

dashboards and it has uh it has direct

connection with Microsoft Zo something

I'm not I just I just know the concept

of Ro level

secur so uh let's say that you are on a

in a project and uh describe me the

steps to optimize powerbi reports for

performance basically okay so first I

will um first I will look at the

database and I will if I will just

remove all the null values I just make

it clean

second I will just uh

create the visualization using and I

will use power query and all the

different charts in

powerbi and

then uh and then if

uh then after filtering and after

creating the visualization there is an

option where we can share the or publish

our work using powerbi service and we

can share the report to the uh to

whoever to the customer or to the

whoever wants it

okay okay so U have you heard about you

know what is the purpose of query editor

in

p u yes I've heard about it but can you

explain me a

bit the query editor basically it makes

sure

that

the like uh the the steps are in the

right order and whether we want to

change some steps during the

visualization is it relevant to

ETL sorry sir is it relevant to

ETL yes power query is relevant to ETL

okay

how we use if you want to extract or

want to transform some data we use power

qu itself so it allows us to import

clean and then transform and then modify

the data set yes ETL operations is done

on power we are using power

query so chrish tell me uh I mean how do

you use Group by functions in pandas I

think you you will be familiar with

pandas and the library using you know

Python and you may subject to the what

are the applications you may follow for

that uh some a little bit familiar with

python uh I've uh I've not I've not used

Group by

function okay okay not an issue okay uh

okay uh can you tell me what do you

understand by broadcasting in

aai okay okay well uh so uh Kish that

was the technical side from my end okay

and uh just tell me I mean what do you

see the difference between a data

science part or a scientist part or data

analyst part can you give

difference so one of the key difference

is that the data scientist data analyst

uh data scientist predicts the data

using machine

learning so uh a data analyst already

have the data and they make a report or

they make a conclusion using the data

but the main main goal of data Cent is

to predict the predict using the using

the data for the future cost they use

machine learning algorithms data

scientist use machine learning

algorithm okay and what about data

analyst the data analyst uh they

basically uh clean transform and modify

the data and uh they make the reports or

conclusion using the existing data

fine so uh I mean chrish why should I

hire you I mean uh can you please let me

know what are your strength and

weakness okay sir so uh one one of the

thing which uh makes me different from

the other is My Strong enthusiasm in

this domain uh I want to learn more and

more about this field and using my

skills and applying it practically in a

company will uh help myself it will help

me to grow as well as it will help your

company to prosper that's one of my

strengths that I'm I'm I I have an

hunger for growth for

Learning and one of my weaknesses is

that uh I sometimes during any

assignment or during any project I focus

on it a lot to make it as perfect as

possible so sometimes it results in an

overdue that that would be my well so

chrish you associated with you know the

mini projects and your main projects in

your academics right so any mini project

or main project related to data have you

done

it uh s like from YouTube I've created

various dashboards I also various

dashboard in powerbi also during my uh

mini project uh during my third year

project I had made a p patient

monitoring system and I had stored the

data using SQL in the back end also

during my last internship I had done AB

testing during an email reading my

company again AB testing AB testing what

it is a testing is basically s where we

uh uh have where we explain something to

n number of people and we explain

another thing to another number of

people and we see the reviews and for

example sir during my internship we had

the it was a digital marketing company

where I had changed the email campaign

so I had showed the previous emails to

some number of people and another emails

slogan to another number of people and I

had I had seen the difference like who

are getting more attracted towards which

slogan and that is

fine so with that U uh chrish I'll let

you know with the outcomes okay your

result will be published soon and thank

you for joining today's podcast thank

you have a great

day so can I can I have a question can I

just say something yes like I just

wanted to say that so what are the I

have a question for you that is what are

the things where I can improve myself

cuz I'm a last we will get back to you

with the feedback okay so just hold on

and we will let you know soon very soon

okay hope