How to keep records of past week visits in a web app? I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is MariaDB.

Each time a visitor requests an article, the visits field of the article row is incremented by 1. So I can easily query articles by their overall visits.

But what I want is to sort out articles based on number of visits which each article has got in the past week.

I thought to have a separate table like weekly-stats in which I can insert and increment article info with their recent visits upon each request, but then I need to empty the table weekly as a cron job, which is not ideal, and it leaves the stats empty after the data is being purged.

I also thought of having articles' visits saved in an expiring cache like redis and set the cached records to expire after a week, but then it brings all the complexities of inserting and querying redis hash elements.

So I thought there might be some more straightforward design strategies that I miss. How can I achieve this with the least hassle?
The weekly_stats table can have one record for every week, i.e. something like:

CREATE TABLE weekly_stats (
  week_start date PRIMARY_KEY, -- e.g. '2018-03-05'
  article_id int unsigned,
  view_count int unsigned DEFAULT 0,
  CONSTRAINT week_stats_fk1 FOREIGN KEY (article_id) 
    REFERENCES `article` (`article_id`) ON DELETE CASCADE ON UPDATE CASCADE
);


Your application will then have update the weekly_stats.view_count every time an article is viewed. If the record for that particular week doesn't exist, the application will have to create it.

If you want to delete old data, which may not be necessary: Instead of a cron job, you could use a MariaDB event. Set it up to only delete records older than some limit, e.g. 365 days.