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

Restaurant Growth LeetCode Solution

Problem Statement

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:

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

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;