Data analyst interview questions, answered step by step.

Most data analyst interviews test SQL and then ask you to reason through a business problem. Here’s one of each, fully answered.

Free question 1 (SQL): Top 3 products by revenue, every month

You have a table orders(order_id, order_date, product_id, quantity, unit_price). Write a query that returns the top 3 products by revenue for each month.

Model answer

Say the plan first: “I’ll total revenue per product per month, rank products inside each month, then keep ranks 1 to 3.”

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_id,
    SUM(quantity * unit_price) AS revenue
  FROM orders
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    month,
    product_id,
    revenue,
    RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rnk
  FROM monthly
)
SELECT month, product_id, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY month, rnk;

Why it works: the first step makes one row per product per month. The window function ranks products separately inside each month (PARTITION BY month), from highest revenue down. The outer query keeps the top three.

Ties, the follow-up you’ll get: RANK gives tied products the same rank, so you can get more than 3 rows in a month. DENSE_RANK does the same but leaves no gaps in the numbers. ROW_NUMBER always returns exactly 3 but picks between ties arbitrarily, so add a second sort column, like product_id, to make it repeatable. Ask which the business wants.

Other things to mention: you can’t filter on a window function in WHERE inside the same query, which is why the ranking sits in its own step. DATE_TRUNC is PostgreSQL. In SQL Server you’d use DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) instead. If refunds are stored as negative quantities, check with the interviewer whether they should count.

Free question 2 (business case): Weekly sign-ups dropped 20%. Why?

Your manager says new account sign-ups fell 20% last week compared with the week before. How do you find out what happened?

Model answer

1. Check the number is real. Before looking for a business cause, rule out a data cause. Did tracking change, did a pipeline run late, did the definition of “sign-up” change? A 20% drop overnight is often a broken tag.

2. Put it in context. Compare with the same week last year and the last 8 to 12 weeks. A holiday week, like Canadian Thanksgiving, or the end of a promotion can explain a drop on its own.

3. Find where the drop sits. Split sign-ups by channel (search, paid, email, referral), device, app or web, region, and new versus returning visitors. If one segment explains most of the drop, you’ve found your lead.

4. Find which step broke. Walk the funnel: visits to the sign-up page, form starts, form submits, confirmed accounts. If visits are flat but submits fell, the problem is on the page. If visits fell, the problem is upstream, like a paused ad campaign.

5. Line it up with changes. Check what launched or changed that week: a release, a pricing change, an ad budget cut, a search ranking drop.

6. Size it and recommend. For example: “About 80% of the drop came from Android after Tuesday’s release, where the submit button fails on older phones. Rolling back should recover roughly 15 points of the 20.” Then say how you’d confirm the fix worked.

What interviewers listen for: you check data quality first, you segment before guessing, and you end with a number and a recommendation, not just a list of possible causes.