Assume you are given the table below that shows
job posting for all companies on the LinkedIn platform. Write a query to get
the number of companies that have posted duplicate job listings.
Clarification:
Duplicate job listings refer to two jobs at the
same company with the same title and description.
table name: job_listings
Solution:
with cte as
(
select count(company_id) as c from job_listings group by company_id,title,description
having count(company_id)>1
)
select count(c) as
co_w_duplicate_jobs from cte
Output:
SQL Script:
CREATE TABLE [dbo].[job_listings](
[job_id]
[int] NOT NULL,
[company_id]
[int] NULL,
[title]
[nvarchar](150) NULL,
[description]
[nvarchar](max) NULL,
CONSTRAINT [PK_job_listings] PRIMARY KEY CLUSTERED
(
[job_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] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[job_listings] ([job_id], [company_id], [title], [description]) VALUES (149, 845, N'Business
Analyst', N'Business analyst evaluates past and current business data with the
primary goal of improving decision-making processes within organizations.')
GO
INSERT [dbo].[job_listings] ([job_id], [company_id], [title], [description]) VALUES (164, 345, N'Data Analyst', N'Data analyst
reviews data to identify key insights into a business''s customers and ways the
data can be used to solve problems.')
GO
INSERT [dbo].[job_listings] ([job_id], [company_id], [title], [description]) VALUES (172, 244, N'Data Engineer', N'Data engineer
works in a variety of settings to build systems that collect, manage, and
convert raw data into usable information for data scientists and business
analysts to interpret.')
GO
INSERT [dbo].[job_listings] ([job_id], [company_id], [title], [description]) VALUES (248, 827, N'Business
Analyst', N'Business analyst evaluates past and current business data with the
primary goal of improving decision-making processes within organizations.')
GO
INSERT [dbo].[job_listings] ([job_id], [company_id], [title], [description]) VALUES (945, 345, N'Data Analyst', N'Data analyst
reviews data to identify key insights into a business''s customers and ways the
data can be used to solve problems.')
GO