Ranking Hosts By Beds

Rank each host based on the number of beds they have listed. The host with the most beds should be ranked 1 and the host with the least number of beds should be ranked last. Hosts that have the same number of beds should have the same rank but there should be no gaps between ranking values. A host can also own multiple properties. Output the host ID, number of beds, and rank from highest rank to lowest.

table name: airbnb_apartments

Solution:

select dense_rank() over (order by sum(n_beds) desc) as rnk, host_id, sum(n_beds) as n_beds from airbnb_apartments
group by host_id

Output:


SQL Script:

USE [StrataScratch]
CREATE TABLE [dbo].[airbnb_aprtments](
	[host_id] [int] NOT NULL,
	[apartment_id] [varchar](50) NULL,
	[apartment_type] [varchar](50) NULL,
	[n_beds] [int] NULL,
	[n_bedrooms] [int] NULL,
	[country] [varchar](50) NULL,
	[city] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (0, N'A1', N'Room', 1, 1, N'USA', N'New York')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (0, N'A2', N'Room', 1, 1, N'USA', N'New Jersey')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (0, N'A3', N'Room', 1, 1, N'USA', N'New Jersey')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (1, N'A4', N'Apartment', 2, 1, N'USA', N'Houston')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (1, N'A5', N'Apartment', 2, 1, N'USA', N'Las Vegas')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (2, N'A6', N'Yurt', 3, 1, N'Mongolia', N'-')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (3, N'A7', N'Penthouse', 3, 3, N'China', N'Tianjin')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (3, N'A8', N'Penthouse', 5, 5, N'China', N'Beijing')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (4, N'A9', N'Apartment', 2, 1, N'Mali', N'Bamako')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (5, N'A10', N'Room', 3, 1, N'Mali', N'Segou')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (5, N'A11', N'Room', 2, 1, N'Mali', N'Segou')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (6, N'A12', N'Penthouse', 6, 6, N'Luxembourg', N'Luxembourg')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (7, N'A13', N'Room', 4, 1, N'Luxembourg', N'Luxembourg')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (8, N'A14', N'Apartment', 2, 1, N'Australia', N'Perth')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (9, N'A15', N'Apartment', 2, 1, N'Australia', N'Perth')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (9, N'A16', N'Apartment', 2, 1, N'Australia', N'Perth')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (10, N'A17', N'Room', 4, 1, N'Brazil', N'Rio De Janeiro')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (10, N'A18', N'Room', 4, 1, N'Argentina', N'Mendoza')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (10, N'A19', N'Room', 4, 2, N'Uruguay', N'Mercedes')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (10, N'A20', N'Room', 4, 2, N'Brazil', N'Brasilia')
GO
INSERT [dbo].[airbnb_aprtments] ([host_id], [apartment_id], [apartment_type], [n_beds], [n_bedrooms], [country], [city]) VALUES (11, N'A21', N'Apartment', 2, 2, N'Mexico', NULL)
GO


Comments (0)
Questions List