Top Streamers

#2010

List the top 10 users who accumulated the most sessions where they had more streaming sessions than viewing. Return the user_id, number of streaming sessions, and number of viewing sessions.

table name: twitch_sessions


Solution:

select top 10
user_id,sum(case when session_type = 'streamer' then 1 else 0 end) as streaming,
sum(case when session_type = 'viewer' then 1 else 0 end) as viewer
from twitch_sessions
group by user_id
having  sum(case when session_type = 'streamer' then 1 else 0 end)>sum(case when session_type = 'viewer' then 1 else 0 end)
order by count(session_type) desc

Output:


SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[twitch_sessions]    Script Date: 12-01-2024 15:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[twitch_sessions](
	[user_id] [int] NULL,
	[session_start] [datetime] NULL,
	[session_end] [datetime] NULL,
	[session_id] [int] NULL,
	[session_type] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (0, CAST(N'2020-08-11T05:51:31.000' AS DateTime), CAST(N'2020-08-11T05:54:45.000' AS DateTime), 539, N'streamer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (2, CAST(N'2020-07-11T03:36:54.000' AS DateTime), CAST(N'2020-07-11T03:37:08.000' AS DateTime), 840, N'streamer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (3, CAST(N'2020-11-26T11:41:47.000' AS DateTime), CAST(N'2020-11-26T11:52:01.000' AS DateTime), 848, N'streamer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (1, CAST(N'2020-11-19T06:24:24.000' AS DateTime), CAST(N'2020-11-19T07:24:38.000' AS DateTime), 515, N'viewer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (2, CAST(N'2020-11-14T03:36:05.000' AS DateTime), CAST(N'2020-11-14T03:39:19.000' AS DateTime), 646, N'viewer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (0, CAST(N'2020-03-11T03:01:40.000' AS DateTime), CAST(N'2020-03-11T03:01:59.000' AS DateTime), 782, N'streamer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (0, CAST(N'2020-08-11T03:50:45.000' AS DateTime), CAST(N'2020-08-11T03:55:59.000' AS DateTime), 815, N'viewer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (3, CAST(N'2020-10-11T22:15:14.000' AS DateTime), CAST(N'2020-10-11T22:18:28.000' AS DateTime), 630, N'viewer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (1, CAST(N'2020-11-20T06:59:57.000' AS DateTime), CAST(N'2020-11-20T07:20:11.000' AS DateTime), 907, N'streamer')
GO
INSERT [dbo].[twitch_sessions] ([user_id], [session_start], [session_end], [session_id], [session_type]) VALUES (2, CAST(N'2020-07-11T14:32:19.000' AS DateTime), CAST(N'2020-07-11T14:42:33.000' AS DateTime), 949, N'viewer')
GO


Comments (0)
Questions List