tsql - SQL query to get list of active customers. Active are those that are in the table at least once each week -

i have table contains usage data our customers - customerid, dateofusage. day, same customerid may occur multiple times.

i want figure out list of active customers. active have used product @ least once each week.

when use :

    select distinct [customerid], datepart(week, [date]) customerusage     group [subscriptionid] 

i list of customerids , week used in. try extend doing this:

    select distinct [customerid], count(datepart(week, [date])) customerusage     group [subscriptionid] 

but numbers count messed up. hoping number of weeks customer active in , if number greater number of weeks far, have list. idea doing wrong?

you need add distinct before datepart ensure count distinct days customer present in records. query posted counts every row customer present.

select distinct [customerid]      , count(distinct datepart(week, [date]))  customerusage group [subscriptionid]