LinkedIn SQL Interview Question | Using CTEs | Advanced SQL

In this video, we will solve a question that involves working with a table named job_listings, which contains job postings from various companies on LinkedIn. The objective is to write a SQL query that identifies and counts the number of companies that have posted duplicate job listings.

( Note: Duplicate job Listings are those job postings that are from the same company (company_id) that have identical title and description. )

Script --

CREATE TABLE job_listings (
company_id INT,
job_id INT,
title VARCHAR(255),
description TEXT
);
INSERT INTO job_listings (company_id, job_id, title, description) VALUES
(827, 248, 'Business Analyst', 'Business analyst evaluates past and current business data with the primary goal of improving decision-making processes within organisations.'),
(845, 149, 'Business Analyst', 'Business analyst evaluates past and current business data with the primary goal of improving decision-making processes within organisations.'),
(345, 945, 'Data Analyst', 'Data analyst reviews data to identify key insights into a business\'s customers and ways the data can be used to solve problems.'),
(345, 164, 'Data Analyst', 'Data analyst reviews data to identify key insights into a business\'s customers and ways the data can be used to solve problems.'),
(244, 172, 'Data Engineer', '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.');

TIMESTAMP
00:00 Introduction
00:12 Problem Description and Approach
02:52 Solution Implementation

[sachins]

wow linkedln is asking such simple question and someone made a video on this.

[prajju]

with cte_job as(
Select company_id, title, description, row_number() over(partition by company_id order by job_id) as job_count from job_listings
)
Select count(distinct company_id) as duplicate_companies from cte_job where job_count>1
This is also giving same result

[rajanchaurasiya]

with cte as(
select title, company_id
from job_listings
group by title, company_id
having count(*) > 1
)
select count(*) as "duplicate_companies"
from cte;

[tanujreddy]

SELECT
count(c)
FROM
(
SELECT
company_id,
description,
COUNT(*) c
FROM
job_listing
GROUP BY
company_id,
description
HAVING
COUNT(*) > 1
);

[JeevanC-lk]

with cte as(
select
company_id,
title,
description,
count(*) over(partition by company_id, title) as counts
from job_listings
)
select
count(distinct company_id) as duplicate_companies
from cte
where counts > 1;

[ajeetadarsh]

How to fetch previous date using today's date?

[architsrivastava]

Select count( Distinct company_id) as duplicate_companies
from(
Select *,
row_number() over(partition by company_id, title, description order by company_id) as rn
from job_listings) x
where rn>1
group by company_id;

[harshitsalecha]

WITH cte1 AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY company_id) as rn FROM job_listings)
SELECT c1.company_id
FROM cte1 as c1
INNER JOIN cte1 as c2
ON c1.company_id=c2.company_id AND c1.title=c2.title AND c1.description=c2.description AND c1.rn<c2.rn;