Find the top 5 states with the most 5 star businesses. Output the state name along with the number of 5-star businesses and order records by the number of 5-star businesses in descending order. In case there are ties in the number of businesses, return all the unique states. If two states have the same result, sort them in alphabetical order.
table name:yelp_business
Solution:
with cte as
(
select rank() over(order by count(stars) desc) as rnk , count(stars) as stars,state from yelp_business1
where stars=5
group by state
)
select stars,state from cte where rnk<=5 order by rnk, state
Output:
SQL Script:
CREATE TABLE [dbo].[yelp_business1](
[business_id] [varchar](50) NOT NULL,
[name] [varchar](50) NULL,
[neighborhood] [varchar](50) 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_business1] 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