Last updated on October 9th, 2024 at 10:01 pm

This Leetcode problem **Restaurant Growth LeetCode Solution** is done in SQL.

*List of all LeetCode Solution*

*List of all LeetCode Solution*

## Level of Question

Medium

**Restaurant Growth LeetCode Solution**

## Table of Contents

**Problem Statement**

Column Name | Type |

customer_id | int |

name | varchar |

visited_on | date |

amount | int |

**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).

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_id | name | visited_on | amount |

1 | Jhon | 2019-01-01 | 100 |

2 | Daniel | 2019-01-02 | 110 |

3 | Jade | 2019-01-03 | 120 |

4 | Khaled | 2019-01-04 | 130 |

5 | Winston | 2019-01-05 | 110 |

6 | Elvis | 2019-01-06 | 140 |

7 | Anna | 2019-01-07 | 150 |

8 | Maria | 2019-01-08 | 80 |

9 | Jaze | 2019-01-09 | 110 |

1 | Jhon | 2019-01-10 | 130 |

3 | Jade | 2019-01-10 | 150 |

**Customer table:**

**Output:**

visited_on | amount | average_amount |

2019-01-07 | 860 | 122.86 |

2019-01-08 | 840 | 120 |

2019-01-09 | 840 | 120 |

2019-01-10 | 1000 | 142.86 |

**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

**1. Restaurant Growth LeetCode Solution MySQL**

select visited_on, amount, average_amount from ( select visited_on, @cnt := @cnt + 1 as cnt, @d7 := @d6, @d6 := @d5, @d5 := @d4, @d4 := @d3, @d3 := @d2, @d2 := @d1, @d1 := amount, @total := @d1 + @d2 + @d3 + @d4 + @d5 + @d6 + @d7 as amount, round(@total / 7, 2) as average_amount from ( select visited_on, sum(amount) as amount from Customer group by visited_on ) as c, ( select @cnt := 0, @total := 0, @d1 := 0, @d2 := 0, @d3 := 0, @d4 := 0, @d5 := 0, @d6 := 0, @d7 := 0 ) as t ) as s where cnt >= 7;