How to count weekly single accesses in Mysql

Question

I have this query in Mysql to check the unique accesses to a system of mine:

SELECT COUNT(DISTINCT id_usuario) AS total, dia FROM acesso
WHERE (dia BETWEEN '2018-01-21' AND '2019-01-21')
GROUP BY dia
ORDER BY dia ASC

And the way I see it, he’s listing the unique access to the system.

My doubt: the customer asked to check the weekly single accesses, IE, if the user access at least 1 only time during the period of 1 week, would already count his access, how to do? I tried this way, but the total ends up giving less than the only daily access:

SELECT COUNT(DISTINCT id_usuario) AS total, dia FROM acesso
WHERE (dia BETWEEN '2018-01-21' AND '2019-01-21')
GROUP BY WEEK(dia)
ORDER BY dia ASC

Answer 1

If you do this query, you will create an analytical table with the distinct ones in a given week, filtering by date range

SELECT id_usuario, week(dia) as semana FROM acesso
WHERE dia  BETWEEN '2019-01-01' and '2019-04-16'
group by id_usuario, week(dia)

Based on these results, you can count the distinct ones in this time interval

SELECT count(distinct id_usuario) as total from (SELECT id_usuario, week(dia) as semana FROM acesso
WHERE dia  BETWEEN '2019-01-01' and '2019-04-16'
group by id_usuario, week(dia)) T

Answer 2

If you want to know only the total of accesses in the last 7 days, you can do so:

SELECT COUNT(DISTINCT id_usuario) AS total FROM acesso
WHERE dia  >= (DATE(NOW()) - INTERVAL 7 DAY)