LinkedIn SQL Interview Question | Using CTEs | Advanced SQL

[@dharmiklingam1108]

Hi Nishtha i hpoe you doing good,it will helpful for people if you provide the script in every video.

[@sachins5660]

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

[@actualprogramming]

Toh mat dhek

[@prajju8114]

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

[@rashmikakrishnaswamy4926]

How to group description column when it is defined as text datatype? it throws error

[@JeevanC-l3k]

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;

[@theinsightminer08]

SELECT COUNT(DISTINCT company_id) AS duplicate_companies
FROM
(
SELECT
company_id,
COUNT(*) OVER(PARTITION BY company_id,title,description) as cnt
FROM job_listings
)
WHERE cnt>1 ;

[@Team_Kanya_raasi]

my approach ;
select job_id,title,count(company_id)as Duplicate_jobid from(
select * , ROW_NUMBER() over (partition by company_id order by job_id) as ranking from
job_listings
) x
where ranking = 2
group by job_id,title
correct me if i'm wrong
thanks

[@durgaprasadbammidi7425]

I think the question is not correctly framed

[@ajeetadarsh2871]

How to fetch previous date using today's date?

[@sonujack1]

You can use lag function.
Select name, dates, lag(dates) over ( order by dates) as previous_dates from table_x

[@rohitsingh3964]

can you learn me sql, if yes please share class time and fees.

[@MovieBuzz-uu8kp]

I can teach you

[@chandanpatra1053]

I can teach you from very scratch

[@architsrivastava6649]

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;

[@tanujreddy8597]

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

[@harshitsalecha221]

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

[@HARSHRAJ-wz2rp]

with cte as(
select job_listings.*,ROW_NUMBER()OVER(PARTITION BY company_id) as x1 FROM job_listings
)
select COUNT(DISTINCT company_id) as duplicate_companies FROM cte where x1>1;
Nishtha man it is request to you that if you found my solution as a right solution,plese give some reaction on my comment