Least Popular Video

#TikTok

You have been asked to find the least popular video based on how many users have watched it.

Consider that a user can watch a video multiple times. Only the unique user views are counted.

In the case of a tie, output all the video ids of the least popular video(s).

table name: videos_watched

Solution:

select top 1 with ties video_id
from videos_watched
group by video_id
order by count(distinct user_id) 

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[videos_watched](
	[user_id] [varchar](50) NULL,
	[video_id] [varchar](50) NULL,
	[watched_at] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid004', CAST(N'2022-08-01T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid002', CAST(N'2022-08-01T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid003', CAST(N'2022-08-01T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid05', N'vid005', CAST(N'2022-08-01T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid004', CAST(N'2022-08-01T09:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid002', CAST(N'2022-08-01T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid003', CAST(N'2022-08-01T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid004', CAST(N'2022-08-01T13:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid001', CAST(N'2022-08-01T17:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid07', N'vid002', CAST(N'2022-08-01T20:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid08', N'vid003', CAST(N'2022-08-02T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid002', CAST(N'2022-08-02T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid08', N'vid005', CAST(N'2022-08-02T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid06', N'vid004', CAST(N'2022-08-02T14:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid09', N'vid002', CAST(N'2022-08-02T16:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid08', N'vid004', CAST(N'2022-08-02T17:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid003', CAST(N'2022-08-02T18:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid004', CAST(N'2022-08-02T22:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid002', CAST(N'2022-08-03T09:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid005', CAST(N'2022-08-03T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid001', CAST(N'2022-08-03T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid003', CAST(N'2022-08-04T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid05', N'vid004', CAST(N'2022-08-04T09:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid001', CAST(N'2022-08-04T13:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid05', N'vid003', CAST(N'2022-08-04T22:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid09', N'vid004', CAST(N'2022-08-05T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid07', N'vid002', CAST(N'2022-08-05T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid09', N'vid005', CAST(N'2022-08-05T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid07', N'vid003', CAST(N'2022-08-05T17:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid005', CAST(N'2022-08-10T06:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid001', CAST(N'2022-08-10T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid006', CAST(N'2022-08-10T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid005', CAST(N'2022-08-10T10:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid001', CAST(N'2022-08-10T12:30:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid003', CAST(N'2022-08-10T14:30:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid01', N'vid005', CAST(N'2022-08-10T17:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid006', CAST(N'2022-08-12T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid005', CAST(N'2022-08-12T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid05', N'vid003', CAST(N'2022-08-12T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid006', CAST(N'2022-08-12T13:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid002', CAST(N'2022-08-12T22:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid09', N'vid001', CAST(N'2022-08-13T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid002', CAST(N'2022-08-13T08:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid09', N'vid005', CAST(N'2022-08-13T15:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid004', CAST(N'2022-08-13T19:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid04', N'vid004', CAST(N'2022-08-13T22:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid08', N'vid002', CAST(N'2022-08-14T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid02', N'vid006', CAST(N'2022-08-15T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid03', N'vid007', CAST(N'2022-08-15T08:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid07', N'vid003', CAST(N'2022-08-15T14:00:00.000' AS DateTime))
GO
INSERT [dbo].[videos_watched] ([user_id], [video_id], [watched_at]) VALUES (N'uid10', N'vid001', CAST(N'2022-08-15T20:00:00.000' AS DateTime))
GO

Comments (0)
Questions List