10 JD.com SQL Interview Questions (Updated 2024)

At Jingdong Mall (JD.com), SQL is used across the company for analyzing customer purchase patterns and optimizing inventory management based on real-time sales data. That's why JD.com frequently asks SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.

As such, to help you ace the JD.com SQL interview, we've collected 10 Jingdong Mall SQL interview questions – how many can you solve?

10 Jingdong Mall (JD.com) SQL Interview Questions

SQL Question 1: Calculate Average Monthly Reviews Ratings for each Product

JD.com is a huge e-commerce platform that has many products. Each product is reviewed by numerous users. The feedback from these reviews is very crucial to the growth of their product line. As a data analyst, suppose you are asked to provide analysis on JD.com's review data. Write a SQL query to calculate the average star ratings for each product on a monthly basis.

Consider the below table:

Example Input:

Your output should have columns: month, product_id, and avg_stars.

Example Output:

Answer:

You can make use of the SQL's to find out the month from the date, and function to calculate the average star rating.

This query aggregates the reviews per product per month and calculates the average star ratings for each of these groupings. The clause is used to order the result set first by month (in ascending order), and then by average star ratings in descending order.

To practice a similar window function question on DataLemur's free online SQL code editor, try this Google SQL Interview Question:

SQL Question 2: Filter Customer Purchases Based on Conditions for JD.com

JD.com wants to filter out customer records for their Purchase Records, focusing on customers who made purchases more than three times during the year 2020 and the total value of purchases for each customer is over 10000 RMB.

In the Purchase Records table, every row indicates a purchase made by a customer, with details including CustomerID, PurchaseID, Date (in format YYYY-MM-DD), and Value (of each purchase). Can you help JD.com filter out these specific customer records?

Example Input:

The output should return the CustomerID, the total amount spent by the customer in 2020, and the total amount of purchases the customer made in 2020.

Example output:

Answer:

This PostgreSQL query uses GROUP BY to group the data by CustomerID and uses SUM and COUNT functions to find out the total value of the purchases and the count of purchases. The HAVING clause checks if a customer made more than three purchases and the total value of the purchases is over 10000 RMB during 2020.

SQL Question 3: How does differ from ?

Both and are used to combine the results of two or more SELECT statements into a single result set.

However, only includes one instance of a duplicate, whereas includes duplicates.

SQL Question 4: Calculating Click-Through Conversion Rates for JD.com

Assume that JD.com wants to analyze their digital marketing campaign and look for ways to improve their sales. One method they are using is by studying their click-through conversion rates from viewing a product to adding a product to the cart.

Here's some sample data:

:

:

We can write a query that calculates the click-through conversion rates for each product based on the views and add to cart events.

Answer:

In this query, for each product ID, we count the number of add to cart events and divide it by the number of views. We then multiply it by 100 to get the conversion rate as a percentage. The conversion rate is calculated using a left join on the view logs and add to cart logs. This helps us capture the entire universe of views even when they did not translate into an add-to-cart event.

Please note that the conversion rate is being calculated for each product. And since a user may add the product to their cart on the same day as the view event or on any subsequent day, we ensure that we're only counting those cases by adding the condition in the join clause.

Given the sample input data, this query will return the conversion rate for each product. Keep in mind that if the input data had timestamps (not just dates), the query would need modifications to account for that.

To solve a similar problem about calculating rates, try this TikTok SQL Interview Question within DataLemur's online SQL coding environment:

SQL Question 5: In SQL, what's the primary difference between the 'BETWEEN' and 'IN' operators?

is used to select rows that match a range of values, whereas the operator checks for values in a specified list of values.

For example, say you were a Data Analyst at JD.com and had a table of advertising campaign data.

To find campaigns with between $500 and$10k in spend, you could use BETWEEN`:

To find ad campaigns that were run on Facebook and Google's Display Network, you could use :

SQL Question 6: Find Customers with Particular Email Domain

JD.com wants to conduct marketing campaigns tailored to the users using different email services. For the campaign, they wish to filter out the customer details who are using the email service 'hotmail.com'. Using the SQL keyword , create a query that will return the list of all customers who have registered their email with the domain 'hotmail.com'.

Example Input:

Example Output:

Answer:

This query uses the LIKE keyword to filter the customers table for records where the email field ends with '@hotmail.com'. The '%' symbol is a wildcard that matches any sequence of characters, thus, the condition '%@hotmail.com' matches any email that ends with '@hotmail.com', effectively selecting all customers who use Hotmail as their email service.

SQL Question 7: How can you select records without duplicates from a table?

"The clause in SQL allows you to select records that are unique, eliminating duplicates.

For a tangible example, say you had a table of JD.com employees:

If you were doing an HR Analytics project and you wanted to get all the unique job titles that currently worked at the company, you would write the following SQL query:

The output would give you 3 distinct job titles at JD.com:

SQL Question 8: Analyzing Customer Purchase and Feedback

JD.com is interested in knowing which products are most purchased by which customer, and how the customer's reviews are affecting the average rating of these products. They would like to have a user-product table joined with their reviews table.

So, the question is: "Can you write a SQL query to identify the most purchased product by each user and calculate the average star rating of that product by looking at all the reviews?"

Example Input:

Example Input:

Example Output:

Answer:

In this query, first, we create a CTE that aggregates the count of each product bought by a user. Then we select the one which was purchased the most by each user. Next, we calculate the average stars for each product in the table. Finally, we join these two tables using the to get the most purchased product by each user and its average stars.

Because joins come up so often during SQL interviews, try an interactive SQL join question from Spotify:

SQL Question 9: Calculate Discounted Price and Sale Identity

JD.com is in the process of conducting a sale event where the products in the "books" category having a review score of 4 and above will be sold at a 20% discount. For each product in the "books" category, calculate the discounted price, rounded to the nearest integer. Also, identify if a product is featured in the sale (1 = sale, 0 = non-sale) based on the average review score. Create a column "sale_identity" to reflect this.

Example Input:

Example Input:

Example Output:

Answer:

This query first performs a group by operation on the reviews table to compute the average rating for each product. If the average rating is 4 or above, a discounted rate of 20% is applied. This information is used to compute and fields. The query then joins the products table with this result, based on product_id, to calculate the rounded price and provide the sale identity for each product in the 'books' category.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages based on product reviews or this McKinsey 3-Topping Pizzas Question which is similar for discount pricing calculation.

SQL Question 10: What's the purpose of a foreign key?

A foreign key is a column or group of columns in a table that refers to the primary key in another table. The foreign key constraint helps maintain referential integrity between the two tables. The table with the foreign key is called the child table, while the table with the candidate key is called the parent or referenced table.

For example, consider a database with two tables: and customer_idcustomer_idcustomer_id` column in JD.com customers table.

JD.com SQL Interview Tips

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the JD.com SQL interview is to solve as many practice SQL interview questions as you can! Beyond just solving the above JD.com SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.

Each problem on DataLemur has hints to guide you, full answers and most importantly, there is an online SQL coding environment so you can right online code up your SQL query answer and have it executed.

To prep for the JD.com SQL interview you can also be wise to practice SQL problems from other tech companies like:

• Alibaba SQL questions

• Pinduoduo SQL questions

• Amazon SQL questions

• eBay SQL questions

But if your SQL foundations are weak, don't worry about going right into solving questions – refresh your SQL knowledge with this DataLemur SQL tutorial.

This tutorial covers SQL concepts such as using ORDER BY and creating pairs via SELF-JOINs – both of which show up frequently in JD.com interviews.

Jingdong Mall Data Science Interview Tips

What Do JD.com Data Science Interviews Cover?

In addition to SQL interview questions, the other types of questions to prepare for the JD.com Data Science Interview are:

• Probability & Stats Questions
• Python Pandas or R Coding Questions
• Product-Sense and Business Case Questions
• ML Interview Questions
• Behavioral & Resume-Based Questions

How To Prepare for JD.com Data Science Interviews?

To prepare for JD.com Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from tech companies like Netflix, Google, & Airbnb
• a crash course on SQL, Product-Sense & ML
• over 900+ reviews on Amazon & 4.5-star rating