Top 2 Users With Most Calls

Return the top 2 users in each company that called the most. Output the company_id, user_id, and the user's rank. If there are multiple users in the same rank, keep all of them.

table : rc_users


table : rc_calls


Solution:

with cte as
(
select r.company_id, r.user_id,count(r.user_id) as noOfCalls,
dense_rank() over (partition by r.company_id order by count(r.user_id) desc) as rnk
from rc_users as r
join rc_calls as c on c.user_id = r.user_id
group by r.company_id,r.user_id
)
select company_id,user_id,rnk from cte 
where rnk in (1,2)

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[rc_calls](
	[user_id] [int] NULL,
	[date] [datetime] NULL,
	[call_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[rc_users](
	[user_id] [int] NULL,
	[status] [varchar](50) NULL,
	[company_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1218, CAST(N'2020-04-19T01:06:00.000' AS DateTime), 0)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1554, CAST(N'2020-03-01T16:51:00.000' AS DateTime), 1)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1857, CAST(N'2020-03-29T07:06:00.000' AS DateTime), 2)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1525, CAST(N'2020-03-07T02:01:00.000' AS DateTime), 3)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1271, CAST(N'2020-04-28T21:39:00.000' AS DateTime), 4)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1181, CAST(N'2020-03-18T04:49:00.000' AS DateTime), 5)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1950, CAST(N'2020-04-12T23:57:00.000' AS DateTime), 6)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1339, CAST(N'2020-04-11T02:15:00.000' AS DateTime), 7)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1910, CAST(N'2020-03-21T08:56:00.000' AS DateTime), 8)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1093, CAST(N'2020-03-07T15:47:00.000' AS DateTime), 9)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1859, CAST(N'2020-04-25T13:55:00.000' AS DateTime), 10)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1079, CAST(N'2020-04-17T16:38:00.000' AS DateTime), 11)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1519, CAST(N'2020-04-15T12:14:00.000' AS DateTime), 12)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1854, CAST(N'2020-04-25T19:59:00.000' AS DateTime), 13)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1968, CAST(N'2020-03-16T21:19:00.000' AS DateTime), 14)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1891, CAST(N'2020-03-30T23:11:00.000' AS DateTime), 15)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1575, CAST(N'2020-03-14T15:21:00.000' AS DateTime), 16)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1162, CAST(N'2020-04-06T18:39:00.000' AS DateTime), 17)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1503, CAST(N'2020-04-01T18:31:00.000' AS DateTime), 18)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1884, CAST(N'2020-04-08T08:44:00.000' AS DateTime), 19)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1854, CAST(N'2020-03-10T10:04:00.000' AS DateTime), 20)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1525, CAST(N'2020-03-04T14:44:00.000' AS DateTime), 21)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1181, CAST(N'2020-03-02T17:07:00.000' AS DateTime), 22)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1503, CAST(N'2020-03-29T11:17:00.000' AS DateTime), 23)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1859, CAST(N'2020-04-11T14:26:00.000' AS DateTime), 24)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1859, CAST(N'2020-03-13T23:52:00.000' AS DateTime), 25)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1859, CAST(N'2020-04-10T00:41:00.000' AS DateTime), 26)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1854, CAST(N'2020-03-28T00:35:00.000' AS DateTime), 27)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1891, CAST(N'2020-04-27T22:09:00.000' AS DateTime), 28)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1181, CAST(N'2020-04-19T06:39:00.000' AS DateTime), 29)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1525, CAST(N'2020-04-15T22:27:00.000' AS DateTime), 30)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1093, CAST(N'2020-03-17T15:21:00.000' AS DateTime), 31)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1857, CAST(N'2020-04-03T02:00:00.000' AS DateTime), 32)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1884, CAST(N'2020-03-20T14:41:00.000' AS DateTime), 33)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1950, CAST(N'2020-03-17T11:17:00.000' AS DateTime), 34)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1162, CAST(N'2020-04-15T03:38:00.000' AS DateTime), 35)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1162, CAST(N'2020-03-08T06:47:00.000' AS DateTime), 36)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1891, CAST(N'2020-04-22T01:46:00.000' AS DateTime), 37)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1554, CAST(N'2020-04-08T05:35:00.000' AS DateTime), 38)
GO
INSERT [dbo].[rc_calls] ([user_id], [date], [call_id]) VALUES (1910, CAST(N'2020-03-11T08:33:00.000' AS DateTime), 39)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1218, N'free', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1554, N'inactive', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1857, N'free', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1525, N'paid', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1271, N'inactive', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1181, N'inactive', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1950, N'free', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1339, N'free', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1910, N'free', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1093, N'paid', 3)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1859, N'free', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1079, N'paid', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1519, N'inactive', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1854, N'paid', 1)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1968, N'inactive', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1891, N'paid', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1575, N'free', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1162, N'paid', 2)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1503, N'inactive', 3)
GO
INSERT [dbo].[rc_users] ([user_id], [status], [company_id]) VALUES (1884, N'free', 1)
GO


Comments (0)
Questions List