We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
WITH CTE1 AS
(
SELECT CONCAT(Name , '(', LEFT(Occupation,1) , ')') as name
FROM OCCUPATIONS
),
CTE2 as
(
SELECT * ,
Count(Occupation) over (Partition by Occupation) AS occ,
Row_Number() over(partition by Occupation order by Name) as rn
FROM OCCUPATIONS
),
CTE3 as
(
select Name, Occupation, Occ from CTE2
where rn =1
),
CTE4 AS
(
Select *, Row_Number() over(order by Occ,Occupation)as rn2 from CTE3
)
SELECT * from
CTE1
union
Select concat('There are a total of ', Occ ,' ' , lower(Occupation)+'s.') from
CTE4
order by 1
go
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
The PADS
You are viewing a single comment's thread. Return to all comments →
SET NOCOUNT ON;
WITH CTE1 AS ( SELECT CONCAT(Name , '(', LEFT(Occupation,1) , ')') as name FROM OCCUPATIONS ),
CTE2 as ( SELECT * , Count(Occupation) over (Partition by Occupation) AS occ, Row_Number() over(partition by Occupation order by Name) as rn FROM OCCUPATIONS ),
CTE3 as ( select Name, Occupation, Occ from CTE2 where rn =1 ), CTE4 AS ( Select *, Row_Number() over(order by Occ,Occupation)as rn2 from CTE3 )
SELECT * from CTE1 union Select concat('There are a total of ', Occ ,' ' , lower(Occupation)+'s.') from CTE4 order by 1
go