1321 Restaurant Growth

Description

Table: Customer

• In SQL, (customer_id, visited_on) is the primary key for this table.
• This table contains data about customer transactions in a restaurant.
• visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
• amount is the total paid by a customer.

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

Problem Statement

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 the result table ordered by visited_on in ascending order.

The result format is in the following example.

Example 1:

Input:

• Customer table:

Output:

Explanation:

• 1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
• 2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
• 3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
• 4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

Solution

This problem can be solved using window functions. In this case, you need to create a window that spans 7 days. This not based on number of rows because on a single day, there can be multiple entries. For example, in the input dataset, you can see on 2019-01-10, there are two entries. So, you cannot use ROWS BETWEEN clause for this window. In this case, you can use RANGE BETWEEN and specify the range of values for the visited_on column. Your window clause would look like ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW.

Next, you create amount column which is sum of amount column from the input data. To calculate average_amount, you have to calculate the sum and divide the sum by 7. Again, you cannot use built in function like AVG() because that would divide the sum by the number of records and not by 7. So, if there are more than one records for a single day, the AVG() function would retrieve incorrect result. You also need to round this result to two decimal numbers using ROUND() function.

1SELECT 
2    visited_on, 
3    SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN  INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount,
4    ROUND(
5        SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) / 7, 2 
6    ) AS average_amount
7    FROM Customer;

This query produces rows for all dates and not just the ones which has past 7 day history, but you can see the results are correct for last 5 rows.

Next, you need single row per date. So, you can use DISTINCT to avoid repeating last two rows in above result. You can also ensure that it has a history of at least 7 days by using WHERE clause and DATE_SUB(visited_on, INTERVAL 6 DAY). This date should be MIN(visited_on) or later. So, you can use WHERE DATE_SUB(visited_on, INTERVAL 6 DAY) >= (SELECT MIN(visited_on) FROM Customer).

 1WITH running_total AS (
 2    SELECT 
 3        visited_on, 
 4        SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN  INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount,
 5        ROUND(
 6            SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) / 7, 2 
 7        ) AS average_amount,
 8        DATE_SUB(visited_on, INTERVAL 6 DAY) AS day_before_7_days
 9        FROM Customer
10) SELECT DISTINCT visited_on, amount, average_amount
11    FROM running_total
12    WHERE day_before_7_days >= (SELECT MIN(visited_on) FROM Customer);