NUHMAN.com

Query Explanation:

This MySQL query calculates a 7-day rolling total and average of the amount spent by customers over a series of distinct dates (visited_on) from the Customer table. The query is broken into two main parts:

1. Common Table Expression (CTE) D

The first part of the query defines a Common Table Expression (CTE) named D which selects distinct visited_on dates from the Customer table.

sql

WITH D AS (
    SELECT DISTINCT
        visited_on
    FROM
        Customer
    WHERE
        visited_on >= (
            SELECT 
                DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
            FROM
                Customer
        )
)

• Purpose of the CTE:
• This part is creating a list of distinct visit dates (visited_on), starting from 6 days after the earliest visit date recorded in the Customer table.
• Explanation:
• MIN(visited_on): Finds the earliest visit date in the Customer table.
• DATE_ADD(MIN(visited_on), INTERVAL 6 DAY): Adds 6 days to this earliest date, so the query begins from this point.
• SELECT DISTINCT visited_on: Ensures that only unique visited_on dates are selected, avoiding duplicates.
• The WHERE clause filters out any visit dates before the adjusted starting date (6 days after the earliest visit).

2. Main Query (Aggregating Amount and Average)

The main part of the query calculates the total (amount) and the 7-day rolling average of the amount spent by customers. It uses the LEFT JOIN to include visits from Customer within a 7-day window around each date in the D CTE.

sql

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):
• Joins each date in D with all visited_on dates from Customer that fall within a 7-day range.
• DATEDIFF(D.visited_on, Customer.visited_on) calculates the difference between the dates.
• The condition BETWEEN 0 AND 6 ensures the difference is within the 7-day window (including the date itself).
• Aggregation:
• SUM(Customer.amount) amount: Sums up the amount spent by customers for each 7-day period.
• ROUND(SUM(Customer.amount)/7, 2) average_amount: Computes the average amount spent per day over the 7-day period. It divides the total amount by 7 (days) and rounds the result to 2 decimal places.
• Grouping:
• GROUP BY 1: Groups the results by the first column in the SELECT statement, which is the visited_on date from D.

What Does This Query Do?

The query calculates the total and average amount spent by customers in each rolling 7-day period, starting from 6 days after the earliest visit recorded in the Customer table.

• The rolling sum represents the total amount spent during the past 7 days (including the current day).
• The rolling average is the average of the amounts spent per day over the last 7 days.

Example Workflow:

• Let's assume the Customer table contains the following records:

idvisited_onamount12023-01-015022023-01-027032023-01-033042023-01-046052023-01-059062023-01-068072023-01-0710082023-01-08110

• If the earliest visited_on is 2023-01-01, then the query starts at 2023-01-07 (6 days after the earliest visit).
• The query will then calculate the total and average amounts for every 7-day window starting from 2023-01-07.

Final Result:

The final result will include:

• visited_on: The start date of the 7-day rolling window.
• amount: The sum of amount for the last 7 days (including the current day).
• average_amount: The average of amount over the 7-day period.

Summary:

• CTE (D): Selects distinct visited_on dates starting from 6 days after the earliest visited_on.
• Main Query: Joins these dates with the Customer table and calculates the 7-day total and average amount for each distinct visited_on date.