SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table login_logs (
    account_id INT,
    login_datetime DATETIME2(0)
);
insert into login_logs (account_id, login_datetime) values (1, '2022-03-02 02:05:02');
insert into login_logs (account_id, login_datetime) values (2, '2022-03-02 02:09:07');
insert into login_logs (account_id, login_datetime) values (3, '2022-03-02 02:14:07');
insert into login_logs (account_id, login_datetime) values (4, '2022-03-02 02:16:07');
insert into login_logs (account_id, login_datetime) values (5, '2022-03-02 02:27:07');
insert into login_logs (account_id, login_datetime) values (6, '2022-03-02 02:28:07');
insert into login_logs (account_id, login_datetime) values (7, '2022-03-02 02:30:00');
insert into login_logs (account_id, login_datetime) values (8, '2022-03-02 02:36:02');
insert into login_logs (account_id, login_datetime) values (9, '2022-03-02 02:40:07');
insert into login_logs (account_id, login_datetime) values (10, '2022-03-02 02:46:17');
insert into login_logs (account_id, login_datetime) values (11, '2022-03-02 02:54:27');
insert into login_logs (account_id, login_datetime) values (12, '2022-03-02 02:58:10');
12 rows affected

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    DATEDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
    CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins AS diff_round_up,
    DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;


account_id
login_datetime
diff
diff_round_up
login_datetime_15_min_interval


1
2022-03-02 02:05:02
141566525
141566535
2022-03-02 02:15:00.000

2
2022-03-02 02:09:07
141566529
141566535
2022-03-02 02:15:00.000

3
2022-03-02 02:14:07
141566534
141566535
2022-03-02 02:15:00.000

4
2022-03-02 02:16:07
141566536
141566550
2022-03-02 02:30:00.000

5
2022-03-02 02:27:07
141566547
141566550
2022-03-02 02:30:00.000

6
2022-03-02 02:28:07
141566548
141566550
2022-03-02 02:30:00.000

7
2022-03-02 02:30:00
141566550
141566550
2022-03-02 02:30:00.000

8
2022-03-02 02:36:02
141566556
141566565
2022-03-02 02:45:00.000

9
2022-03-02 02:40:07
141566560
141566565
2022-03-02 02:45:00.000

10
2022-03-02 02:46:17
141566566
141566580
2022-03-02 03:00:00.000

11
2022-03-02 02:54:27
141566574
141566580
2022-03-02 03:00:00.000

12
2022-03-02 02:58:10
141566578
141566580
2022-03-02 03:00:00.000


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval,
    COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
ORDER BY login_datetime_15_min_interval;


login_datetime_15_min_interval
distinct_account_logins


2022-03-02 02:15:00.000
3

2022-03-02 02:30:00.000
4

2022-03-02 02:45:00.000
2

2022-03-02 03:00:00.000
3


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    DATEDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
    FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins AS diff_round_down,
    DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;


account_id
login_datetime
diff
diff_round_down
login_datetime_15_min_interval


1
2022-03-02 02:05:02
141566525
141566520
2022-03-02 02:00:00.000

2
2022-03-02 02:09:07
141566529
141566520
2022-03-02 02:00:00.000

3
2022-03-02 02:14:07
141566534
141566520
2022-03-02 02:00:00.000

4
2022-03-02 02:16:07
141566536
141566535
2022-03-02 02:15:00.000

5
2022-03-02 02:27:07
141566547
141566535
2022-03-02 02:15:00.000

6
2022-03-02 02:28:07
141566548
141566535
2022-03-02 02:15:00.000

7
2022-03-02 02:30:00
141566550
141566550
2022-03-02 02:30:00.000

8
2022-03-02 02:36:02
141566556
141566550
2022-03-02 02:30:00.000

9
2022-03-02 02:40:07
141566560
141566550
2022-03-02 02:30:00.000

10
2022-03-02 02:46:17
141566566
141566565
2022-03-02 02:45:00.000

11
2022-03-02 02:54:27
141566574
141566565
2022-03-02 02:45:00.000

12
2022-03-02 02:58:10
141566578
141566565
2022-03-02 02:45:00.000


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval,
    COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
ORDER BY login_datetime_15_min_interval;


login_datetime_15_min_interval
distinct_account_logins


2022-03-02 02:00:00.000
3

2022-03-02 02:15:00.000
3

2022-03-02 02:30:00.000
3

2022-03-02 02:45:00.000
3


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 AS nearest_indicator,
    CASE
        WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
            THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
        ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
        END AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;


account_id
login_datetime
nearest_indicator
login_datetime_15_min_interval


1
2022-03-02 02:05:02
0.3333333333333333333
2022-03-02 02:00:00.000

2
2022-03-02 02:09:07
0.6000000000000000000
2022-03-02 02:15:00.000

3
2022-03-02 02:14:07
0.9333333333333333333
2022-03-02 02:15:00.000

4
2022-03-02 02:16:07
0.0666666666666666666
2022-03-02 02:15:00.000

5
2022-03-02 02:27:07
0.8000000000000000000
2022-03-02 02:30:00.000

6
2022-03-02 02:28:07
0.8666666666666666666
2022-03-02 02:30:00.000

7
2022-03-02 02:30:00
0.0000000000000000000
2022-03-02 02:30:00.000

8
2022-03-02 02:36:02
0.4000000000000000000
2022-03-02 02:30:00.000

9
2022-03-02 02:40:07
0.6666666666666666666
2022-03-02 02:45:00.000

10
2022-03-02 02:46:17
0.0666666666666666666
2022-03-02 02:45:00.000

11
2022-03-02 02:54:27
0.6000000000000000000
2022-03-02 03:00:00.000

12
2022-03-02 02:58:10
0.8666666666666666666
2022-03-02 03:00:00.000


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_15_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
ORDER BY login_datetime_15_min_interval;


login_datetime_15_min_interval
distinct_account_logins


2022-03-02 02:00:00.000
1

2022-03-02 02:15:00.000
3

2022-03-02 02:30:00.000
4

2022-03-02 02:45:00.000
2

2022-03-02 03:00:00.000
2


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 5;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_5_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
ORDER BY login_datetime_5_min_interval;


login_datetime_5_min_interval
distinct_account_logins


2022-03-02 02:05:00.000
1

2022-03-02 02:10:00.000
1

2022-03-02 02:15:00.000
2

2022-03-02 02:25:00.000
1

2022-03-02 02:30:00.000
2

2022-03-02 02:35:00.000
1

2022-03-02 02:40:00.000
1

2022-03-02 02:45:00.000
1

2022-03-02 02:55:00.000
1

2022-03-02 03:00:00.000
1


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 10;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_10_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
ORDER BY login_datetime_10_min_interval;


login_datetime_10_min_interval
distinct_account_logins


2022-03-02 02:10:00.000
3

2022-03-02 02:20:00.000
1

2022-03-02 02:30:00.000
3

2022-03-02 02:40:00.000
2

2022-03-02 02:50:00.000
2

2022-03-02 03:00:00.000
1


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 20;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_20_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
ORDER BY login_datetime_20_min_interval;


login_datetime_20_min_interval
distinct_account_logins


2022-03-02 02:00:00.000
2

2022-03-02 02:20:00.000
4

2022-03-02 02:40:00.000
4

2022-03-02 03:00:00.000
2


DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 30;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS FLOAT)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS FLOAT)) * @interval_mins, @begin_datetime)
		END AS login_datetime_30_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS FLOAT)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS FLOAT)) * @interval_mins, @begin_datetime)
		END
ORDER BY login_datetime_30_min_interval;


login_datetime_30_min_interval
distinct_account_logins


2022-03-02 02:00:00.000
3

2022-03-02 02:30:00.000
6

2022-03-02 03:00:00.000
3

account_id	login_datetime	diff	diff_round_up	login_datetime_15_min_interval
1	2022-03-02 02:05:02	141566525	141566535	2022-03-02 02:15:00.000
2	2022-03-02 02:09:07	141566529	141566535	2022-03-02 02:15:00.000
3	2022-03-02 02:14:07	141566534	141566535	2022-03-02 02:15:00.000
4	2022-03-02 02:16:07	141566536	141566550	2022-03-02 02:30:00.000
5	2022-03-02 02:27:07	141566547	141566550	2022-03-02 02:30:00.000
6	2022-03-02 02:28:07	141566548	141566550	2022-03-02 02:30:00.000
7	2022-03-02 02:30:00	141566550	141566550	2022-03-02 02:30:00.000
8	2022-03-02 02:36:02	141566556	141566565	2022-03-02 02:45:00.000
9	2022-03-02 02:40:07	141566560	141566565	2022-03-02 02:45:00.000
10	2022-03-02 02:46:17	141566566	141566580	2022-03-02 03:00:00.000
11	2022-03-02 02:54:27	141566574	141566580	2022-03-02 03:00:00.000
12	2022-03-02 02:58:10	141566578	141566580	2022-03-02 03:00:00.000

account_id	login_datetime	diff	diff_round_down	login_datetime_15_min_interval
1	2022-03-02 02:05:02	141566525	141566520	2022-03-02 02:00:00.000
2	2022-03-02 02:09:07	141566529	141566520	2022-03-02 02:00:00.000
3	2022-03-02 02:14:07	141566534	141566520	2022-03-02 02:00:00.000
4	2022-03-02 02:16:07	141566536	141566535	2022-03-02 02:15:00.000
5	2022-03-02 02:27:07	141566547	141566535	2022-03-02 02:15:00.000
6	2022-03-02 02:28:07	141566548	141566535	2022-03-02 02:15:00.000
7	2022-03-02 02:30:00	141566550	141566550	2022-03-02 02:30:00.000
8	2022-03-02 02:36:02	141566556	141566550	2022-03-02 02:30:00.000
9	2022-03-02 02:40:07	141566560	141566550	2022-03-02 02:30:00.000
10	2022-03-02 02:46:17	141566566	141566565	2022-03-02 02:45:00.000
11	2022-03-02 02:54:27	141566574	141566565	2022-03-02 02:45:00.000
12	2022-03-02 02:58:10	141566578	141566565	2022-03-02 02:45:00.000

account_id	login_datetime	nearest_indicator	login_datetime_15_min_interval
1	2022-03-02 02:05:02	0.3333333333333333333	2022-03-02 02:00:00.000
2	2022-03-02 02:09:07	0.6000000000000000000	2022-03-02 02:15:00.000
3	2022-03-02 02:14:07	0.9333333333333333333	2022-03-02 02:15:00.000
4	2022-03-02 02:16:07	0.0666666666666666666	2022-03-02 02:15:00.000
5	2022-03-02 02:27:07	0.8000000000000000000	2022-03-02 02:30:00.000
6	2022-03-02 02:28:07	0.8666666666666666666	2022-03-02 02:30:00.000
7	2022-03-02 02:30:00	0.0000000000000000000	2022-03-02 02:30:00.000
8	2022-03-02 02:36:02	0.4000000000000000000	2022-03-02 02:30:00.000
9	2022-03-02 02:40:07	0.6666666666666666666	2022-03-02 02:45:00.000
10	2022-03-02 02:46:17	0.0666666666666666666	2022-03-02 02:45:00.000
11	2022-03-02 02:54:27	0.6000000000000000000	2022-03-02 03:00:00.000
12	2022-03-02 02:58:10	0.8666666666666666666	2022-03-02 03:00:00.000

login_datetime_5_min_interval	distinct_account_logins
2022-03-02 02:05:00.000	1
2022-03-02 02:10:00.000	1
2022-03-02 02:15:00.000	2
2022-03-02 02:25:00.000	1
2022-03-02 02:30:00.000	2
2022-03-02 02:35:00.000	1
2022-03-02 02:40:00.000	1
2022-03-02 02:45:00.000	1
2022-03-02 02:55:00.000	1
2022-03-02 03:00:00.000	1

login_datetime_10_min_interval	distinct_account_logins
2022-03-02 02:10:00.000	3
2022-03-02 02:20:00.000	1
2022-03-02 02:30:00.000	3
2022-03-02 02:40:00.000	2
2022-03-02 02:50:00.000	2
2022-03-02 03:00:00.000	1