By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #TBL(
Email varchar(50),
First_Name varchar(50),
AccessDate DateTime
)
--Insert Values
INSERT INTO #TBL(Email , First_Name , AccessDate)
VALUES
('USER1@GMAIL','ABC','20190214'),
('USER1@GMAIL','ABC','20190212'),
('USER1@GMAIL','ABC','20190206'),
('USER1@GMAIL','ABC','20190201'),
('USER2@GMAIL','CDE','20190111'),
('USER2@GMAIL','CDE','20190210'),
('USER2@GMAIL','CDE','20190202'),
('USER2@GMAIL','CDE','20190127'),
('USER3@GMAIL','EFG','20190213'),
('USER3@GMAIL','EFG','20190211'),
('USER3@GMAIL','EFG','20190208'),
('USER3@GMAIL','EFG','20190207')
12 rows affected
WITH CTE_1 AS ( SELECT Email USER_MAIL,
AccessDate ACCESS_DATE,
DENSE_RANK() OVER(ORDER BY DATEPART(WW,AccessDate) DESC) WEEK_NUMBER,
DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, AccessDate), DATEDIFF(dd, 0, AccessDate))) WEEK_END_DATE
FROM #TBL
WHERE DATEDIFF(WW,AccessDate,GETDATE()) >= 0 AND
DATEDIFF(WW,AccessDate,GETDATE()) <= 52
) ,CTE_2 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY USER_MAIL,WEEK_NUMBER ORDER BY ACCESS_DATE DESC) as ACCESS_ORDER FROM CTE_1 )
SELECT * FROM CTE_2 WHERE ACCESS_ORDER <= 2 ORDER BY USER_MAIL,WEEK_NUMBER,ACCESS_ORDER
| USER_MAIL | ACCESS_DATE | WEEK_NUMBER | WEEK_END_DATE | ACCESS_ORDER |
|---|---|---|---|---|
| USER1@GMAIL | 14/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 | 1 |
| USER1@GMAIL | 12/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 | 2 |
| USER1@GMAIL | 06/02/2019 00:00:00 | 2 | 09/02/2019 00:00:00 | 1 |
| USER1@GMAIL | 01/02/2019 00:00:00 | 3 | 02/02/2019 00:00:00 | 1 |
| USER2@GMAIL | 10/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 | 1 |
| USER2@GMAIL | 02/02/2019 00:00:00 | 3 | 02/02/2019 00:00:00 | 1 |
| USER2@GMAIL | 27/01/2019 00:00:00 | 3 | 02/02/2019 00:00:00 | 2 |
| USER2@GMAIL | 11/01/2019 00:00:00 | 4 | 12/01/2019 00:00:00 | 1 |
| USER3@GMAIL | 13/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 | 1 |
| USER3@GMAIL | 11/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 | 2 |
| USER3@GMAIL | 08/02/2019 00:00:00 | 2 | 09/02/2019 00:00:00 | 1 |
| USER3@GMAIL | 07/02/2019 00:00:00 | 2 | 09/02/2019 00:00:00 | 2 |
WITH CTE_1 AS ( SELECT Email USER_MAIL,
AccessDate ACCESS_DATE,
DENSE_RANK() OVER(ORDER BY DATEPART(WW,AccessDate) DESC) WEEK_NUMBER,
DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, AccessDate), DATEDIFF(dd, 0, AccessDate))) WEEK_END_DATE
FROM #TBL
WHERE DATEDIFF(WW,AccessDate,GETDATE()) >= 0 AND
DATEDIFF(WW,AccessDate,GETDATE()) <= 52
) ,CTE_2 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY USER_MAIL,WEEK_NUMBER ORDER BY ACCESS_DATE DESC) as ACCESS_ORDER FROM CTE_1 )
SELECT DISTINCT
USER_MAIL,
MIN(ACCESS_DATE) OVER(PARTITION BY USER_MAIL, WEEK_NUMBER)
FIRST_ACCESS_DATE, MAX(ACCESS_DATE) OVER(PARTITION BY USER_MAIL, WEEK_NUMBER) SECOND_ACCESS_DATE, WEEK_NUMBER, WEEK_END_DATE
FROM CTE_2
WHERE ACCESS_ORDER <= 2
ORDER BY USER_MAIL,WEEK_NUMBER
| USER_MAIL | FIRST_ACCESS_DATE | SECOND_ACCESS_DATE | WEEK_NUMBER | WEEK_END_DATE |
|---|---|---|---|---|
| USER1@GMAIL | 12/02/2019 00:00:00 | 14/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 |
| USER1@GMAIL | 06/02/2019 00:00:00 | 06/02/2019 00:00:00 | 2 | 09/02/2019 00:00:00 |
| USER1@GMAIL | 01/02/2019 00:00:00 | 01/02/2019 00:00:00 | 3 | 02/02/2019 00:00:00 |
| USER2@GMAIL | 10/02/2019 00:00:00 | 10/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 |
| USER2@GMAIL | 27/01/2019 00:00:00 | 02/02/2019 00:00:00 | 3 | 02/02/2019 00:00:00 |
| USER2@GMAIL | 11/01/2019 00:00:00 | 11/01/2019 00:00:00 | 4 | 12/01/2019 00:00:00 |
| USER3@GMAIL | 11/02/2019 00:00:00 | 13/02/2019 00:00:00 | 1 | 16/02/2019 00:00:00 |
| USER3@GMAIL | 07/02/2019 00:00:00 | 08/02/2019 00:00:00 | 2 | 09/02/2019 00:00:00 |