User Login Activity Summary
Preview mode. Log in to edit, run, submit, and save progress.
User Login Activity Summary
You are given a table of user logins. Each row records a login event with a user ID, date, and device type. A user may log in multiple times on the same day from different devices. Write a SQL query to find for each user: the number of distinct active days, the total number of login events, and the date of their most recent login. Return the result ordered by user_id. Table: Logins
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| user_id | INT | ID of the user |
| login_date | DATE | Date of the login event |
| device | VARCHAR | Device used ('mobile','desktop','tablet') |
Examples
User 1 logged in on 2 distinct dates (3 total events). COUNT(DISTINCT login_date) gives active days; COUNT(*) gives total events; MAX(login_date) gives the last login.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.