Вопросы на собеседовании аналитика данных на английском

Пример ответа · EN

WHERE filters rows before grouping, so it cannot use aggregate functions. HAVING filters groups after GROUP BY, so it can check conditions such as COUNT(*) > 10. I put row conditions in WHERE whenever possible, because the database then aggregates fewer rows. A query can use both.

Пример ответа · EN

A LEFT JOIN keeps all rows from the left table and fills missing right-side values with NULL. If I then filter on a right-table column in WHERE, those NULL rows are removed, so the result matches an inner join. To keep them, I move the condition into the ON clause. I compare row counts before and after a join.

Пример ответа · EN

COUNT(*) counts all rows, while COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. So the three numbers can differ on the same table, and the difference itself can reveal missing data. I choose the variant that matches the business question.

Пример ответа · EN

All three number rows within a window in the order set by ORDER BY. ROW_NUMBER gives every row a unique number, even for ties. RANK gives tied rows the same number and then skips, for example 1, 1, 3. DENSE_RANK also gives ties the same number but does not skip: 1, 1, 2. I use ROW_NUMBER to pick one latest record per user.

Пример ответа · EN

First I define what a duplicate means for the business, for example the same email and order date. Then I group by those columns and keep groups with HAVING COUNT(*) > 1. To see or remove the extra rows, I number them with ROW_NUMBER over the same columns and keep the first one. I also find out why the duplicates appeared.

Пример ответа · EN

I use SUM as a window function with ORDER BY, for example SUM(revenue) OVER (ORDER BY day). With PARTITION BY, I can restart the total for each region or product. I check how ties in the ordering column are handled, because the default frame includes peer rows. For a moving average, I set a frame such as the last seven rows.

Пример ответа · EN

I assign each user to a cohort by the month of their first activity. Then for each later activity I calculate the number of months since that first month. I count distinct active users per cohort and period and divide by the cohort size. I agree in advance on what counts as active, because it changes the result a lot.

Пример ответа · EN

The mean is sensitive to outliers, while the median is the middle value and is not pulled by extremes. For skewed data, such as income or order value, a few very large values can make the mean misleading. In that case I report the median, and often the mean too, because the gap between them tells a story.

Пример ответа · EN

I say that it is a range of plausible values for the true number, given the data we have. A 95% interval comes from a method that captures the true value in about 95% of repeated samples. A wide interval means we have little information. I avoid saying there is a 95% chance the true value lies inside this particular range.

Пример ответа · EN

A p-value is the probability of seeing a result at least as extreme as ours if the null hypothesis were true. A small value suggests the data are unlikely under that assumption. It does not give the probability that the hypothesis is true, and it says nothing about the size or business value of the effect. I always report the effect size with an interval.

Пример ответа · EN

A Type I error is a false positive: we conclude there is an effect when there is none. Its rate is controlled by the significance level, for example 5%. A Type II error is a false negative: we miss a real effect. Power is the probability of avoiding it, and larger samples increase power.

Пример ответа · EN

The sample size depends on the minimum effect we care about, the baseline metric, the significance level and the desired power. Fixing it in advance tells us how long the test must run and prevents stopping when the numbers happen to look good. If the required size is too large, we may need to target a bigger effect or a more sensitive metric.

Пример ответа · EN

It happens when the actual split of users between groups differs from the planned one, for example 52/48 instead of 50/50, by more than chance can explain. I check it with a chi-squared test. A mismatch usually means a bug in assignment or tracking, so I do not trust the results until I find the cause.

Пример ответа · EN

If I check the p-value repeatedly and stop at the first significant result, the real false-positive rate becomes much higher than 5%. This is called peeking. I set the duration in advance, or use a method designed for sequential monitoring. Dashboards can show progress, but the decision follows the plan.

Пример ответа · EN

A trend can appear in every group and reverse when the groups are combined, because the groups have different sizes. For example, a new version can convert better on both mobile and desktop but worse overall, if most of its traffic comes from mobile, where conversion is lower. So I check key results by segment before drawing conclusions.

Пример ответа · EN

I start from the question. To compare categories, I use a bar chart sorted by value. For change over time, a line chart works best. To show a relationship between two numbers, I use a scatter plot. I avoid pie charts with many slices and 3D effects, and bar charts always start at zero.

Пример ответа · EN

First I check whether the drop is real: data delays, tracking changes or a broken pipeline. Then I compare with the same weekday and look at recent releases or campaigns. Next I break the metric down by segment, such as platform, country and traffic source, to find where the drop is concentrated. I share early findings quickly and update them as I learn more.