Search code examples
mysqlsqldatecountwindow-functions

How to count the visits of the last 7 days in MySQL

I have a sample table that capture user visits

User|Visit|Date--
Mike|01|1/9/2020--
Mike|01|3/9/2020--
Mike|01|10/9/2020--
John|01|12/9/2020--
John|01|20/9/2020--
Carl|01|16/9/2020--
Carl|01|18/9/2020--
Carl|01|20/9/2020

I want to create a MySQL query such that it captures all the fields/rows from example table and create an additional column "last 7Days". In this column, the idea is to sum all visits from the user from the last 7 days.

The outcome I'm looking out it is something like this

Query_Image

Solution

  • you can use window function with range check this out

    select t.*, 
  sum(visit) over (partition by user order by `Date` 
    range between interval + 7 day preceding and current row  ) -1 as x
from t

    Fiddle