Show unique number of users

Question

Hi @MelissaMa-MSFT
I'm trying get unique number of visitors, Here is something wrong . In front of ID = 1 same date it should be LastVisit

Create Table Sample2 (VisitorID int,Datetime datetime)  
Insert into Sample2   
Values (170906,'2017-12-24 12:00:50.320'),  
       (170906,'2017-12-26 10:53:27.440'),  
       (170906,'2017-12-26 10:53:27.440'),  
       (170906,'2017-12-28 09:15:07.877'),  
       (170906,'2017-12-29 09:45:29.893'),  
       (1370,'2017-11-20 15:57:04.000'),  
       (1370,'2017-11-21 15:30:42.000'),  
       (1,'2017-12-30 20:40:18.813');  
---  
Declare @begin int = 1;  
Create Table #Temp(VisitorID int,VisitDate date)  
While(@begin < 5)  
Begin  
Insert into #Temp  
SELECT distinct VisitorId,CAST(DATEADD(DAY, -1, Datetime) AS DATE)  
From Sample2  
  
  
  
Set @begin +=1  
END  
  
;with visits as (  
 SELECT distinct VisitorID, VisitDate, ROW_NUMBER() OVER (  
  PARTITION BY VisitorID  
  ORDER BY VisitDate desc  
  ) as rownum  
  FROM #Temp  
)  
  
SELECT VisitorID,  
 VisitDate,  
CASE   
When rownum = 1 THEN 'LastVisit'  
WHEN rownum = 2 THEN 'SecondLast'   
When rownum = 3 Then 'ThirdLast'  
When rownum = 4 Then 'FourthLast'  
  
ELSE 'Unknown' END as Which_Visit  
FROM visits  
  
WHERE rownum = 4 or rownum = 3 or rownum = 2 or rownum = 1   
order by VisitorID desc  

Answer 1

Hi @RXR ,

You could also refer below with least modification based on your query.

with visits as (  
   SELECT distinct VisitorID, CONVERT(date, Datetime) VDate, ROW_NUMBER() OVER (  
   PARTITION BY VisitorID  
   ORDER BY CONVERT(date, Datetime) desc  
   ) as rownum  
   FROM Sample2  
   Group by VisitorID,CONVERT(date, Datetime)  
 )  
      
      
 SELECT distinct VisitorID,  
 VDate,  
 CASE   
 When rownum = 1 Then 'LastVisit'  
 WHEN rownum = 2 THEN 'SecondLast'   
 When rownum = 3 Then 'ThirdLast'  
 When rownum = 4 Then 'FourthLast'  
      
 ELSE 'Unknown' END as Which_Visit  
 FROM visits  
 WHERE rownum = 4 or rownum = 3 or rownum = 2 or rownum = 1   

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @RXR ,

Please refer below query and check whether it is working.

drop table if exists #Temp  
  
 ;with visits as (  
   SELECT distinct VisitorID, Cast(Datetime as Date) VDate  
   FROM Sample2  
 )  
 ,visitnum as (  
 SELECT distinct VisitorID, VDate, ROW_NUMBER() OVER (  
   PARTITION BY VisitorID  
   ORDER BY VDate desc    
   ) as rownum  
   FROM visits )  
      
 SELECT distinct VisitorID,  
 VDate,  
 CASE   
 When rownum = 1 Then 'LastVisit'  
 WHEN rownum = 2 THEN 'SecondLast'   
 When rownum = 3 Then 'ThirdLast'  
 When rownum = 4 Then 'FourthLast'  
 ELSE 'Unknown' END as Which_Visit  
 FROM visitnum  
 WHERE rownum <5  

Output:

VisitorID	VDate	Which_Visit  
1	2017-12-30	LastVisit  
1370	2017-11-21	LastVisit  
1370	2017-11-20	SecondLast  
170906	2017-12-29	LastVisit  
170906	2017-12-28	SecondLast  
170906	2017-12-26	ThirdLast  
170906	2017-12-24	FourthLast  
989762	2020-10-15	LastVisit  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.