Challenge
Consider a table: UserLogins.
The UserLogins table has columns: user_id (int), login_date (date).
Write a SQL query to identify users who logged in every day in a specific week (from ‘2023-01-01’ to ‘2023-01-07’).
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.
Explanation
We select all logins that occurred between ‘2023-01-01’ and ‘2023-01-07’, group by the user_id and count the distinct login_dates for each user. If a user logged in every day in this week, the count of distinct login_dates would be 7. Therefore, we use HAVING COUNT(DISTINCT login_date) = 7 to filter for these users.