Why Some Data Analyst Candidates Ace Every Round Except the Last

Manager: Our repeat purchase rate fell last quarter. How would you look into it?

Candidate: I would write a SQL query to calculate repeat rate
by month, then build a dashboard in Power BI with slicers
for region and category. Then I would share the dashboard.

Manager: Our repeat purchase rate fell last quarter. How would you look into it?

Candidate: First I want to check the definition did not change,
for example whether returns or cancelled orders were counted.

If the definition is stable, I would split the drop into:
- new vs existing customers
- acquisition channel
- first-order category

If the fall is concentrated in customers who came from one
campaign, that is an acquisition-quality problem, not a
retention problem, and the action is different.

I would come back with the one segment that explains most
of the drop and a suggestion for what to test next.

-- Repeat purchase rate by region and quarter
WITH first_orders AS (
  SELECT customer_id,
         MIN(order_date) AS first_date
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
),
repeaters AS (
  SELECT f.customer_id
  FROM first_orders f
  JOIN orders o
    ON o.customer_id = f.customer_id
   AND o.status = 'delivered'
   AND o.order_date >  f.first_date
   AND o.order_date <= f.first_date + INTERVAL '60 days'
  GROUP BY f.customer_id
)
SELECT c.region,
       DATE_TRUNC('quarter', f.first_date) AS cohort_quarter,
       COUNT(*) AS new_customers,
       COUNT(r.customer_id) AS repeated,
       ROUND(100.0 * COUNT(r.customer_id) / COUNT(*), 1) AS repeat_pct
FROM first_orders f
JOIN customers c ON c.customer_id = f.customer_id
LEFT JOIN repeaters r ON r.customer_id = f.customer_id
GROUP BY c.region, DATE_TRUNC('quarter', f.first_date)
ORDER BY cohort_quarter, c.region;

"Looking at customers by the quarter of their first order,
the repeat rate within 60 days dropped in every region,
but most of the drop is in the North.

In the same weeks, the North had more late deliveries.
I think late first deliveries are a likely cause, but I have
not yet checked whether prices changed there too.

My suggestion: compare repeat rates for on-time vs late
first orders in the North. If late ones repeat far less,
fixing delivery is worth more than a discount campaign."

Situation: Weekly sales report for a category team.

Mistake: I joined orders to a product table that had
duplicate product IDs after a catalogue update. Revenue
for two categories was overstated in one week's report.

How I caught it: Total revenue in my report did not match
the finance total, so I traced the gap to the join.

What I did: Told the team the same day, sent the corrected
figures, and added a check that row counts before and after
every join must match unless I expect them to change.

What I learned: Reconcile one total against a trusted
source before sharing any report.

Stakeholder: Your conversion number is wrong. Ours is higher.

Analyst: That is possible. Can we check three things together?
1. Are we counting sessions or unique users?
2. Are we using the same date range and time zone?
3. Are test or internal orders excluded in both?

If we still differ after that, I will trace one day of
data row by row so we know exactly where the gap is.

THE TEAM I AM JOINING

What does the team report on? (sales, ops, marketing, product)
______________________________________

Three metrics they probably care about most:
1. ____________  2. ____________  3. ____________

One question from earlier rounds that hinted at a real problem:
______________________________________

How I would approach that problem in the first two weeks:
______________________________________

STORIES READY FOR THE FINAL ROUND

[ ] Analysis that changed or supported a decision
[ ] Mistake I caught in my own work
[ ] Time I questioned a request or a definition
[ ] Time I explained data to a non-technical person
[ ] Time I worked with unclear requirements

For each story write:
Situation (1 line) | My action (2 lines) | Result (1 line)
| What I would do differently (1 line)

QUESTIONS FOR THE HIRING MANAGER

- What decision did the team's analysis influence recently?
- Where does the data come from, and who owns its quality?
- What would a good first month look like for this role?
- Which stakeholder is hardest to satisfy, and why?
- How do analysts here get feedback on their reports?

Pick two. Asking all five sounds like a checklist.