Write an SQL query to report the first login date
for each player. Return the result table in any order.
table name: Activity
Question Explanation:
We have given table name activity, in which for each player_id there can be multiple records. Our task is to find the first event_date for each player.
Input:
Solution Explanation:
Use group by on player_id which will divide rows on different group for each unique player_id.
select player_id from Activity group by player_id
Use min() function to get earliest date for each player.
min(event_date) as first_login
Solution:
select player_id, min(event_date) as first_login from Activity group by player_id
Output:
SQL Script:
CREATE TABLE [dbo].[Activity](
[player_id][int] NULL,
[device_id][int] NULL,
[event_date][date] NULL,
[games_played][int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Activity] ([player_id], [device_id], [event_date], [games_played]) VALUES (1, 2, CAST(N'2016-03-01' AS Date), 5)
GO
INSERT [dbo].[Activity] ([player_id], [device_id], [event_date], [games_played]) VALUES (1, 2, CAST(N'2016-05-02' AS Date), 6)
GO
INSERT [dbo].[Activity] ([player_id], [device_id], [event_date], [games_played]) VALUES (2, 3, CAST(N'2017-06-25' AS Date), 1)
GO
INSERT [dbo].[Activity] ([player_id], [device_id], [event_date], [games_played]) VALUES (3, 1, CAST(N'2016-03-02' AS Date), 0)
GO
INSERT [dbo].[Activity] ([player_id], [device_id], [event_date], [games_played]) VALUES (3, 4, CAST(N'2018-07-03' AS Date), 5)
GO