Show unique number of users

RXR 121 Reputation points
2020-10-15T01:43:13.047+00:00

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
32458-image.png

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  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,224 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,513 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-15T04:28:58.857+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-15T03:03:24.37+00:00

    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.

    0 comments No comments