How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at DoorDash.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at DoorDash during technical interviews.

Find orders from bottom-quartile revenue restaurants

Q: Find orders from bottom-quartile revenue restaurants

This question evaluates proficiency in SQL data manipulation, including time-windowed aggregation, joins, filtering by status and date, and percentile-based ranking of restaurant revenue using completed orders.

SQL Question

You want to identify orders coming from restaurants whose total revenue is in the bottom 25th percentile.

Assume the following tables:

`restaurants`

restaurant_id (BIGINT, PK)
name (VARCHAR)
market_id (BIGINT)

`orders`

order_id (BIGINT, PK)
restaurant_id (BIGINT, FK → restaurants.restaurant_id)
customer_id (BIGINT)
order_total (DECIMAL(10,2)) — revenue for this order (exclude tips)
created_at (TIMESTAMP)
status (VARCHAR) — e.g., 'completed', 'canceled'

Task

Write a SQL query to return orders from restaurants whose total completed-order revenue is in the bottom 25% among all restaurants over a specified analysis window.

Requirements

Use an analysis window of the last 30 days relative to CURRENT_DATE .
Consider only status = 'completed' orders.
Define restaurant revenue as SUM(order_total) over the window.
Compute the 25th percentile of restaurant revenue across restaurants with ≥1 completed order in the window.

Output columns

order_id
restaurant_id
order_total
created_at
restaurant_revenue_30d

State any assumptions (e.g., percentile function availability in your SQL dialect).

SQL Question

You want to identify orders coming from restaurants whose total revenue is in the bottom 25th percentile.

Assume the following tables:

restaurants

restaurant_id (BIGINT, PK)

name (VARCHAR)

market_id (BIGINT)

orders

order_id (BIGINT, PK)

restaurant_id (BIGINT, FK → restaurants.restaurant_id)

customer_id (BIGINT)

order_total (DECIMAL(10,2)) — revenue for this order (exclude tips)

created_at (TIMESTAMP)

status (VARCHAR) — e.g., 'completed', 'canceled'

Task

Write a SQL query to return orders from restaurants whose total completed-order revenue is in the bottom 25% among all restaurants over a specified analysis window.

Requirements

Use an analysis window of the last 30 days relative to CURRENT_DATE .

Consider only status = 'completed' orders.

Define restaurant revenue as SUM(order_total) over the window.

Compute the 25th percentile of restaurant revenue across restaurants with ≥1 completed order in the window.

Output columns

order_id

restaurant_id

order_total

created_at

restaurant_revenue_30d

State any assumptions (e.g., percentile function availability in your SQL dialect).

Find orders from bottom-quartile revenue restaurants

Quick Overview

SQL Question

`restaurants`

`orders`

Task

Requirements

Output columns

Comments (0)

Find orders from bottom-quartile revenue restaurants

Quick Overview

SQL Question

`restaurants`

`orders`

Task

Requirements

Output columns

Comments (0)