Search ⌘K
I
IshaSQL

Foundations Track · Easy · 15 min

The Latest Login in 2020

Table: Logins +----------------+----------+ | Column Name | Type | +----------------+----------+ | user_id | int | | time_stamp | datetime | +---------------...

Foundations Track
Easy
15 min
aggregation
filtering
date functions

Company labels are directional practice context, not official interview guidance.

Timer 00:00
Back to practice

Objective

Practice aggregation through a IshaSQL-tagged business scenario.

Approach

Use this track to lock in clean query structure, basic filtering logic, and confidence with grouped output.

Company context

Company labels are directional practice context, not official interview guidance.

Prereq: query basics
Prereq: filtering

Table: Logins +----------------+----------+ | Column Name | Type | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ (user_id, time_stamp) is the primary key (combination of columns with unique values) for this table. Each row contains information about the login time for the user with ID user_id. Write a solution to report the latest login for all users in the year 2020 . Do not include the users who did not login in 2020 . Return the result table in any order . The result format is in the following example. Example 1: Input: Logins table: +---------+---------------------+ | user_id | time_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 6 | 2021-04-21 14:06:06 | | 6 | 2019-03-07 00:18:15 | | 8 | 2020-02-01 05:10:53 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | | 2 | 2019-08-25 07:59:08 | | 14 | 2019-07-14 09:00:00 | | 14 | 2021-01-06 11:59:59 | +---------+---------------------+ Output: +---------+---------------------+ | user_id | last_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | +---------+---------------------+ Explanation: User 6 logged into their account 3 times but only once in 2020, so we include this login in the result table. User 8 logged into their account 2 times in 2020, once in February and once in December. We include only the latest one (December) in the result table. User 2 logged into their account 2 times but only once in 2020, so we include this login in the result table. User 14 did not login in 2020, so we do not include them in the result table.

Logins
user_id INT
time_stamp TIMESTAMP
PRIMARY KEY (user_id
time_stamp
Editor
SQL workspace

Run queries against the protected question data, then submit once the result shape looks right.

Sign in to run SQL

Create a free account or sign in before running queries against protected question data.