SQL query to find out number of days in a week a user visited

Question

I'd like to find out how many days in a week users have visited my site. For example, 1 day in a week, 2 days in a week, every day of the week (7).

I imagine the easiest way of doing this would be to set the date range and find out the number of days within that range (option 1). However, ideally I'd like the code to understand a week so I can run a number of weeks in one query (option 2). I'd like the users to be unique for each number of days (ie those who have visited 2 days have also visited 1 day but would only be counted in the 2 days row)

In my database (using SQLWorkbench64) I have user ids (id) and date (dt)

I'm relatively new to SQL so any help would be very much appreciated!!

Expected results (based on total users = 5540):

Option 1:

Number of Days     Users
1                  2000
2                  1400
3                  1000
4                  700
5                  300
6                  100
7                  40

Option 2:

Week Commencing   Number of Days     Users
06/05/2019        1                  2000
06/05/2019        2                  1400
06/05/2019        3                  1000
06/05/2019        4                  700
06/05/2019        5                  300
06/05/2019        6                  100
06/05/2019        7                  40

It looks like you're after the number of visitors per week, instead of numbers being based on the activity of a single user (as you used the term "a user visited" repeatedly). Can you please clarify this? — Dai, May 14 '19 at 16:27
Hi @Dai, thanks for your comment. I've updated to mention 'users', rather than 'a user'. I hope this helps, please let me know if you still think it's unclear though! — Beckyp, May 14 '19 at 16:35
Generally, this is something you would want a date table for, with a row for each day and any attributes you want (like week of year). You don't say what RDBMS you're using, but here's an [excellent example](https://stackoverflow.com/a/5635628/2751573) of how to create one in SQL Server. — Andrew, May 14 '19 at 16:49

score 0 · Answer 1 · answered May 14 '19 at 17:21

You can find visitor count between a date range with below script. Its also consider if a visitor visits multi days in the given date range, s/he will be counted for the latest date only from the range-

Note: Dates are used as sample in the query.

SELECT date,COUNT(id) 
FROM
(
    SELECT id,max(date) date
    FROM your_table
    WHERE date BETWEEN '04/21/2019' AND '04/22/2019'
    GROUP BY ID
)A
GROUP BY date

score 0 · Answer 2 · answered May 14 '19 at 18:25

You can find the Monday of the week of a date and then group by that. After you have the week day there is a series of group by. Here is how I did this:

DECLARE @table TABLE
(
    id INT,
    date DATETIME,
    MondayOfWeek DATETIME
)

DECLARE @info TABLE
(
    CommencingWeek DATETIME,
    NumberOfDays INT,
    Users INT
)

INSERT INTO @table (id,date) VALUES
(1,'04/15/2019'), (2,'07/21/2018'), (3,'04/16/2019'), (4,'04/16/2018'), (1,'04/16/2019'), (2,'04/17/2019')

UPDATE @table
SET MondayOfWeek = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, date) - 6, date)), 101)

INSERT INTO @info (CommencingWeek,NumberOfDays) 
SELECT MondayOfWeek, NumberDaysInWeek FROM 
(
    SELECT id,MondayOfWeek,COUNT(*) AS NumberDaysInWeek FROM @table
    GROUP BY id,MondayOfWeek
) T1

SELECT CommencingWeek,NumberOfDays,COUNT(*) AS Users FROM @info
GROUP BY CommencingWeek,NumberOfDays
ORDER BY CommencingWeek DESC

Here is the output from my query:

CommencingWeek  NumberOfDays    Users
2019-04-14 00:00:00.000    1    2
2019-04-14 00:00:00.000    2    1
2018-07-15 00:00:00.000    1    1
2018-04-15 00:00:00.000    1    1

SQL query to find out number of days in a week a user visited

2 Answers2