Get number of records for the week

Question

I'm trying to build an endpoint for a chart to fetch number of users that accessed a site for the current week, current month or a custom time duration. For the current week data, i have a query like

SELECT DAYOFWEEK(last_accessed) as week_day, COUNT(*) as active_users
FROM users WHERE WEEKOFYEAR(last_accessed) = WEEKOFYEAR(now()) AND
YEAR(last_accessed) = YEAR(now())
GROUP BY week_day
ORDER BY week_day

The problem is the grouping does not include days where the count is 0, i.e days where no user accessed the site. If i want to include this how do i do it? ,how can i structure this query properly to achieve this.

Answer 1

This is how i'd tackle it in SQL Server, so will need some work, but is a start.

with cteBase as
(
    select n from (values(0),(0)) as t(n)
)
select top 7 dates_table.day_all_week,count(u.user_id) as USERS_COUNT
from
(
    select dateadd(day, ROW_NUMBER() over (order by c.n)-1,
         cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date)) as day_all_week
    from ctebase as c,ctebase as c2,cteBase as c3
) as dates_table
left join #users as u on dates_table.day_all_week=u.last_accessed
group by dates_table.day_all_week
order by dates_table.day_all_week

Top 7 is a little crude, you can use where to select the dates required, currently it's set up to start on the first day of the week (cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date)). This can just be changed to a fixed date.

You can simplify the weekday dates to be like so. Leaving the tally table in means that you can expand quite easily to get months/years etc.

select dateadd(DD,n,(cast(DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) as date)))
from (values(0),(1),(2),(3),(4),(5),(6)) as t(n)