JD Data Analyst Interview Experience Sharing: SQL, Python, and Business Insights Full Assessment

Background

Let me start with my background. I graduated with a bachelor's degree in Statistics and have been working as a data analyst at a mid-sized e-commerce company for 2 years. My daily work mainly involves writing SQL queries, building reports, and occasionally using Python for automation scripts and simple data modeling. Honestly, the job at my current company is stable, but I always felt limited in terms of growth — the data infrastructure isn't great, and I spend too much time on repetitive data extraction tasks rather than doing deep analysis.

In mid-March this year, I came across a JD Retail - Data Analyst position on JD's career website. The role required up to 3 years of experience, and the job description focused on user behavior analysis, campaign performance evaluation, and metrics system development — all of which aligned well with my current work direction. After hesitating for a couple of days, I decided to apply. After all, JD's data scale and analytical depth would be on a completely different level from where I am now.

About a week after applying, HR called to schedule the interview. The overall process was Technical Round 1 → Technical Round 2 → Business + HR Round, spanning about two weeks. Below is my detailed recap of each round, hoping it helps those preparing for JD data analyst interviews.

Round 1: Technical Interview 1 (Video Call, ~55 minutes)

The first-round interviewer was a guy who looked around 30. He started with a self-introduction and then jumped straight into technical questions. The pace was quite tight with a high density of questions.

1. SQL: Top 3 Products by Sales in Each Category

The interviewer gave me an order_detail table with fields: order_id, product_id, category_id, sale_amount, order_date. I needed to find the top 3 products by total sales in each category.

I was familiar with this type of question, so I used window functions directly:

My Answer:

SELECT category_id, product_id, total_sale
FROM (
  SELECT category_id, product_id, SUM(sale_amount) AS total_sale,
        ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY SUM(sale_amount) DESC) AS rn
  FROM order_detail
  GROUP BY category_id, product_id
) t
WHERE rn <= 3;

The interviewer followed up: What's the difference between RANK() and ROW_NUMBER()? I explained the tied ranking scenario — RANK() skips numbers after ties, while DENSE_RANK() doesn't skip. He nodded and moved on.

2. SQL: Users Who Logged In for 3 Consecutive Days

Given a user_login table with fields user_id and login_date, find users who logged in for 3 or more consecutive days.

I had practiced similar questions before, using the ROW_NUMBER() date difference method:

My Answer:

SELECT user_id
FROM (
  SELECT user_id, login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM (SELECT DISTINCT user_id, login_date FROM user_login) t1
) t2
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

The interviewer said the approach was correct but asked about an edge case: What if there are multiple login records on the same day? I said I handled it with DISTINCT in the subquery. He confirmed that was right.

3. SQL: Retention Rate Calculation

Given a user registration table and a login table, calculate Day-1 and Day-7 retention rates.

My Answer: Use LEFT JOIN, calculate the difference between registration date and login date, filter for differences of 1 and 7 respectively, then divide by the number of registrations on that day. The interviewer asked me to write the complete SQL, which took about 5 minutes. He checked and confirmed the logic was correct.

4. Python: Pandas Data Cleaning

The interviewer asked: If you have a DataFrame where the age column has missing values and outliers (like negative numbers and values over 150), how would you clean it?

My Answer: First, use df['age'].isnull().sum() to check the number of missing values. If the missing ratio is small, fill with median: df['age'].fillna(df['age'].median()). For outliers, use df.loc[df['age'] < 0 | (df['age'] > 150), 'age'] = np.nan to set them to NaN first, then fill. The interviewer followed up: What if the missing ratio is very large? I said we might need to consider whether the feature is still meaningful, or use model-based imputation, but in practice, I'd confirm with the business team first.

5. Python: Groupby and Aggregation Operations

Question: How would you use pandas to group by user and calculate each user's order count, average order amount, and maximum order amount?

My Answer: df.groupby('user_id')['order_amount'].agg(['count', 'mean', 'max']). The interviewer said OK, then asked how to customize column names. I answered: .agg(order_count=('order_amount', 'count'), avg_amount=('order_amount', 'mean'), max_amount=('order_amount', 'max')). He was satisfied.

6. Statistics: P-value and Hypothesis Testing

The interviewer asked: What does the p-value mean? How do you interpret the 0.05 significance level?

My Answer: The p-value is the probability of observing the current or more extreme results, assuming the null hypothesis is true. The 0.05 significance level means that if the null hypothesis is true, we have a 5% chance of incorrectly rejecting it (i.e., Type I error rate controlled at 5%). The interviewer followed up: Does a small p-value necessarily mean the effect is significant? I said not necessarily — we also need to consider effect size and sample size. With large samples, even tiny differences can produce significant p-values, but the practical significance might be negligible. He nodded in agreement.

7. Statistics: Law of Large Numbers vs. Central Limit Theorem

Question: Briefly explain the difference between the Law of Large Numbers and the Central Limit Theorem.

My Answer: The Law of Large Numbers states that as the sample size increases, the sample mean converges to the population expectation. The Central Limit Theorem states that as the sample size increases, the distribution of sample means approaches a normal distribution, regardless of the population distribution. The former focuses on "convergence to the true value," while the latter focuses on "distribution shape." The interviewer said the explanation was very clear.

Round 2: Technical Interview 2 (Video Call, ~60 minutes)

The second-round interviewer was a more senior female interviewer, likely a team lead. The overall style leaned more toward business scenarios, and technical questions were more closely tied to business contexts.

1. SQL: User Funnel Analysis

Given a user_action table with fields user_id, action_type (browse/add-to-cart/order/pay), and action_time, calculate the conversion rate at each step from browsing to payment.

My Answer: First, use CASE WHEN to check whether each user completed each step, then aggregate to calculate conversion rates. I wrote this in about 7-8 minutes. The interviewer reviewed it and said the approach was correct, but reminded me about deduplication — the same user might browse multiple times, so we should check whether they completed an action, not count occurrences. I admitted this was easy to overlook and revised it. She approved.

2. A/B Testing: Designing an Experiment for Coupon Effectiveness

The interviewer gave a scenario: JD wants to test the impact of a "$30 off $200" coupon on GMV. How would you design the experiment?

My Answer: First, determine the experiment metrics — primary metric is per-user GMV, secondary metrics are average order value and order conversion rate. Then determine the traffic splitting strategy — random split by user ID, with the treatment group receiving coupons and the control group not. Calculate sample size using power analysis to ensure we can detect a 5% lift. The experiment should run for at least 7 days to cover a full weekly cycle. Finally, use a t-test or Mann-Whitney U test to compare the two groups.

The interviewer followed up with two questions: First, how to handle social spillover effects between users? I said we could consider cluster-based randomization by social circles, or use time-switchback experiments. Second, what if a major promotional event occurs during the experiment? I said either avoid the promotion period or include it as a stratification factor. She said the answers were quite comprehensive.

3. A/B Testing: Simpson's Paradox

Question: What is Simpson's Paradox? How do you avoid it in A/B testing?

My Answer: Simpson's Paradox occurs when a trend appears in different groups of data but disappears or reverses when the groups are combined. For example, the treatment group might perform better on both iOS and Android separately, but worse overall when combined — possibly due to disproportionate iOS user ratios between groups. To avoid this, we should do stratified analysis or use methods like CUPED to control for confounding variables. The interviewer followed up on CUPED's principle — I could only give a rough explanation about using pre-experiment covariates to reduce variance, but I couldn't remember the exact formula. This was one of the questions I didn't answer well in Round 2. The interviewer said it was fine and that understanding the concept was enough.

4. Business Analysis: JD 618 Shopping Festival Post-Mortem

The interviewer asked: If you were doing a data post-mortem for JD's 618 shopping festival, what dimensions would you analyze?

My Answer: I would approach it from four dimensions: First, overall performance — GMV, order volume, and average order value changes year-over-year and period-over-period. Second, traffic analysis — UV, PV, channel acquisition effectiveness, and bounce rate. Third, conversion funnel — conversion rates at each step from browsing to ordering, identifying steps with high drop-off. Fourth, user segmentation — contribution ratios of new vs. existing users, behavior characteristics of high-value users, and reactivation effectiveness for dormant users. The interviewer followed up: If GMV grew year-over-year but profit margin decreased, how would you analyze that? I said we'd need to break down the cost structure — whether it's due to excessive subsidies or an increased share of low-margin categories — and then do cross-analysis with category and user dimensions. She found this approach reasonable.

5. Metrics System: Building a Category Operations Metrics System

Question: If you were responsible for the data of a JD product category, how would you build an operations metrics system?

My Answer: I would use the OSM (Objective-Strategy-Measurement) model. First, define the Objective — for example, category GMV growth. Then decompose the Strategy — GMV = Traffic × Conversion Rate × Average Order Value, and further break down each factor. Finally, define Measurements — traffic looks at UV/PV, conversion rate at each step's conversion, and average order value at per-user spending. Also, distinguish between North Star metrics, process metrics, and monitoring metrics to form a dashboard. The interviewer asked: What if there are conflicts between metrics? I said we need to look at priorities — for example, when short-term GMV conflicts with long-term user satisfaction, we need to align with the business team on strategic direction. She nodded.

6. SQL: Year-over-Year and Period-over-Period Growth Calculation

Given a monthly sales summary table with fields month, category_id, and gmv, calculate the YoY and PoP growth rates for each category each month.

My Answer: For PoP, use LAG(gmv, 1) OVER(PARTITION BY category_id ORDER BY month); for YoY, use LAG(gmv, 12) OVER(PARTITION BY category_id ORDER BY month), then calculate the growth rates. The interviewer said that was correct and asked: What if some months have no data, causing LAG to return NULL? I said use COALESCE to handle it, or ensure the data has a continuous month sequence.

Round 3: Business + HR Interview (~45 minutes)

The third round was conducted jointly by the business lead and HR — about 30 minutes for the business portion and 15 minutes for HR.

Business Interview Portion

The business interview focused mainly on project experience and business understanding. The interviewer asked me to walk through my most accomplished data analysis project. I described a user churn prediction project I had worked on — using the RFM model to segment users, discovering that 12% of high-value users hadn't repurchased in the last 30 days, then working with the operations team on targeted reactivation, ultimately achieving an 18% reactivation rate and contributing approximately 800K RMB in GMV.

The interviewer followed up on several points: How did I determine the thresholds for each RFM dimension? I said I used K-means clustering to identify the cut-off points. What was the reactivation strategy? SMS + push notifications + exclusive coupons. How did I evaluate the results? I used a control group for comparison. He said the overall logic was clear but asked how I would improve it if I did it again. I said I would try using machine learning models instead of RFM rules, incorporating more behavioral features like browsing frequency and search keywords.

There was also an open-ended question: JD PLUS membership renewal rate has dropped — how would you analyze this? I said I'd first break it down — which type of users saw the biggest decline? New members or long-term members? Then analyze possible causes: declining benefit attractiveness? Competitor membership poaching? Price sensitivity? Then do targeted data verification. The interviewer seemed satisfied.

HR Interview Portion

The HR portion was fairly standard:

1. Why do you want to join JD? — Large platform, massive data scale, great growth opportunities
2. Current salary and expectations? — Answered honestly
3. Career plans? — Become a senior analyst capable of independently managing data analysis for a business line within 3 years
4. Anything you'd like to know? — Asked about team size and business direction

HR said they'd give results within about a week.

Interview Questions Summary

Here's a summary of all questions from the three rounds for quick reference:

Technical Round 1:

1. SQL: Top 3 products by sales per category → Window functions ROW_NUMBER/RANK → ⭐⭐
2. SQL: Users with 3+ consecutive login days → Date difference method → ⭐⭐⭐
3. SQL: Day-1/Day-7 retention rate calculation → LEFT JOIN + date difference → ⭐⭐⭐
4. Python: Pandas missing values and outlier cleaning → fillna + conditional replacement → ⭐⭐
5. Python: Groupby multi-aggregation operations → agg function → ⭐⭐
6. Statistics: P-value meaning and significance level → Hypothesis testing basics → ⭐⭐
7. Statistics: Law of Large Numbers vs. Central Limit Theorem → Probability theory basics → ⭐⭐

Technical Round 2:

1. SQL: User behavior funnel analysis → CASE WHEN + deduplication → ⭐⭐⭐
2. A/B Testing: Coupon effectiveness experiment design → Full experiment design process → ⭐⭐⭐⭐
3. A/B Testing: Simpson's Paradox and avoidance → Stratified analysis/CUPED → ⭐⭐⭐⭐
4. Business Analysis: 618 shopping festival post-mortem dimensions → OSM/decomposition thinking → ⭐⭐⭐
5. Metrics System: Category operations metrics framework → OSM model → ⭐⭐⭐⭐
6. SQL: YoY and PoP growth calculation → LAG window function → ⭐⭐⭐

Business + HR Round:

1. Project deep dive: User churn prediction project → Project review capability → ⭐⭐⭐
2. Open question: PLUS membership renewal rate decline analysis → Business decomposition thinking → ⭐⭐⭐⭐
3. Behavioral interview: Why JD / Career plans → Motivation and planning → ⭐⭐

Key Takeaways and Advice

1. SQL is fundamental — window functions are a must
JD's data analyst interviews heavily test SQL, and window functions are almost guaranteed to appear. ROW_NUMBER, RANK, and LAG should become muscle memory. I recommend practicing 2-3 LeetCode SQL problems daily to stay sharp. Also, details like deduplication and NULL handling are easily overlooked — make sure to proactively consider edge cases during interviews.

2. A/B testing is a major differentiator — understand principles, not just concepts
For the A/B testing design question in Round 2, just reciting "random split, hypothesis testing" is far from enough. The interviewer will dig into traffic splitting strategies, sample size calculations, and confounding variable control. I recommend systematically studying causal inference and experimental design. Understanding advanced methods like CUPED and difference-in-differences will give you a significant edge.

3. Business thinking matters more than technical depth
Starting from Round 2, interviewers care more about whether you can apply techniques to business scenarios. For example, the 618 post-mortem question wasn't technically difficult — the challenge was whether you could systematically decompose the problem and propose insightful analytical dimensions. I recommend reading more business analysis reports to develop structured thinking.

4. Don't panic on questions you can't answer — show your thought process
I didn't answer the CUPED question perfectly in Round 2, but I first admitted I couldn't remember the exact formula, then explained my understanding of the approach and use cases. The interviewer later said they care more about how candidates think when facing unfamiliar problems than about memorized formulas. So when you encounter something you don't know, stay calm, analyze it, and explain your reasoning — it's much better than just saying "I don't know."

Finally, the result: 6 days after the third round, HR called — I got the offer! 🎉 Overall, JD's interview process was very professional, the interviewers were highly skilled, and the questions were well-targeted. Best of luck to everyone preparing for interviews!

FAQ

Q1: What are the educational requirements for JD's data analyst position?
A: For experienced hires, a bachelor's degree or above is sufficient, with more emphasis on practical project experience. I graduated from a non-prestigious university and still passed the resume screening, so education isn't a hard barrier. However, degrees in Statistics, Mathematics, or Computer Science are advantageous.

Q2: How difficult are the SQL questions in the interview?
A: Generally medium-to-above-medium difficulty. Window functions are the core topic, and JOINs and subqueries are also tested. They won't ask about complex stored procedures or performance optimization, but the logic must be correct. Recommended practice platforms: LeetCode SQL, Nowcoder SQL specialization.

Q3: How deep is the Python testing? Do I need to know machine learning?
A: Python is mainly tested on pandas data processing — they won't test algorithm implementation. Machine learning isn't a hard requirement, but if your projects use it, the interviewer may follow up. I recommend mastering pandas operations like groupby, merge, and apply.

Q4: How important is A/B testing in the interview?
A: Very important, especially in Round 2. As an e-commerce company, A/B testing is the core methodology for JD's daily work. I recommend focusing on: experiment design process, sample size calculation, traffic splitting strategies, Simpson's Paradox, and CUPED.

Q5: How long does it take from application to offer?
A: My entire process took about 3 weeks: 1 week after application for Round 1, 3 days after Round 1 for Round 2, 4 days after Round 2 for Round 3, and 6 days after Round 3 for the result. Timelines may vary by department — this is just for reference.