Grouping Mysql by Week, One Month, and Three Previous Months

The structure of the table consists of a sample row. The desired output for my application includes the number of new users within specific time frames. For instance, in the last 24 hours, there were 42 new users, in the last week, there were 532 new users, and in the last month, there were 4568 new users. The same calculation applies to 3 months, 6 months, and all-time. It is important to note that the calculation for the week starts on Sunday.


Question:

I possess a table containing a column called ”

started_at

“. My objective is to obtain the statistics for newly inserted rows within the last day, week, month, and three months. The format of the ”

started_at

” column is a string timestamp represented by ”
default MySQL
“. Prior to submitting this inquiry, I attempted the following queries.

SELECT WEEK(`started_at`) , COUNT(*) AS nbr FROM users_in_bots WHERE `bot_id` = 5529 GROUP BY WEEK (`started_at`);
SELECT MONTH(`started_at`), COUNT(*) AS nbr FROM users_in_bots WHERE `bot_id` = 5529 GROUP BY MONTH(`started_at`);

The outcome is not in line with my expectations. I desire to obtain all statistics using only a single query.
The structure of the table:

CREATE TABLE `users_in_bots` (
  `user_id` bigint(20) NOT NULL,
  `bot_id` bigint(20) NOT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

sample row :

INSERT INTO `users_in_bots` (`user_id`, `bot_id`, `started_at`) VALUES
(2314, 509492849, '2022-02-27 03:59:21'),
(28779, 210686266, '2022-03-03 21:51:38'),
(28779, 503513058, '2022-04-01 12:28:37'),
(28779, 515774720, '2022-03-25 08:25:16'),
(28779, 518099352, '2022-03-22 17:22:38'),
(28779, 519646468, '2022-03-04 22:02:02'),
(84588, 517141146, '2022-03-28 12:36:45'),
(87075, 509498849, '2022-02-27 03:59:21'),
(116264, 210509102, '2022-02-27 00:02:54'),
(116264, 212268136, '2022-02-27 00:29:06');

The desired output for my application is as follows:
– Number of new users in the past 24 hours: 42
– Number of new users in the past week: 532
– Number of new users in the past month: 4568
Similarly, the counts for the past 3 months, 6 months, and all-time will also be included.


Solution:

To achieve the desired outcome, conditional aggregation can be utilized. For instance:

SELECT SUM(started_at BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()) last_hour,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE()) last_week,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()) last_month,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 3 MONTH AND CURDATE()) last_3month,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE()) last_6month
FROM users_in_bots

Output (based on the provided sample data as of April 13th, 2022):

last_hour   last_week   last_month  last_3month last_6month
0           0           4           10          10

Demo on dbfiddle

Frequently Asked Questions