logo

10 JD.com SQL Interview Questions (Updated 2024)

Updated on

February 14, 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:

review_iduser_idsubmit_dateproduct_idstars
61711232022-06-08500014
78022652022-06-10698524
52933622022-06-18500013
63521922022-07-26698523
45179812022-07-05698522

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

Example Output:

monthproduct_idavg_stars
6500013.50
6698524.00
7698522.50

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: 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:
PurchaseIDCustomerIDDateValue
113112020-05-203000
248212020-04-122000
345412021-03-301000
448212020-09-185000
548212020-12-294000
613112020-08-212500
713412020-09-036200
813412020-12-251500
913112020-01-215000
1045412020-02-134500

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:

CustomerIDTotal AmountCount of Purchase
1311105003
4821110003

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.

Jingdong Mall SQL Interview Questions

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:

:

view_iduser_idview_dateproduct_id
1012552022-07-011001
1022342022-07-011002
1032342022-07-021001
1049702022-07-021002
1059702022-07-031001

:

cart_iduser_idcart_dateproduct_id
5012552022-07-011001
5022342022-07-011002

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 interview question from TikTok

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 500and500 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:

customer_idnameemail
1001John Doejohndoe@gmail.com
1002Jane Smithjanesmith@hotmail.com
1003Peter Parkerpeter_parker@yahoo.com
1004Mary Janemary.j@hotmail.com
1005Tony Starktony.stark@hotmail.com

Example Output:

customer_idnameemail
1002Jane Smithjanesmith@hotmail.com
1004Mary Janemary.j@hotmail.com
1005Tony Starktony.stark@hotmail.com

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:

first_namejob_title
AkashData Analyst
BrittanyData Scientist
CarlosData Engineer
DiegoData Engineer
EvaData Analyst

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:

job_title
Data Analyst
Data Scientist
Data Engineer

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:
purchase_iduser_idproduct_idpurchase_date
11235000106/01/2022
21235000106/05/2022
32656985206/02/2022
43625000106/15/2022
51926985207/01/2022
Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022 00:00:00500014
780226506/10/2022 00:00:00698524
529336206/18/2022 00:00:00500013
635219207/26/2022 00:00:00698523
451798107/05/2022 00:00:00698522
Example Output:
user_idproduct_idavg_stars
123500014.00
265698524.00
362500013.00

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 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:
product_idcategoryprice
50001"books"185
69852"electronics"300
33421"books"120
85692"books"200
99531"books"150
Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022500014
780226506/10/2022698524
851317206/12/2022334215
529312806/18/2022500013
635233407/26/2022856924
456947308/10/2022995315
900238608/11/2022500015
Example Output:
productrounded_pricesale_identity
500011481
33421961
856921600
995311201

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. DataLemur Question Bank

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:

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.

SQL interview 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:

JD.com Data Scientist

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

Ace the Data Science Interview by Nick Singh Kevin Huo