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;
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
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;
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
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
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;
Hi Nishtha i hpoe you doing good,it will helpful for people if you provide the script in every video.
wow linkedln is asking such simple question and someone made a video on this.
Toh mat dhek
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
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;
How to fetch previous date using today's date?
You can use lag function.
Select name, dates, lag(dates) over ( order by dates) as previous_dates from table_x
I think the question is not correctly framed
can you learn me sql, if yes please share class time and fees.
I can teach you
I can teach you from very scratch
SELECT
count(c)
FROM
(
SELECT
company_id,
description,
COUNT(*) c
FROM
job_listing
GROUP BY
company_id,
description
HAVING
COUNT(*) > 1
);
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
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