Calculate Monthly Restaurant Sales Growth
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
# Calculate Monthly Restaurant Sales Growth
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
delivery_orders
| column | type |
|---|---|
| delivery_id | integer |
| order_place_time | timestamp |
| restaurant_id | integer |
order_value
| column | type |
|---|---|
| delivery_id | integer |
| order_total | numeric |
## Task
For restaurant 8, calculate month-over-month percentage sales change for February through December 2021. Assume the restaurant has at least one delivery in every month of 2021.
## Required Output
Return sales_month, monthly_sales, previous_month_sales, and growth_pct rounded to two decimal places. Sort by month ascending.
## Constraints
- Aggregate monthly sales before using a window function.
- Ignore January in the final output but retain it to calculate February growth.
- If previous-month sales are zero, return NULL growth rather than dividing by zero.
```hint Lag the monthly total
Join orders to values, aggregate by month, and use LAG on those twelve monthly rows.
```
Quick Answer: A PostgreSQL interview problem about calculating month-over-month restaurant sales growth. It tests monthly aggregation, window functions, missing prior periods, division-by-zero handling, and deterministic result ordering.