Moving Average - MySQL

Question

I am trying to implement system-wide login control and I need to calculate the average daily number of failed login attempts in the last 3 months.

I am currently inserting a record for every login failure, each with a timestamp. How can I do this in MySQL?

Thanks in advance for your help.

Answer 1

SELECT AVG(cnt)
  FROM (SELECT COUNT(*) AS cnt
          FROM mytable
         WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND NOW()
      GROUP BY DATE(`date`)) x

Assuming you have a table mytablewith a datetype field date, datetimeortimestamp