CODEWITHSUNDEEP
mysql
amarincolas
amarincolas

Reputation: 141

Calculate the number of regular users in mysql

Given the table showed in the picture, I want to calculate the number of users who have dates far in more than one day. Basically the problem is to calculate the number of regular visitors.

table

For example: The user adrian@ have 3 timestamps, 2 of them in the same day and the other one 2 days after, so this user came back. Instead, the user david@ only have 2 timestamps (in the same day), that means this user didn't come back. Any ideas?

Upvotes: 1

Views: 49

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT usuario_email
FROM users
GROUP BY usuario_email
HAVING COUNT(DISTINCT DATE(fecha)) > 1

The above will select users having visited your site in 2 or more different dates, hence it will select only adrian@ based on your sample data.

Demo here

Upvotes: 2

Related Questions