8 DoorDash SQL Interview Questions (Updated 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 Name | Type |
|---|---|
| order_id | integer |
| customer_id | integer |
| trip_id | integer |
| status | string ('completed successfully', 'completed incorrectly', 'never received') |
| order_timestamp | timestamp |
Example Input:
| order_id | customer_id | trip_id | status | order_timestamp |
|---|---|---|---|---|
| 727424 | 8472 | 100463 | completed successfully | 06/05/2022 09:12:00 |
| 242513 | 2341 | 100482 | completed incorrectly | 06/05/2022 14:40:00 |
| 141367 | 1314 | 100362 | completed incorrectly | 06/07/2022 15:03:00 |
| 582193 | 5421 | 100657 | never_received | 07/07/2022 15:22:00 |
| 253613 | 1314 | 100213 | completed successfully | 06/12/2022 13:43:00 |
Table:
| Column Name | Type |
|---|---|
| dasher_id | integer |
| trip_id | integer |
| estimated_delivery_timestamp | timestamp |
| actual_delivery_timestamp | timestamp |
Example Input:
| dasher_id | trip_id | estimated_delivery_timestamp | actual_delivery_timestamp |
|---|---|---|---|
| 101 | 100463 | 06/05/2022 09:42:00 | 06/05/2022 09:38:00 |
| 102 | 100482 | 06/05/2022 15:10:00 | 06/05/2022 15:46:00 |
| 101 | 100362 | 06/07/2022 15:33:00 | 06/07/2022 16:45:00 |
| 102 | 100657 | 07/07/2022 15:52:00 | - |
| 103 | 100213 | 06/12/2022 14:13:00 | 06/12/2022 14:10:00 |
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| signup_timestamp | timestamp |
Example Input:
| customer_id | signup_timestamp |
|---|---|
| 8472 | 05/30/2022 00:00:00 |
| 2341 | 06/01/2022 00:00:00 |
| 1314 | 06/03/2022 00:00:00 |
| 1435 | 06/05/2022 00:00:00 |
| 5421 | 06/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:
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_id | driver_id | delivery_start_time | delivery_end_time |
|---|---|---|---|
| 1 | 123 | 08/01/2022 14:00:00 | 08/01/2022 14:40:00 |
| 2 | 123 | 08/01/2022 15:15:00 | 08/01/2022 16:10:00 |
| 3 | 265 | 08/01/2022 14:00:00 | 08/01/2022 15:30:00 |
| 4 | 265 | 08/01/2022 16:00:00 | 08/01/2022 16:50:00 |
| 5 | 123 | 08/02/2022 11:00:00 | 08/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:
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
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_id | restaurant_name |
|---|---|
| 001 | Burger King |
| 002 | KFC |
| 003 | McDonald's |
| 004 | Pizza Hut |
| 005 | Starbucks |
Example Input
| user_id | user_name |
|---|---|
| 101 | John Doe |
| 102 | Jane Smith |
| 103 | Bob Johnson |
| 104 | Alice Anderson |
| 105 | Emma Wilson |
Example Input
| order_id | user_id | restaurant_id | order_date |
|---|---|---|---|
| 2001 | 101 | 001 | 2022-10-01 |
| 2002 | 102 | 002 | 2022-10-02 |
| 2003 | 101 | 003 | 2022-10-03 |
| 2004 | 103 | 002 | 2022-10-04 |
| 2005 | 102 | 001 | 2022-10-05 |
| 2006 | 104 | 004 | 2022-10-06 |
| 2007 | 105 | 005 | 2022-10-07 |
| 2008 | 101 | 001 | 2022-10-08 |
| 2009 | 102 | 002 | 2022-10-09 |
| 2010 | 104 | 005 | 2022-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:
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_id | order_time | delivery_time | restaurant_id | customer_id |
|---|---|---|---|---|
| 0001 | 08/25/2021 18:00:00 | 08/25/2021 18:40:00 | 100 | 123 |
| 0002 | 08/25/2021 19:00:00 | 08/25/2021 19:30:00 | 200 | 265 |
| 0003 | 08/25/2021 20:00:00 | 08/25/2021 20:40:00 | 200 | 362 |
| 0004 | 08/25/2021 21:00:00 | 08/25/2021 21:35:00 | 300 | 192 |
| 0005 | 08/25/2021 22:00:00 | 08/25/2021 22:45:00 | 100 | 981 |
Example Output:
| restaurant_id | avg_delivery_time_in_minutes |
|---|---|
| 100 | 42.5 |
| 200 | 35.0 |
| 300 | 35.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:
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_id | courier_id | start_point | end_point | delivery_fee | quantity | date |
|---|---|---|---|---|---|---|
| 1001 | 501 | 10 | 15 | 5.00 | 3 | 01/01/2022 |
| 1002 | 501 | 5 | 7 | 2.00 | 2 | 02/01/2022 |
| 1003 | 501 | 0 | 3 | 3.00 | 1 | 03/01/2022 |
| 1004 | 502 | 18 | 20 | 2.00 | 4 | 03/01/2022 |
| 1005 | 503 | 30 | 35 | 5.00 | 1 | 03/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:
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.
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.
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 include:
- Probability & Statistics Questions
- Python or R Programming Questions
- A/B testing Questions
- Product-Sense Questions
- Machine Learning Questions
- Behavioral & Resume-Based Questions
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