Flags per Video

For each video, find how many unique users flagged it. A unique user can be identified using the combination of their first name and last name. Do not consider rows in which there is no flag ID.

table name: user_flags


Solution:

select video_id, count(distinct concat(user_firstname,' ',user_lastname)) as cnt
from user_flags
where flag_id is not null
group by video_id

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[user_flags](
	[user_firstname] [varchar](50) NULL,
	[user_lastname] [varchar](50) NULL,
	[video_id] [varchar](50) NULL,
	[flag_id] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Richard', N'Hasson', N'y6120QOlsfU', N'0cazx3')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'May', N'Ct6BUPvE2sM', N'1cn76u')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Gina', N'Korman', N'dQw4w9WgXcQ', N'1i43zk')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'May', N'Ct6BUPvE2sM', N'1n0vef')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'May', N'jNQXAC9IVRw', N'1sv6ib')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Gina', N'Korman', N'dQw4w9WgXcQ', N'20xekb')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'May', N'5qap5aO4i9A', N'4cvwuv')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Daniel', N'Bell', N'5qap5aO4i9A', N'4sd6dv')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Richard', N'Hasson', N'y6120QOlsfU', N'6jjkvn')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Pauline', N'Wilks', N'jNQXAC9IVRw', N'7ks264')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Courtney', NULL, N'dQw4w9WgXcQ', NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Helen', N'Hearn', N'dQw4w9WgXcQ', N'8946nx')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'Johnson', N'y6120QOlsfU', N'8wwg0l')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Richard', N'Hasson', N'dQw4w9WgXcQ', N'arydfd')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Gina', N'Korman', NULL, NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mark', N'Johnson', N'y6120QOlsfU', N'bl40qw')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Richard', N'Hasson', N'dQw4w9WgXcQ', N'ehn1pt')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Lopez', N'dQw4w9WgXcQ', N'hucyzx', NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Greg', NULL, N'5qap5aO4i9A', NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Pauline', N'Wilks', N'jNQXAC9IVRw', N'i2l3oo')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Richard', N'Hasson', N'jNQXAC9IVRw', N'i6336w')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Johnson', N'y6120QOlsfU', N'iey5vi', NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'William ', N'Kwan', N'y6120QOlsfU', N'kktiwe')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Ct6BUPvE2sM', NULL, NULL, NULL)
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Loretta', N'Crutcher', N'y6120QOlsfU', N'nkjgku')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Pauline', N'Wilks', N'jNQXAC9IVRw', N'ov5gd8')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Mary', N'Thompson', N'Ct6BUPvE2sM', N'qa16ua')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Daniel', N'Bell', N'5qap5aO4i9A', N'xciyse')
GO
INSERT [dbo].[user_flags] ([user_firstname], [user_lastname], [video_id], [flag_id]) VALUES (N'Evelyn', N'Johnson', N'dQw4w9WgXcQ', N'xvhk6d')
GO


Comments (0)
Questions List