Data Analyst Interview Questions

Before I write the query, let me clarify two things: first, when there are salary ties, should both count as "second highest" or do we want strictly one row? I'll assume ties should both appear — so I'll use DENSE_RANK. Second, should departments with only one employee appear in the output? I'll include them as NULL for completeness.

Here's my approach using a CTE:

WITH ranked_salaries AS (
  SELECT employee_id, department_id, salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked_salaries
WHERE salary_rank = 2;

This partitions employees by department, ranks their salaries from highest to lowest, and then filters for rank 2. If we wanted to handle departments with only one employee, we could LEFT JOIN this back to the departments table.

Why DENSE_RANK over RANK? If two employees tie for the highest salary, DENSE_RANK still assigns 2 to the next salary. RANK would skip to 3, which means we'd miss the actual second-highest value.

Before diving in, I'd want to clarify a few things. How is "conversion" defined here — is it visit-to-purchase, visit-to-signup, or something else? What's the baseline period we're comparing against? And is this 15% drop across all users or a specific segment?

Assuming it's visit-to-purchase conversion, here's how I'd structure my investigation:

Step 1: Data validation. First, I'd check if the tracking is working correctly. A deployment or a tracking pixel breaking can cause artificial drops. I'd look at total event counts — if page views also dropped proportionally, it's likely a traffic issue, not a conversion issue.

Step 2: Decompose the metric. Conversion = purchases / visits. Did purchases drop, or did visits spike (diluting the rate)? If visits spiked, it could be a new marketing campaign bringing lower-intent traffic.

Step 3: Segment the drop. I'd run the conversion rate by: traffic channel (organic, paid, direct, referral), device type (mobile vs. desktop), geography (country or city), and user type (new vs. returning). The goal is to find where the drop concentrates. If it's 30% on mobile but flat on desktop, that's a different problem than a uniform 15% drop everywhere.

Step 4: Hypothesize and test. Let's say the drop concentrates on mobile paid traffic. My hypotheses would be: (a) a checkout UI bug on mobile, (b) a new ad creative attracting the wrong audience, or (c) a competitor running a mobile-targeted promotion. I'd validate (a) by checking mobile checkout completion rates and error logs, (b) by looking at click-through-rate vs. bounce rate by ad creative, and (c) by cross-referencing with competitor pricing data if available.

Step 5: Recommend. Once I identify the root cause, I'd present the finding with a clear recommendation. For example: "Mobile checkout error rate increased from 2% to 8% after Tuesday's deploy. The fix is rolling back the checkout component. Estimated recovery: 2-3 days after fix."

Let me define "retention" first: a user is "retained" in month N+1 if they performed at least one qualifying action in both month N and month N+1. I'll assume the events table has user_id, event_type, and event_date columns.

WITH monthly_users AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_date) AS activity_month
  FROM events
  WHERE event_type IN ('purchase', 'page_view', 'session_start')
),
retention AS (
  SELECT
    curr.activity_month,
    COUNT(DISTINCT curr.user_id) AS total_users,
    COUNT(DISTINCT next_month.user_id) AS retained_users
  FROM monthly_users curr
  LEFT JOIN monthly_users next_month
    ON curr.user_id = next_month.user_id
    AND next_month.activity_month = curr.activity_month + INTERVAL '1 month'
  GROUP BY curr.activity_month
)
SELECT
  activity_month,
  total_users,
  retained_users,
  ROUND(100.0 * retained_users / NULLIF(total_users, 0), 1) AS retention_rate_pct
FROM retention
ORDER BY activity_month;

A few notes: I used LEFT JOIN so months with 0% retention still appear. NULLIF prevents division by zero. And I used DATE_TRUNC + interval arithmetic rather than EXTRACT(MONTH) to correctly handle year boundaries (December → January).

The moment that hooked me was during a college project where I analyzed Uber ride data for a stats course. I was just doing the assignment — cleaning the dataset, running basic regressions. But then I noticed that ride cancellation rates spiked at a specific time window on Fridays. I dug deeper, cross-referenced it with weather data and event schedules, and found that surge pricing during rain + event overlap was causing 40% of cancellations.

That was the first time I realized that a few SQL queries and a scatterplot could surface an insight that might actually change a pricing algorithm and save millions. I was hooked on that feeling of "turning messy data into a decision someone can act on."

Since then, I've pursued this deliberately — I completed the Google Data Analytics Certificate, solved 100+ SQL problems on DataLemur, and did a freelance analytics project for a local e-commerce brand where my funnel analysis helped them increase checkout completion by 12%.