SQL INTERVIEW QUESTION - Return SalesID which are not part of multiple salestypes

DML Script: datasculptor.b...
Feedback: forms.gle/NQuC...
The Data Sculptor KZbin channel is a dedicated platform that explores the art and science of working with data. This channel caters to a diverse audience, including data enthusiasts, analysts, scientists, and anyone interested in harnessing the power of data for insightful and creative purposes.
The channel covers a wide range of topics related to data manipulation, analysis, visualization, and interpretation. The term "Data Sculptor" emphasizes the creative aspect of working with data, suggesting that it's not just about raw numbers but also about crafting meaningful stories and insights from the information available.
Viewers can expect a variety of content, including tutorials on popular data analysis tools and programming languages, discussions on emerging trends in the data field, case studies showcasing innovative data applications, and tips for effective data communication. The Data Sculptor aims to demystify complex concepts, making data-related skills accessible to both beginners and experienced professionals.
Whether you're a student looking to enter the field of data science, a business professional seeking to enhance your analytical skills, or simply someone curious about the world of data, the Data Sculptor KZbin channel strives to provide engaging and informative content to help you navigate the exciting realm of data exploration and analysis.
Playlists:
SQL Interview Questions: • SQL Interview questions
Recursive CTE: • Recursive CTE
Power BI: • Power BI and DAX
Data Modeling: • Data Modelling
Generative AIs: • Generative AIs
Excel: • Excel
Follow me
Linkedin: / data-sculptor-93a00b2a8
Instagram: / datasculptor2895
Quora: datasculptorss...
BuyMeACoffee: buymeacoffee.c...

[@VARUNTEJA73]

with cte as (select distinct salesrep_id,sales_type,dense_rank()over(partition by salesrep_id order by sales_type)ranks from orders) select salesrep_id ,count(ranks)as counts from cte group by salesrep_id having count(ranks)=1

[@bhavanib2951]

Nice approach babu. Keep going babu

[@datasculptor2895]

I will try my best

[@DB-bk4bf]

Good question.

[@vijaygupta7059]

SELECT salesRepID FROM Orders group by salesRepID having count(distinct SalesType)=1;

[@monasanthosh9208]

Select salesrepid from (Select *,count(Salesrepid) as CNTfrom (Select * from (Select SalesrepId,row_Number() over (Partition by Salesrepid,SalesType) as RN from Orders)N where RN = 1) group by Salesrepid)N where CNT=1

[@vijay.s-ll1yq]

3rd methods using correlated subquery select salesrep from orders A where salestype ='International' and not exists (select salesrep from orders b where a.salesrep =b.salesrep and salestype ='Domestic') union select salesrep from orders A where salestype ='Domestic' and not exists (select salesrep from orders b where a.salesrep =b.salesrep and salestype ='International')

[@vijay.s-ll1yq]

2nd method select * from (select salesrep from orders where salestype ='International' except select salesrep from orders where salestype ='Domestic') A union SELECT * FROM (select salesrep from orders where salestype ='Domestic' except select salesrep from orders where salestype ='International' ) B

[@vijay.s-ll1yq]

select a.salesrep from ( select * from orders where salestype ='International') A left outer join ( select * from orders where salestype ='Domestic') B on a.salesrep =b.salesrep where b.salesrep is null union select b.salesrep from ( select * from orders where salestype ='International') A right outer join ( select * from orders where salestype ='Domestic') B on a.salesrep =b.salesrep where a.salesrep is null

[@user-gq6cg3ls7f]

select SalesRepID from Orders where SalesRepID not in( select SalesRepID from ( SELECT count(*) CNT, SalesRepID, SalesType FROM Orders group by salesRepID, SalesType ) x where CNT =2 )

[@user-gq6cg3ls7f]

your SalesRepId returns 3003,4004,5005,6006 so do my query. Am I missing something? not following our point. Please advise.

[@mustapharidwan576]

The link to your site is not working

[@datasculptor2895]

It’s working. Please try again

[@king-hc6vi]

Brother, you are using too many select statements so I dont think it is a optimized solution. Instead you can use window functions..

[@datasculptor2895]

There are only 2. Please share solution with only one select statement

[@king-hc6vi]

​@@datasculptor2895 I don't think there is any need of the last select statement which is used as a sub query. Which make 3 select statement in one solution. Instead you can generate row number and keep it in CTE. And then fetch the result using filters

[@UnrealAdi]

@@king-hc6vi Could you please provide a query? Can't be that simple without doing some sort of manipulations🤔

[@king-hc6vi]

WITH CTE AS (SELECT salesrepID FROM orders, COUNT() OVER (PARTITION BY salesrepID order by invoiceID) as Total_count), SELECT salesrepID from CTE where Total_count 2; ​@@UnrealAdi

[@king-hc6vi]

​@@UnrealAdi WITH CTE AS (SELECT salesrepID FROM orders, COUNT() OVER (PARTITION BY salesrepID order by invoiceID) as Total_count), SELECT salesrepID from CTE where Total_count 2;