User Login Activity Summary

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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')

Database Schema (Inferred)

Logins

Column Name	Example Value
id	1
user_id	1
login_date	2023-01-05
device	mobile

Example

Logins

id	user_id	login_date	device
1	1	2023-01-05	mobile
2	1	2023-01-05	desktop
3	1	2023-02-10	mobile
4	2	2023-01-07	desktop
5	2	2023-03-15	tablet
6	3	2023-03-20	mobile

Output

user_id	active_days	total_logins	last_login_date
1	2	3	2023-02-10
2	2	2	2023-03-15
3	1	1	2023-03-20

Explanation:

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.