How Long It Really Takes to Judge a Data Analyst Candidate

"So I did my schooling from Noida, then I took science
in class 12, then I did my B.Com, and in college I did
many activities. Then I got interested in data, and I
learned Python, R, SQL, Excel, Power BI, Tableau, machine
learning... I am a hardworking and passionate person and
I am a quick learner..."

(Two minutes in, there is nothing specific to ask about.)

"I finished my B.Com this year and have spent the last
few months focused on data analysis, mainly SQL and
Power BI.

My main project looked at a retail sales dataset to
find why one category had high sales but low profit.
It turned out heavy discounts on a few products were
driving most of the losses.

I am applying here because your role involves sales
reporting, which is exactly the kind of work I enjoy.
Happy to go into the project or anything else."

-- Monthly active users: users with at least one
-- qualifying event in the month (PostgreSQL)
SELECT
  DATE_TRUNC('month', event_time) AS month,
  COUNT(DISTINCT user_id)         AS monthly_active_users
FROM events
WHERE event_type IN ('login', 'purchase', 'search')
  AND user_id IS NOT NULL
GROUP BY DATE_TRUNC('month', event_time)
ORDER BY month;

-- Sanity check for one month
SELECT COUNT(*) AS users_in_oct
FROM (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_type IN ('login', 'purchase', 'search')
    AND event_time >= '2026-10-01'
    AND event_time <  '2026-11-01'
) t;

Interviewer: Write a query for monthly active users.

Candidate: Before I write it, what counts as active?
Any event, or only meaningful ones like login or
purchase? Background events such as app pings could
inflate the number.

I will assume login, purchase or search. I will count
DISTINCT users per calendar month, because one user
has many events. I will also exclude NULL user IDs
from logged-out sessions.

After writing it, I would recount one month with a
simple subquery to make sure the numbers match.

Interviewer: Why COUNT(DISTINCT user_id) and not COUNT(*)?

Candidate: Because the events table has one row per
event, not per user. A user who logs in ten times in a
month would be counted ten times with COUNT(*). MAU is
about people, so I need each user once per month.

Interviewer: What about time zones?

Candidate: If event_time is stored in UTC, users in India
who are active just after midnight IST could fall into the
previous day in UTC, and at month boundaries that shifts
them into the wrong month. I would convert to the business
time zone before truncating, for example
  DATE_TRUNC('month', event_time AT TIME ZONE 'Asia/Kolkata')
if the column is a timestamp with time zone.

Candidate (two minutes later): Sorry, I want to correct
something in my query. I filtered event_type but did not
exclude rows where user_id is NULL. Logged-out events
would then form one extra 'user' only if NULLs were
counted, and COUNT(DISTINCT) actually ignores NULLs, so
the count is fine. But I would still filter them out to
make the intent clear to anyone reading the query.

(The candidate reasons through it openly. Even when the
fix turns out to be unnecessary, the habit is visible.)

MY ONE-MINUTE INTRODUCTION

Who I am now (1 line): _________________________
What I focus on (2 tools + what I do): __________
One project + specific finding: ________________
Why this role (link to JD): ____________________
Hand-back line: "Happy to go into ____ or anything else."

Time it. Over 75 seconds? Cut the skills list first.

COMMON FIRST TASKS TO PRACTISE COLD

[ ] Monthly active users
[ ] Total sales per city with HAVING filter
[ ] Top N per category with a window function
[ ] Customers with no orders (LEFT JOIN + IS NULL)
[ ] Month-over-month change with LAG

For each: clarify -> write -> sanity-check -> explain.

FOR EACH MAIN ANSWER, PREPARE:

Why did I use this function/approach?
__________________________________
What edge case could break it?
__________________________________
How would I check the result?
__________________________________
What would change with 10x more data?
__________________________________