Reviews of Categories

Find the top business categories based on the total number of reviews. Output the category along with the total number of reviews. Order by total reviews in descending order.

table name: yelp_business

CREATE TABLE [dbo].[yelp_business](
	[business_id] [varchar](50) NOT NULL,
	[name] [varchar](550) NULL,
	[neighborhood] [varchar](550) NULL,
	[address] [varchar](550) NULL,
	[city] [varchar](50) NULL,
	[state] [varchar](50) NULL,
	[postal_code] [varchar](50) NULL,
	[latitude] [float] NULL,
	[longitude] [float] NULL,
	[stars] [float] NULL,
	[review_count] [int] NULL,
	[is_open] [int] NULL,
	[categories] [varchar](550) NULL,
 CONSTRAINT [PK_yelp_business] PRIMARY KEY CLUSTERED 
(
	[business_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Solution:
select v.value,sum(y.review_count)
from yelp_business as y
cross apply string_split(y.categories,';') as v
group by v.value
order by sum(y.review_count) desc

Output (first 5 rows):


Comments (0)
Questions List