Restaurant Growth LeetCode Solution

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

Level of Question

Medium

Restaurant Growth LeetCode Solution

Restaurant Growth LeetCode Solution

Problem Statement

Column NameType
customer_idint
namevarchar
visited_ondate
amountint
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_idnamevisited_onamount
1Jhon2019-01-01100
2Daniel2019-01-02110
3Jade2019-01-03120
4Khaled2019-01-04130
5Winston2019-01-05110
6Elvis2019-01-06140
7Anna2019-01-07150
8Maria2019-01-0880
9Jaze2019-01-09110
1Jhon2019-01-10130
3Jade2019-01-10150
Customer table:

Output:

visited_onamountaverage_amount
2019-01-07860122.86
2019-01-08840120
2019-01-09840120
2019-01-101000142.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;
Scroll to Top