Data Science interview question · SQL · senior level
What share of first-time buyers make a second purchase within 30 days? Write the SQL.
Free to read in full. This is 1 of the 150 questions in the Data Science interview pack, with 191 diagrams. Get the pack, ₹999 · $20
Why it is asked
Repeat rate is a core retention metric for any commerce app. The interviewer checks that you count users rather than orders, measure the window from the first purchase, and notice that recent buyers have not had 30 days to come back.
The answer
Find each user's first order and the date of their next order, then take the share of users whose next order falls within 30 days of the first; include only users whose first order is at least 30 days before the data ends, because the others have not had a full 30 days to come back.
Steps (DuckDB query in the code below):
- One row per user: ROW_NUMBER() over each user's orders by date marks the first order (rn = 1), and LEAD(order_date) (the next row's value) on the same window gives the next order's date.
- The flag: next_date <= first date + 30. A user with no next order has a NULL next_date, which the CASE turns into 0.
- The denominator is users, not orders: COUNT(*) over the rn = 1 rows.
- Right-censoring means we stopped watching before the outcome window finished. The data ends on June 30, so only users whose first order is on or before May 31 are counted.
On 4,000 simulated users with first orders from January to June 2025, the query keeps 3,309 users and gives 35.3%. Without the date filter it gives 32.9%, because 691 June buyers had less than 30 days to return; the June cohort alone shows 21.3%, against 34% to 37% for the other months. The true rate in the simulation is 35.2%.
Drop late users whatever they did, including those who already came back. Keeping only the late users who returned would push the rate up instead. To track the metric over time, report it per first-order month (a cohort) and mark the newest month as incomplete.
What the interviewer listens for
- One row per user: first order (ROW_NUMBER = 1) plus the next order date (LEAD or a self-join).
- The 30-day window starts at each user's first order.
- Count users, not orders.
- Right-censoring: drop users whose first order is within 30 days of the data end (35.3% vs 32.9% here).
Follow-ups the interviewer may ask
- Two orders placed 5 minutes apart: is that a repeat? Hint: decide on a rule, such as a second order on a later day or a gap of more than an hour, and write it into the metric definition.
- How would you use the users you dropped? Hint: a survival curve (Kaplan-Meier) uses each user's partial follow-up instead of discarding it.
- How do you show the trend? Hint: the same query grouped by first-order month, with the last month flagged as incomplete.
Common mistakes
- Dividing by orders instead of first-time buyers.
- Including buyers whose 30-day window has not finished, which drags the rate down.
- Excluding only the late buyers who did not return, which pushes it up.
Written by Roni Das, who builds distributed systems and AI systems for a living.
One question is not enough for the interview.
This is 1 of the 150 questions in the Data Science pack
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (191 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: PayPal. Download at once from My packs.
More free questions from this pack
- You need to show delivery times for 20 cities. Would you use histograms, box plots, violin plots or ECDFs, and why? (junior)
- A test is 99% accurate and you test positive. What is the chance you have the disease? (mid)
- How would you forecast daily orders for the next four weeks, and how do you know the forecast is any good? (senior)
- A coupon A/B test shows a small average lift. How do you find which users the coupon actually works for, and who should get it next time? (staff)
Free questions from the other packs
- What do temperature and top-p control, and what values would you use for data extraction versus brainstorming? (AI Engineering)
- What is hybrid search, and how do you merge keyword and vector results with Reciprocal Rank Fusion? (AI Engineering)
- Users ask "list all X" questions and RAG returns only five items. Why, and what do you do? (AI Engineering)
- How do you check that an LLM judge agrees with human reviewers? (AI Engineering)
- Cut the cost of an LLM feature serving 10 million requests a day by 60% without hurting quality. Walk through your plan. (AI Engineering)
- Why are LLMs bad at counting letters or reversing a word? (GenAI and LLM)
- Write out scaled dot-product attention with the shape of every tensor. (GenAI and LLM)
- How does speculative sampling keep exactly the target model's distribution? (GenAI and LLM)
- What is GRPO, and how do verifiable rewards train reasoning models? (GenAI and LLM)
- Design a system that answers questions from 300-page contracts accurately. (GenAI and LLM)
- Describe the basic agent loop. (Agentic AI)
- What are pass@k and pass^k, and why do they matter for agents? (Agentic AI)
- How does prompt injection become dangerous once the agent has tools? (Agentic AI)
- Design the security architecture for an agent that can read and send company email. (Agentic AI)
- Design a customer support agent for an e-commerce company. (Agentic AI)
- What happens, step by step, when an LLM server handles one request? (Inference Engineering)
- Derive the KV cache size formula and work it out for Llama 3 70B. (Inference Engineering)
- What is PagedAttention? (Inference Engineering)
- When does speculative decoding stop helping or make things worse? (Inference Engineering)
- Design a service to serve Llama 3 70B at 10 QPS with latency targets. (Inference Engineering)
- A model scores 95% accuracy offline but users say it is useless. What do you check first? (ML Engineering)
- How do you train on a dataset that does not fit in memory? (ML Engineering)
- A long distributed training job dies at hour 20. How do you make training fault tolerant? (ML Engineering)
- How does request batching on a GPU trade latency for throughput? (ML Engineering)
- An online model metric dropped 8% overnight. How do you debug it? (ML Engineering)
- Compute the cosine similarity of a = (3, 4) and b = (4, 3) by hand. What does the result mean? (Maths for ML)
- A batch of 32 MNIST images goes into a dense layer with 128 units. What are the shapes, and what is the rule for when two matrices can be multiplied? (Maths for ML)
- A disease affects 1% of people. A test catches 90% of sick people (sensitivity) and wrongly flags 9% of healthy people (false positive rate). You test positive. What is the chance you are sick? (Maths for ML)
- Why do we not use mean squared error for classification with a sigmoid output? Compare the gradients numerically. (Maths for ML)
- Compute scaled dot-product attention by hand for two tokens with d_k = 2. (Maths for ML)
- A customer says: "We want AI for our support team." What do you do in the first meeting? (Forward Deployed Engineer)
- The customer's admin offers you a global admin account "to save time". What do you ask for instead, and why? (Forward Deployed Engineer)
- Your connector writes tickets into the customer's system. A request timed out, the connector retried, and now there are duplicate tickets. How do you make the writes safe to retry? (Forward Deployed Engineer)
- The customer wants RAG over 10 million documents, all behind their firewall. Size it for me on the whiteboard. (Forward Deployed Engineer)
- One deployment of your AI platform must serve 50 business units of the same enterprise. How do you design multi-tenancy? (Forward Deployed Engineer)