Finding the customers that are coming back in a week

Question

Here is my table:

 sessid  userid  date        prodcode 
 xxxxx   xx0101  01/01/2020  rpd032   
 xxxxx   xx2021  01/01/2020  xxxx01    
 xxxxx   xx0101  01/01/2020  xx0381    
 xxxxx   xxju23  02/01/2020  xxx023
 xxxxx   xxjp17  03/01/2020  xxx016
 xxxxx   xxju23  03/01/2020  xxxx03
 xxxxx   xx2021  04/01/2020  xxx023
 xxxxx   xxx270  05/01/2020  xxx023
 xxxxx   xx0j34  06/01/2020  rpd032
 xxxxx   xxcj02  07/01/2020  xxx333
 xxxxx   xxjr04  08/01/2020  rpd032

I want to run a query every week. I might just turn into a procedure later. For now, I want to know the number of customers coming back to the website for the week starting the 02/01/2020. As you can see from the sample above there is only one customer that is coming back (xxju23) so the result of my query should be 1 but I am struggling with it.

 select count(userid)
 from (
   select userid, count(*) as comingbak 
   from orders 
   where customers in dateadd(week,7,'02/01/2020') 
   groupby comingback
   having cominback > 1
 );

Answer 1

Based on GMB's comment. There are some following mistakes (Feel free to correct me if I'm mistaken):

1. Error syntax: No column name was specified for column 1 of 't'. https://ibb.co/52FBxMc
2. Condition in Where clause combines with having count(*) > 1 is wrong: You won't get any value >= '20200102'. It should be value >= '20200101'
3. You will get xx0101 as well. However, it should be excluded as back in the same day https://ibb.co/YtbCL1z
4. You should select userId or something like that instead of 1 as it makes confuse
5. Your condition just works in the time range 20200101 while it should be dynamic.

In short, the answer to @Phong might be more suitable.

Answer 2

Demo on db<>fiddle

You can use datepart(wk, date) to get week in a year.

;with t1 as ( -- Exclude customer comeback in the same date
select distinct userid, date
from #table1
),
t2 as (-- Get week in year 
select userid, 'Week ' + cast(datepart(wk, date) as varchar(2)) Week
from t1
)
select userid, Week, count(*) as numberOfVisit -- group by userId and week in year 
from t2
group by userid, Week
having count(*)  > 1

You can also Count all customer to get the last result.

;with t1 as (
select distinct userid, date
from #table1
),

t2 as (
select userid, 'Week ' + cast(datepart(wk, date) as varchar(2)) Week
from t1
),

t3 as (
select userid, Week, count(*) as numberOfVisit
from t2
group by userid, Week
having count(*)  > 1)

select count(*) Total
from t3

Answer 3

I understand that you are looking for the count of customers that had more than one visit in the website during the week that started on January 2nd.

Consider:

select count(*)
from (
    select 1
    from orders
    where date >= '20200102' and date < dateadd(week, 1, '20200102')
    group by userid
    having count(*) > 1
) t