Database Analyst

Data analysis deals with collecting, inspecting, cleansing, transforming and modelling data to glean valuable insights and support better decision making in an organization. The various steps involved in the data analysis process include –

The DISTINCT keyword is used in the SELECT statement to eliminate repetition of identical data. It is also used in aggregate functions. When DISTINCT is used with only one column or expression, the query will strictly return the unique values for that particular column or expression. Similarly, when DISTINCT is used with multiple columns or expressions, the query will return only the unique combinations of those columns or expressions. Note that the DISTINCT keyword doesn’t ignore the NULL value when sifting through data.

This question lets you assess if candidates have the hard skills you need and can tell you what areas they might need training in. It is also another way to ensure basic competency.

What to look for in an answer:

☛ Software the job ad emphasized

☛ Experience with the software

☛ Ability to speak with familiarity

DELETE from EMPLOYEE WHERE firstName = ‘ABCDEF’

Structured Query Language

CROSS JOIN defines a Cartesian product on the sets of records from two or more joined tables, where the number of rows in the first table is multiplied by the number of rows in the second table.

This is one of the most commonly asked data analyst interview questions. Listed below are the requirements needed for becoming a data analyst:

☛ Sound knowledge of statistical packages used in analyzing big datasets like Excel, SAS, SPSS and many others

☛ Very good knowledge of the programming language (Javascript, ETL frameworks or XML), reporting packages (Business Objects) and databases.

☛ Strong technical knowledge in areas like data models, segmentation techniques, data mining and database design

☛ Good skills in knowing how to run analysis, organization, collection and dissemination of big data accurately

Though both WHERE and HAVING are used to filter records, there is a subtle difference between the two. The WHERE clause is used to filter records from a result, whereas HAVING is used to filter groups. If the two are used together, the WHERE clause is applied first to filter rows; only after grouping finishes is the HAVING clause applied.

The difference between data profiling and data mining is:

Data Profiling is aimed at individual attributes’ analysis. Information on different attributes like discrete values, value ranges and their data type, frequency, length are gotten from it. Data mining, on the other hand, targets unusual records detection, cluster analysis, sequence discovery and others.

This question is basic but serves an essential function. It weeds out the candidates who lack a rudimentary understanding of data analysis. It also lets you compare how well various candidates understand data analysis.

What to look for in an answer:

☛ Coverage of each step

☛ Mention of soft skills, such as communication

☛ Discussion of how data analysts benefit a company

My biggest challenge was making prediction sales during the recession period and estimating financial losses for the upcoming quarter. Interpreting the information was a seamless process. However, it was slightly difficult to forecast future trends when the market fluctuates frequently. Usually I analyze and report on data that has already occurred. In this case, I had to research how receding economic conditions impacted varying income groups and then make an inference on the purchasing capacity of each group.

My greatest communication strength would have to be my ability to relay information. I'm good at speaking in a simple, yet effective manner so that even people who aren't familiar with the terms can grasp the overall concepts. I think communication is extremely valuable in a role like this, specifically when presenting my findings. This is even more important when those findings could be beneficial or detrimental to other departments within the company and you need to make sure that everyone understands the overall message.

The steps involved in an analysis project can be listed as:

☛ Problem identification

☛ Exploration of data

☛ Preparation of data

☛ Modeling

☛ Data Validation

☛ Implementation and tracking

This question lets you measure candidates' organizational skills and how well they anticipate. It also gives you an opportunity to see if candidates' leadership or work styles are compatible with your company culture.

What to look for in an answer:

☛ Clear steps

☛ Deliberate process

☛ Consideration of deadline

A view is a virtual table that consists of a subset of data from a table. The content of a view is defined by the query. A view takes up little space because it doesn’t copy all data from the given table but only a subset of data as defined by the view. Note that a view can also display a combination of data from one or more tables. Views allow you to hide the complexity of large data and instead narrow in on areas of interest.

GROUP BY is a statement that divides the result for a particular query into groups of rows. It is often used with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT, which calculate information about each group. The SELECT statement returns one row for each group.

☛ Separate data depending on their attributes

☛ In the case of massive datasets, do a stepwise cleansing and improve on the data on every step until the data quality is good.

☛ For common data cleansing, you need to generate a set of scripts which include blanking out every value not matching a regex.

☛ Do analysis on the statistic for every column.

☛ Stay up to date with all cleaning operations, so changes could make when necessary.

With a question like this, you glean insight into how candidates approach and solve problems. It also gives you a better idea of the type of work they have done.

What to look for in an answer:

☛ Explanation of how challenge(s) were overcome

☛ Lack of blaming others

☛ Discussion of why the project was difficult