Finding unique number of logins from a database based on two columns-CodePudding

I have a database with information of when users have logged into my app. There are two columns -

User ID
Log in date (YYYY/MM/DD)

Each user can have multiple logins in one day.

I want to find the number of unique day logins for each user. For example, if a user logs in five times on 10th October, 2022 and he logs in ten times on 11th October, 2022, and 0 times every other day, then I want the value of '2' next to his id, because he logged in a total of two unique days.

Please note that I need to this using only one file (no intermediate saved files) and I cannot use a CET either.

Thanks!

CodePudding user response：

You can count the distinct dates per user:

SELECT UserID, COUNT(DISTINCT LogInDate) 
FROM myTable
GROUP BY UserID

CodePudding user response：

Use a subquery to reduce the individual login rows to the days a user logged in, then use an aggregate query to count them. Like so.

SELECT UserID, COUNT(*) NumberOfDaysLoggedIn
  FROM (SELECT UserID, LogInDate FROM myTable GROUP BY UserID, LogInDate)
 GROUP BY UserID

The subquery to generate one row per user per date is

SELECT UserID, LogInDate FROM myTable GROUP BY UserID, LogInDate

but you could also use DISTINCT for the purpose. They do the same thing.

SELECT DISTINCT UserID, LogInDate FROM myTable

This happens to be one of the few queries involving dates that will work on all makes and versions of table server.