SQL Interview Questions – Restaurant Growth

Spread the love

Problem Description –

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Write an SQL query to compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Return result table ordered by visited_on in ascending order.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Restaurant Growth

Solution – 

WITH result as (
SELECT 
    visited_on,
    SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
    visited_on,
    SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
    ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
    DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
) 
SELECT 
	visited_on,
    amount,
    average_amount
FROM result2
WHERE rnk > 6

To solve this problem first we have to group the data by visited_on and calculate the total amount.

SELECT 
    visited_on,
    SUM(amount) as amount
FROM customer
GROUP BY visited_on

Next, we have to use 3 window functions and change their default value to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for the seven days window.

WITH result as (
SELECT 
    visited_on,
    SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
    visited_on,
    SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
    ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
    DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
) 
SELECT * FROM result2

We almost has the result. All we need to do is exclude the top six results using the ranking column and select only the columns that is required in the solution.

WITH result as (
SELECT 
    visited_on,
    SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
    visited_on,
    SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
    ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
    DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
) 
SELECT 
    visited_on,
    amount,
    average_amount
FROM result2
WHERE rnk > 6

Leave a Reply