Restaurant Growth SQL MySQL leetcode solution with DATEDIFF and left join sum average functions
SQL MySQL leetcode with CTE DATEDIFF SUM AVG left join
Challenge: Analyzing Restaurant Customer Spending Trends
Difficulty: Medium
Data:
A table named Customer stores customer transaction information:
customer_id (int): Unique identifier for each customer.
name (varchar): Customer name.
visited_on (date): Date of the customer's visit.
amount (int): Total amount paid by the customer.
(customer_id, visited_on) is the primary key for this table.
Objective:
As a restaurant owner, you'd like to analyze customer spending patterns to understand potential trends. Calculate the moving average of the amount spent by customers within a window of seven days (including the current day).
Output:
A table with three columns:
visited_on (date): Date of the customer visit.
amount (int): Total amount paid by the customer on that visit.
average_amount (decimal): The moving average of the total amount spent within the seven-day window, rounded to two decimal places.
This MySQL query computes a 7-day rolling average of the amount column for visits from a Customer table. Here's a step-by-step breakdown of the query:
1. Common Table Expression (CTE) D
The first part of the query creates a Common Table Expression (CTE) named D:
sql
Copy code
WITH D AS (
SELECT DISTINCT
visited_on
FROM
Customer
WHERE
visited_on >= (
SELECT
DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
FROM
Customer
)
)
Purpose: The CTE generates a list of unique visited_on dates starting from 6 days after the earliest date in the Customer table.
Details:
MIN(visited_on): Finds the earliest visit date in the Customer table.
DATE_ADD(MIN(visited_on), INTERVAL 6 DAY): Adds 6 days to the earliest visit date.
The SELECT DISTINCT visited_on clause ensures only unique visit dates are included.
The WHERE clause filters out dates that are earlier than this adjusted date (6 days after the earliest date).
2. Main Query
The main part of the query calculates the total and average amounts for a 7-day period starting from each date in D:
sql
Copy code
SELECT
D.visited_on,
SUM(Customer.amount) amount,
ROUND(SUM(Customer.amount)/7, 2) average_amount
FROM
D
LEFT JOIN
Customer ON (DATEDIFF(D.visited_on, Customer.visited_on) BETWEEN 0 AND 6)
GROUP BY
1;
Join Operation:
LEFT JOIN Customer ON (DATEDIFF(D.visited_on, Customer.visited_on) BETWEEN 0 AND 6): This joins each date in D with all dates in the Customer table that fall within a 7-day range (from the date in D to 6 days before).
DATEDIFF(D.visited_on, Customer.visited_on) BETWEEN 0 AND 6: Ensures that the difference in days between the dates in D and Customer is between 0 and 6 (inclusive).
Aggregation:
SUM(Customer.amount) amount: Sums the amount for each 7-day period.
ROUND(SUM(Customer.amount)/7, 2) average_amount: Computes the average amount over the 7-day period, rounded to 2 decimal places.
Grouping:
GROUP BY 1: Groups the results by the visited_on date from D.
Summary
The query generates a result set with the following columns:
visited_on: Each unique date starting from 6 days after the earliest visit date in the Customer table.
amount: The total amount spent by customers over a 7-day period ending on the visited_on date.
average_amount: The average amount spent per day over this 7-day period.
The query effectively provides a 7-day rolling total and average amount for customer visits, giving insight into spending trends over time.
Watch video Restaurant Growth SQL MySQL leetcode solution with DATEDIFF and left join sum average functions online, duration hours minute second in high quality that is uploaded to the channel CodeWis Technologies by Nuhman Paramban 26 May 2024. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 36 times and liked it 1 visitors.
