CODEWITHSUNDEEP
sqlclickhouse
Jens
Jens

Reputation: 291

How to get users which were online everyday last week?

Data example:

id  visiting_time
1   13.01.2001 02:34
1   14.01.2001 02:36
1   15.01.2001 02:36
1   16.01.2001 02:37
1   17.01.2001 02:38
1   18.01.2001 02:39
1   19.01.2001 02:40
2   13.01.2001 02:35
2   15.01.2001 02:36
2   16.01.2001 02:37
2   17.01.2001 02:38
2   18.01.2001 02:39
2   19.01.2001 02:40

I want to get all users which were online everyday for the last week, f.e. from 13th january 00:00 till 20th january 00:00. For my data sample the answer is:

id
1

Upvotes: 0

Views: 88

Answers (2)

simPod
simPod

Reputation: 13456

Considered

everyday for the last week, f.e. from 13th january 00:00 till 20th january 00:00

and

I point it out myself. In general, I can choose any number of days I want.

I guess it works only as a filter so the task is "find users online everyday during selected interval

SELECT id,
       count(DISTINCT toDate(visiting_time)) AS number_of_days_visited
FROM user_visits
WHERE visiting_time BETWEEN '2001-01-13 00:00:00' AND '2001-01-20 00:00:00'
GROUP BY id
HAVING number_of_days_visited =
       round((toUInt32(toDateTime('2001-01-20 00:00:00')) - toUInt32(toDateTime('2001-01-13 00:00:00'))) / 60 / 60 / 24)

In HAVING I computed number of days from the WHERE filter.

Upvotes: 1

forpas
forpas

Reputation: 164089

The below code will work only if the visiting_time column format is YYYY-MM-DD HH:MM, otherwise the dates are not comparable:

SELECT t.id FROM (SELECT id, COUNT(DISTINCT substr(visiting_time, 1, 10)) AS counter From table1 WHERE ((visiting_time >= '2001-01-13 00:00' AND visiting_time < '2001-01-20 00:00')) GROUP BY id) AS t WHERE t.counter=7

Upvotes: 1

Related Questions