Why Most Data Analyst Candidates Can't Defend Their Own Numbers

-- Same data, two denominators
SELECT
  DATE_TRUNC('week', s.started_at) AS week,
  ROUND(100.0 * COUNT(DISTINCT o.session_id)
        / COUNT(DISTINCT s.session_id), 2) AS conv_per_session,
  ROUND(100.0 * COUNT(DISTINCT o.user_id)
        / COUNT(DISTINCT s.user_id), 2)    AS conv_per_user
FROM sessions s
LEFT JOIN orders o
  ON o.session_id = s.session_id
GROUP BY DATE_TRUNC('week', s.started_at)
ORDER BY week;

Interviewer: You say conversion improved. Which conversion?

Candidate: Per user. I counted users who placed at least
one order in the week, divided by users who had at least
one session. I chose per user because the campaign
brought back old users who browse several times before
buying, which pulls per-session conversion down even when
more people actually buy.

Per-session conversion fell slightly in the same period,
and I would mention that in any report.

import pandas as pd

data = pd.DataFrame({
    'month':   ['Sep', 'Sep', 'Oct', 'Oct'],
    'device':  ['desktop', 'mobile', 'desktop', 'mobile'],
    'visits':  [8000, 2000, 4000, 6000],
    'orders':  [800, 100, 440, 330],
})

seg = data.assign(conv=lambda d: d.orders / d.visits)
print(seg.pivot(index='device', columns='month', values='conv'))
# desktop: 0.100 -> 0.110, mobile: 0.050 -> 0.055

total = data.groupby('month')[['visits', 'orders']].sum()
print(total.orders / total.visits)
# Sep 0.090, Oct 0.077 -> overall fell, each segment rose

-- PostgreSQL: mean and median order value per month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  ROUND(AVG(amount), 2) AS mean_value,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)
    AS median_value,
  COUNT(*) AS orders
FROM orders
WHERE status = 'delivered'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- A large gap between mean and median means a few
-- very large orders are pulling the average up.

from statsmodels.stats.proportion import proportions_ztest

# conversions and visitors for version A and version B
conversions = [480, 560]
visitors    = [5000, 5000]

z_stat, p_value = proportions_ztest(conversions, visitors)
print(f'A: {conversions[0]/visitors[0]:.2%}  '
      f'B: {conversions[1]/visitors[1]:.2%}')
print(f'z = {z_stat:.2f}, p = {p_value:.4f}')

# Small groups: same rates, much weaker evidence
z_small, p_small = proportions_ztest([12, 15], [100, 100])
print(f'small sample p = {p_small:.3f}')

"Version B converted at 11.2% against 9.6% for version A,
with five thousand visitors in each group. A two-proportion
z-test gave a p-value well below 0.05, so the difference is
unlikely to be random noise.

Two cautions: the test ran for only two weeks, so I would
watch whether the lift holds, and it says nothing about
whether B's customers return or refund more."

NUMBER: ______________________

Exact definition (what is counted): ____________
Denominator: __________________________________
Time period: __________________________________
Compared with (baseline): _____________________
Relative or absolute change? __________________
Mean or median? _______________________________
Sample size: __________________________________
One thing that could make it misleading: _______

ASK A FRIEND TO FIRE THESE AT YOU

1. Out of what?
2. Compared with when?
3. Is that the average or the typical case?
4. How many records is that based on?
5. Could a change in mix explain it?
6. What would make this number wrong?

Answer each in one or two sentences.
No answer = homework before the interview.

BEFORE
- Improved sales by 25% through data analysis

AFTER (only if true for your work)
- Identified slow-moving SKUs in a sample retail dataset;
  a simulated reorder rule cut stock-out days for top items
  from 6 to 3 per month in back-testing

Rule: every number needs a definition, a baseline and
a source you can explain, or it comes off the resume.