SQL to calculate the average amount paid in the last 7 days

I have a table with 2 columns visited and the amount paid

visited         amount paid
-------------------------
2018-04-01      100
2018-04-02      200
2018-04-03      300

I want to get the average amount paid in the last 7 days (today and last 6 days) along with visited and amount paid in result:

Output:

visited         amount      average of last 7 days
---------------------------------------------------
2018-04-01      100         100
2018-04-02      200         150
2018-04-03      300         200

Explanation:

Row 1 : average amount = 100/1 = 100
Row 2 : average amount = (100 + 200)/2 = 150
Row 3 : average amount = (100 + 200 + 300)/3 = 200

I am trying this using MySQL or Oracle database, anything is fine.

Answer

You need to use a correlated sub-query in MySQL 5.x:

SELECT visited, amount_paid, (
    SELECT AVG(amount_paid)
    FROM t AS x
    WHERE x.visited >= t.visited - INTERVAL 6 DAY
    AND   x.visited <= t.visited
) AS 7_day_avg
FROM t

In MySQL 8 and Oracle you could use window functions.

Advertisement

Answer