SQL problem - Restaurant Growth

1 min read 245 words

Problem

Query

WITH T AS (
SELECT
    *,
    (
        CASE
            WHEN
                ROW_NUMBER() OVER (ORDER BY visited_on) >= 7 THEN
                    SUM(c.sum_amount_per_day) OVER (ORDER BY visited_on
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        END
    ) as cumulative_sum,
    (
        CASE
            WHEN
                ROW_NUMBER() OVER (ORDER BY visited_on) >= 7 THEN
                    ROUND(AVG(c.sum_amount_per_day) OVER (ORDER BY visited_on
                                    ROWS BETWEEN 6 PRECEDING AND
                                    CURRENT ROW), 2)
            ELSE NULL
        END
    ) AS average_amount
FROM
    (
        SELECT *, SUM(amount) as sum_amount_per_day
        FROM Customer
        GROUP BY visited_on
    ) as c
GROUP BY
    c.visited_on
)

SELECT
    visited_on, cumulative_sum as amount, average_amount
FROM T
WHERE average_amount IS NOT NULL
ORDER BY visited_on ASC