Data Analyst interview questions

The three functions differ in how they assign positions when values tie.

• ROW_NUMBER gives every row a unique sequential number, even when values tie.
• RANK gives tied rows the same number and skips later positions.
• DENSE_RANK gives tied rows the same number without leaving gaps.
• Use ROW_NUMBER for one-row deduplication and choose rank semantics deliberately for top-N results.

I deduplicate by ranking each user's records from newest to oldest.

• Compute ROW_NUMBER over PARTITION BY user_id and ORDER BY updated_at DESC.
• Filter the result to rn = 1 to retain one complete row per user.
• Add a stable tiebreaker such as the primary key after updated_at.
• Avoid GROUP BY with MAX when you need columns from the same latest record.

A reliable 7-day rolling average starts from a complete daily series.

• Aggregate revenue to exactly one row per calendar day.
• Apply AVG over ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
• Join to a date spine so days without orders still appear.
• Fill missing daily revenue with zero when no orders truly means zero revenue.

I choose among these forms based on readability, reuse, and materialization needs.

• Use CTEs to name transformation stages and keep normal analytical SQL readable.
• Use a subquery for a small inline lookup or one simple nested operation.
• Use a temp table when several downstream queries reuse an expensive intermediate result.
• Check engine behavior because some warehouses re-execute a referenced CTE instead of materializing it.

A right-table filter in WHERE is the usual reason a LEFT JOIN loses unmatched rows.

• Unmatched right-side columns are NULL after the join.
• A condition such as WHERE orders.status = 'paid' rejects those NULL rows.
• The surviving result therefore behaves like an INNER JOIN.
• Move the right-side condition into ON when unmatched left rows must remain.

Exact distinct counts are expensive because the engine must track every unique value.

• The operation needs substantial memory, hashing, or sorting on large tables.
• Approximate functions or HyperLogLog sketches trade roughly 1 to 2 percent error for much less compute.
• Pre-aggregate recurring distinct metrics into summary tables when exact reporting is required.
• Choose the method from the accuracy requirement rather than defaulting to the most expensive option.

Basic first and last order dates can be computed in one grouped query.

• GROUP BY user_id and calculate MIN(order_date) and MAX(order_date).
• Apply the database's date-difference function to those two values.
• Use FIRST_VALUE or LAST_VALUE when attributes from the boundary orders are also needed.
• Give LAST_VALUE an unbounded following frame so it does not return the current row by default.

LAG and LEAD expose neighboring rows without a self-join.

• LAG reads a previous row and LEAD reads a following row within the chosen window.
• LAG(revenue) over ordered months places prior-month revenue beside the current value.
• Month-over-month growth is then current minus previous, divided by previous.
• Applying LAG to event timestamps also supports action-gap, session, and churn analysis.

I separate query regressions caused by data, plans, and warehouse conditions.

• Compare current row counts, new partitions, and recent backfills with the last healthy run.
• Inspect the execution plan for lost partition pruning, full scans, or changed join order.
• Check whether a join became many-to-many and created a large fanout.
• Verify queue contention and compute size before assuming the SQL itself regressed.

I treat SQL NULLs as unknown values rather than ordinary comparable values.

• Prefer NOT EXISTS because NOT IN can return no rows when its subquery contains NULL.
• Remember that column-based counts and most aggregates skip NULL while all-row counts do not.
• Use COALESCE only where the business meaning of a replacement value is explicit.
• Profile NULL rates and use IS DISTINCT FROM for NULL-safe comparisons where supported.

A window function is the normal choice for comparing each row with its group average.

• AVG(order_value) OVER (PARTITION BY customer_id) keeps the original row grain.
• The deviation can be calculated directly beside each order.
• A pre-aggregated self-join adds code and creates another opportunity for join-key errors.
• Use the join form only for limited window support or aggregates built from a different filtered row set.

WHERE filters source rows, while HAVING filters groups after aggregation.

• Put non-aggregate conditions in WHERE so unwanted rows are removed early.
• Use HAVING for conditions such as SUM(amount) > 1000.
• A plain row condition in HAVING may work but wastes aggregation effort.
• Classify each condition by whether it depends on an aggregate result.

Sessionization is a gap-detection problem followed by a cumulative count.

• Use LAG to obtain each user's preceding event timestamp.
• Flag a new session when the prior event is absent or the gap exceeds 30 minutes.
• Take a running SUM of the flag per user to assign session numbers.
• Add a deterministic event tiebreaker when timestamps can be identical.

Percentiles describe skewed distributions better than an average alone.

• Use PERCENTILE_CONT(0.5) for an exact continuous median where supported.
• Use an approximate quantile function for very large warehouse datasets when suitable.
• Heavy-tailed revenue and latency can pull the mean far from a typical observation.
• Report the median with a tail measure such as p90 to show both typical values and the distribution's tail.

I default to UNION ALL unless deduplication is an explicit requirement.

• UNION ALL appends inputs without checking the combined rows for duplicates.
• UNION adds a global sort or hash step that can be costly on wide datasets.
• Automatic deduplication can hide duplicate records that should be investigated.
• Use UNION only after defining which selected columns make two output rows duplicates.

Conditional aggregation is the portable way to pivot known values in SQL.

• Group by the dimension that should remain as rows.
• Build each output column with SUM and a CASE condition for its month.
• A native PIVOT operator is shorter but usually still requires enumerated column values.
• Keep truly dynamic presentation columns in the BI layer rather than generating unstable SQL.

Recursive CTEs are useful when the number of traversal steps is not known beforehand.

• Use them to walk parent-child hierarchies such as org charts or category trees.
• They can also generate date or number sequences on engines without generator functions.
• Rolling up costs through a parent-child department table is a practical example with variable branch depth.
• Add a depth limit or cycle check to prevent malformed relationships from looping indefinitely.

Partitioning and clustering matter because query shape determines how much data is scanned.

• Filter directly on the partition column so the engine can skip irrelevant partitions.
• Avoid wrapping that column in transformations that may prevent partition pruning.
• Filters and joins on clustering columns can reduce scanning within each partition.
• Inspect the table definition before writing a costly warehouse query.

A p-value measures how surprising the observed result is under a no-effect assumption.

• Start by assuming the tested change has no real effect.
• Imagine repeating the experiment and observing sampling variation under that assumption.
• The p-value is the chance of a result at least as extreme as the one observed.
• It is not the probability that the change worked or that the null hypothesis is true.

A confidence interval communicates the sampling uncertainty around an estimate.

• A 95 percent procedure captures the true value in about 95 percent of repeated samples.
• Report an estimated lift together with its lower and upper bounds.
• An interval from -1 to +7 supports different decisions than one from +2 to +4.
• Compare the plausible range with business-relevant gains and losses, not just zero.