logo

8 DoorDash SQL Interview Questions (Updated 2024)

Updated on

January 25, 2024

Doordash's experimentation platform Curie relies on Data Analysts and Data Scientists to write ad-hoc SQL queries to analyze A/B testing data. That's why DoorDash LOVES to ask SQL problems during interviews for Data Science, Data Engineering and Data Analytics jobs.

So, if you're studying for a SQL Assessment, we've curated 8 DoorDash SQL interview questions to practice, which are similar to recently asked questions at DoorDash – how many can you solve?

8 DoorDash SQL Interview Questions

SQL Question 1: First 14-Day Satisfaction

The Growth Team at DoorDash wants to ensure that new users, who make orders within their first 14 days on the platform, have a positive experience. However, they have noticed several issues with deliveries that result in a bad experience.

These issues include:

  • Orders being completed incorrectly, with missing items or wrong orders.
  • Orders not being received due to incorrect addresses or drop-off spots.
  • Orders being delivered late, with the actual delivery time being 30 minutes later than the order placement time. Note that the is automatically set to 30 minutes after the .

Write a query that calculates the bad experience rate for new users who signed up in June 2022 during their first 14 days on the platform. The output should include the percentage of bad experiences, rounded to 2 decimal places.

Table:
Column NameType
order_idinteger
customer_idinteger
trip_idinteger
statusstring ('completed successfully', 'completed incorrectly', 'never received')
order_timestamptimestamp
Example Input:
order_idcustomer_idtrip_idstatusorder_timestamp
7274248472100463completed successfully06/05/2022 09:12:00
2425132341100482completed incorrectly06/05/2022 14:40:00
1413671314100362completed incorrectly06/07/2022 15:03:00
5821935421100657never_received07/07/2022 15:22:00
2536131314100213completed successfully06/12/2022 13:43:00
Table:
Column NameType
dasher_idinteger
trip_idinteger
estimated_delivery_timestamptimestamp
actual_delivery_timestamptimestamp
Example Input:
dasher_idtrip_idestimated_delivery_timestampactual_delivery_timestamp
10110046306/05/2022 09:42:0006/05/2022 09:38:00
10210048206/05/2022 15:10:0006/05/2022 15:46:00
10110036206/07/2022 15:33:0006/07/2022 16:45:00
10210065707/07/2022 15:52:00-
10310021306/12/2022 14:13:0006/12/2022 14:10:00
Table:
Column NameType
customer_idinteger
signup_timestamptimestamp
Example Input:
customer_idsignup_timestamp
847205/30/2022 00:00:00
234106/01/2022 00:00:00
131406/03/2022 00:00:00
143506/05/2022 00:00:00
542106/07/2022 00:00:00
Example Output:
bad_experience_pct
75.00
Explanation:

Order 727424 is excluded from the analysis as it was placed after the first 14 days upon signing up.

Out of the remaining orders, there are a total of 4 orders. However, only 3 of these orders resulted in a bad experience, as one order with ID 253613 was completed successfully. Therefore, the bad experience rate is 75% (3 out of 4 orders).

Answer:


For a full step-by-step explanation, and to solve this problem interactively in the browser, subscribe to DataLemur Premium to unlock this Doordash SQL question:

Doordash SQL Question

SQL Question 2: Analyze DoorDash Delivery Performance

As a Data Analyst at DoorDash, you're tasked to analyze the delivery performance of the drivers. Specifically, you are asked to compute the average delivery duration of each driver for each day, the rank of each driver's daily average delivery duration, and the overall average delivery duration per driver.

Use the table where each row represents a single delivery. The columns are:

  • : An identifier for the delivery
  • : An identifier for the driver
  • : Timestamp for the start of the delivery
  • : Timestamp for the end of the delivery

Here are a few rows from table:

delivery_iddriver_iddelivery_start_timedelivery_end_time
112308/01/2022 14:00:0008/01/2022 14:40:00
212308/01/2022 15:15:0008/01/2022 16:10:00
326508/01/2022 14:00:0008/01/2022 15:30:00
426508/01/2022 16:00:0008/01/2022 16:50:00
512308/02/2022 11:00:0008/02/2022 11:35:00

The output should have the following fields:

  • : Average minutes taken to for a delivery in a particular day
  • : Rank over the daily average duration for each driver
  • : Average minutes taken to deliver for all deliveries the driver has made

Answer:


This query calculates the delivery duration by subtracting the start time from the end time for each delivery in minutes. It averages the delivery duration for each day for each driver. The rank is computed based on this daily average duration. The window function allows us to do these computations in a single SQL query without needing to join multiple subqueries.

To practice a similar SQL interview question on DataLemur's free online SQL coding environment, attempt this Facebook SQL Interview question: Facebook Click-through-rate SQL Question

SQL Question 3: Can you explain what an index is and the various types of indexes?

A database index is a way to optimize the performance of a database by reducing the amount of data that needs to be searched to retrieve a record.

There are several types of indexes:

  • unique & non-inuqie indexes
  • primary & composite indexes
  • clustered & non-clustered indexes

DoorDash SQL Interview Questions

SQL Question 4: Restaurant Performance Analysis

As a DoorDash data analyst, your task is to understand the behavior and preferences of DoorDash users, which would be fundamental in improving the service. One of the essential measures of service quality and restaurant popularity is the number of orders each restaurant receives over time. Your task is to design a database consisting of restaurants, users, and orders.

Given the , , and tables below, please write a query to identify the top 5 restaurants with the most orders in the last month.

Example Input
restaurant_idrestaurant_name
001Burger King
002KFC
003McDonald's
004Pizza Hut
005Starbucks
Example Input
user_iduser_name
101John Doe
102Jane Smith
103Bob Johnson
104Alice Anderson
105Emma Wilson
Example Input
order_iduser_idrestaurant_idorder_date
20011010012022-10-01
20021020022022-10-02
20031010032022-10-03
20041030022022-10-04
20051020012022-10-05
20061040042022-10-06
20071050052022-10-07
20081010012022-10-08
20091020022022-10-09
20101040052022-10-10

Answer:


This query joins the table with the table on , filters out orders that are more than a month old, groups the results by , counts the number of orders for each restaurant, sorts the results by the count in descending order, and finally limits the result to the top 5 restaurants. The output will be the names of the top 5 restaurants with the most orders in the last month and the counts of their orders.

To solve a related problem on DataLemur's free online SQL coding environment, solve this Meta SQL interview question: Meta SQL interview question

SQL Question 5: Can you give an example of a one-to-one relationship between two entities, vs. a one-to-many relationship?

In database schema design, a one-to-one relationship between two entities is where each entity is associated with only one instance of the other entity. For example, the relationship between a car and a license plate is one-to-one, because each car can only have one licensce plate, and each licensce plate belongs to exactly one car.

On the other hand, a one-to-many relationship is where one entity can be associated with multiple instances of the 2nd entity. For example, a teacher can teach multiple classes, but each class is associated with only one teacher.

SQL Question 6: Average Delivery Time per Restaurant

As an analyst at DoorDash, you are asked to measure the performance of restaurant partners. One important measure is the average delivery time associated with each restaurant. Assume that we calculate the delivery time by the difference between the order time and delivery completion time. Can you write a SQL query to find the average delivery time for each restaurant?

Please consider the following tables:

Example Input:
order_idorder_timedelivery_timerestaurant_idcustomer_id
000108/25/2021 18:00:0008/25/2021 18:40:00100123
000208/25/2021 19:00:0008/25/2021 19:30:00200265
000308/25/2021 20:00:0008/25/2021 20:40:00200362
000408/25/2021 21:00:0008/25/2021 21:35:00300192
000508/25/2021 22:00:0008/25/2021 22:45:00100981
Example Output:
restaurant_idavg_delivery_time_in_minutes
10042.5
20035.0
30035.0

Answer:


In the above query, we are using the and functions. will give the number of seconds in the interval. We are calculating the difference between the delivery time and order time which gives us an interval. We extract the number of seconds from this interval and convert it to minutes by dividing by 60. This is done for each order and then we take an average for each restaurant using function grouped by .

To solve a similar SQL problem on DataLemur's free online SQL code editor, try this Meta SQL interview question: Meta SQL interview question

SQL Question 7: How does the LEAD() function differ from the LAG() function?

{#Question-7}

Both window functions are used to find rows at a given offset from the current row. However, will give you the rows AFTER the current row you. On the other hand, will give you the rows BEFORE the current row.

SQL Question 8: Calculating Courier Average Distance and Total Revenue

You are the data analyst at DoorDash and it's your job to calculate the average distance travelled, rounded to the nearest whole number, and the total revenue for each courier id in the last month. The total revenue for each courier is calculated by summing all the delivery fee times the quantity of deliveries.

The courier fee is the absolute difference between the delivery's start point and end point, expressed as an integer with two decimal places. If the courier fee is greater than the delivery fee, the courier fee becomes the new delivery fee.

Consider the following table

example input:
delivery_idcourier_idstart_pointend_pointdelivery_feequantitydate
100150110155.00301/01/2022
1002501572.00202/01/2022
1003501033.00103/01/2022
100450218202.00403/01/2022
100550330355.00103/01/2022

Answer:


The above query does the following:

  • The function is used to round the average distance to the nearest whole number.
  • For the total revenue, we use the statement to check if the absolute difference between the start and end point (i.e., the courier fee) is greater than the delivery fee. If so, the courier fee is used; otherwise, the delivery fee is used. This total is then multiplied by the quantity of deliveries to calculate the total revenue for each order.
  • The clause filters out the data for the last one month using the clause.
  • This is grouped by the to provide these metrics for each courier.

To solve a related SQL interview question on DataLemur's free online SQL code editor, solve this Meta SQL interview question: Meta SQL interview question

DoorDash SQL Interview Tips

The best way to prepare for a DoorDash SQL interview is to practice, practice, practice. Besides solving the earlier DoorDash SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like FAANG tech companies and tech startups.

DataLemur SQL Interview Questions

Each exercise has hints to guide you, step-by-step solutions and best of all, there is an interactive coding environment so you can right online code up your SQL query and have it checked.

To prep for the DoorDash SQL interview it is also helpful to practice SQL questions from other tech companies like:

However, if your SQL skills are weak, forget about going right into solving questions – refresh your SQL knowledge with this DataLemur SQL Tutorial.

DataLemur SQL Tutorial for Data Science

This tutorial covers SQL concepts such as aggregate functions like SUM()/COUNT()/AVG() and Subqueries – both of these pop up often in SQL job interviews at DoorDash.

DoorDash Data Science Interview Tips

What Do DoorDash Data Science Interviews Cover?

For the DoorDash Data Science Interview, besides SQL questions, the other types of questions to practice:

  • Probability & Statistics Questions
  • Python or R Programming Questions
  • Business Sense and Product-Sense Questions
  • Machine Learning Questions
  • Behavioral & Resume-Based Questions

DoorDash Data Scientist

How To Prepare for DoorDash Data Science Interviews?

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

  • 201 interview questions sourced from Google, Microsoft & tech startups
  • a crash course covering SQL, Product-Sense & ML
  • over 900+ 5-star reviews on Amazon

Ace the Data Science Interview by Nick Singh Kevin Huo