What Separates a Memorized Answer From Real Data Analyst Skill

Interview Insights · Data Analyst

What Separates a Memorized Answer From Real Data Analyst Skill

SECTION 01How a memorized answer gives itself away

A memorized answer has a particular sound. It comes out fast, in one block, often with the exact variable names from a popular website. When I ask why it works, the candidate repeats the answer more slowly instead of explaining it. When I change one detail, they either freeze or try to stretch the old answer to fit.

The classic example is the second highest salary query. Almost every candidate can write the version with MAX inside a subquery. Fewer can say what happens when two employees share the top salary, what happens with NULLs, or how to get the second highest within each department.

-- Second highest salary overall
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Works, and handles ties at the top correctly:
-- if two people earn the max, it still returns the next
-- distinct salary. Returns NULL if only one salary exists.
-- MAX also ignores NULL salaries.

-- Twist: second highest salary in EACH department
WITH ranked AS (
  SELECT department_id,
         employee_id,
         salary,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS salary_rank
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE salary_rank = 2;

-- DENSE_RANK: tied top earners share rank 1, so rank 2
-- is the next distinct salary. Departments with only one
-- distinct salary return no row; mention this.

A candidate with real understanding often says something like, 'The subquery version does not extend nicely to groups, so I would switch to a window function.' That sentence alone tells me they understand both approaches rather than one memorized pattern.

SECTION 02Common twists on classic analyst questions

Twists follow a small number of patterns. Once you know them, you can practise each classic question with every twist, and you will rarely be surprised. The most common are: do it per group, handle ties, handle NULLs or missing data, reset the calculation over time, and keep one row instead of many.

Window functions solve a large share of these twists, because they let you compute per-group values while keeping every row. If you understand PARTITION BY and ORDER BY inside OVER, many twists become small edits instead of new problems.

-- Classic: running total across all days
SELECT order_date,
       daily_sales,
       SUM(daily_sales) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- Twist: reset at the start of every month
SELECT order_date,
       daily_sales,
       SUM(daily_sales) OVER (
         PARTITION BY DATE_TRUNC('month', order_date)
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS month_to_date
FROM daily_sales
ORDER BY order_date;

-- Classic: which emails are duplicated?
SELECT LOWER(TRIM(email)) AS clean_email, COUNT(*) AS copies
FROM signups
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

-- Twist: keep only the most recent signup per email
WITH numbered AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY created_at DESC
         ) AS rn
  FROM signups
)
SELECT *
FROM numbered
WHERE rn = 1;

The explicit ROWS BETWEEN clause in the month-to-date query matters. Without it, many databases default to a RANGE frame, which treats rows with the same date as one group. If your table had two rows for the same day, the running total would jump unexpectedly. Mentioning this is a clear sign of real experience.

SECTION 03Explaining trade-offs is the strongest proof

The clearest sign of real skill is the ability to compare approaches. A candidate who can say when a subquery is fine, when a window function is better, and what each costs, has clearly worked with both. Memorization rarely includes trade-offs, because popular answers present one solution as 'the' answer.

Trade-off questions also appear outside SQL. In pandas, should you use groupby or pivot_table? In Excel, is a pivot table or SUMIFS better for a recurring report? In Uncodemy mock interviews, we often ask learners to solve a problem two ways and then choose one, because that choice reveals understanding faster than any single answer.

import pandas as pd

sales = pd.DataFrame({
    'region':  ['North', 'North', 'South', 'South', 'South'],
    'month':   ['Sep', 'Oct', 'Sep', 'Oct', 'Oct'],
    'revenue': [120, 150, 90, 60, 50],
})

# Way 1: groupby -> long format, easy to filter and chart
long = sales.groupby(['region', 'month'], as_index=False)['revenue'].sum()
print(long)

# Way 2: pivot_table -> wide cross-tab, easy to read
wide = sales.pivot_table(index='region', columns='month',
                         values='revenue', aggfunc='sum', fill_value=0)
print(wide)

# Both give the same totals: North Sep 120, Oct 150;
# South Sep 90, Oct 110.

Interviewer: Why did you use a window function here
instead of a subquery?

Candidate: For one overall value, the subquery is simple
and readable, and I would happily use it. But you asked
for every department. With a subquery I would need a
correlated query per department, which is harder to read
and easy to get wrong with ties.

The window function ranks salaries within each department
in one pass, and changing to the third highest is just
changing the number in the WHERE clause. So it is more
flexible for this version of the question.

Notice the candidate does not claim one approach is always better. Saying 'I would happily use the subquery for the simple case' is a mature answer, and it is exactly what memorized responses lack.

SECTION 04Learning by variation: a practice method

Rote practice means solving the same question until you remember the answer. Variation practice means solving one question, then changing one constraint at a time. It takes the same time but builds understanding instead of memory.

CLASSIC: ______________________________

Solve it. Then solve each variation:
[ ] Per group (department, city, month)
[ ] With ties
[ ] With NULLs in the key column
[ ] Return Nth instead of 2nd
[ ] Reset over time
[ ] Keep one row instead of many

For each: what changed, and why?

EXPLAIN WITHOUT CODE

Pick a query you wrote. Explain aloud:
- What one row of the input means
- What each clause does to the rows
- What the output's one row means
- What would break it

If you need to read the code to explain it,
you have not understood it yet.

ONE WEEK OF VARIATION PRACTICE

Mon  Second highest salary + 4 twists
Tue  Running totals + reset by month and by user
Wed  Duplicates: find, count, keep latest
Thu  Top N per group with ties
Fri  Month-over-month and same month last year
Sat  Same problems in pandas
Sun  Mock: a partner invents twists you have not seen

SECTION 05Habits that build understanding instead of memory

These habits make sure your preparation produces skill that survives twists.

• Use the names given — In interviews, use the interviewer's table and column names. Defaulting to names from a website suggests memorization.
• Ask how the data looks — Before writing a classic query, ask about ties, NULLs and grouping. It shows you know where twists come from.
• Practise twists, not repeats — Never solve the same question twice in the same form. Change one constraint each time.
• Say why each clause exists — When you finish a query, describe what each part does in one line. It builds the model that memory lacks.
• Compare two solutions — Solve important problems two ways and decide when each is better. Trade-offs are the clearest proof of skill.

SECTION 06Interview Q&A — Memorized vs real

SECTION 07Test yourself — Memorized vs real quiz

SECTION 08Frequently asked questions

SECTION 09Related reads