Katkat
Back

Calculate Monthly Restaurant Sales Growth

DoorDashData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests the ability to aggregate transactional data by month and use window functions to calculate percentage growth between consecutive periods while handling null values.

Problem Statement

You are given a table orders containing historical restaurant transaction data. You need to calculate the month-over-month (MoM) sales growth percentage for each restaurant.

Table: orders

ColumnTypeDescription
order_idintegerUnique identifier
restaurant_idintegerID of the restaurant
sale_amountdecimalRevenue from the order
created_attimestampDate of the transaction

Task

Write a SQL query to return the restaurant_id, month (in 'YYYY-MM' format), total_monthly_sales, and growth_percentage.

  • The growth_percentage is defined as: (current_month_sales - prior_month_sales) / prior_month_sales * 100.
  • If a restaurant has no sales in the prior month, the growth percentage should be NULL.
  • Order the results by restaurant_id ascending, and then by month ascending.

Example

Input:

restaurant_idsale_amountcreated_at
11002023-01-05
12002023-02-10

Output:

restaurant_idmonthtotal_monthly_salesgrowth_percentage
12023-01100NULL
12023-02200100.0

Constraints

  • If prior_month_sales is 0, treat the growth as NULL or handle it gracefully to avoid division errors.
  • Ensure all months are represented, even if there are no sales in a specific month for a specific restaurant (only consider months where sales > 0).

Hints

Hint 1

Use DATE_TRUNC or TO_CHAR to extract the month and year from the `created_at` timestamp.

Hint 2

Aggregate the sales by `restaurant_id` and `month` using a GROUP BY clause before applying window functions.

Hint 3

Use the `LAG()` window function to fetch the `total_monthly_sales` from the previous month, partitioned by `restaurant_id`.

Follow-up questions

  • How would you modify your query to include months where a restaurant had zero sales if you were provided a master calendar table?
  • What steps would you take to ensure the query remains performant as the number of transactions grows to billions of rows?
  • If the business defines 'growth' differently for new restaurants (e.g., first month is 0% growth), how would you adjust your calculation logic?