Brilliant SQL Interview Question | Solve it without using CTE, Sub Query, Window functions

[@Howto-ty4ru]

For those who are having issue with creation of table and insertion of data:
CREATE TABLE int_orders(
order_number int NOT NULL,
order_date date NOT NULL,
cust_id int NOT NULL,
salesperson_id int NOT NULL,
amount float NOT NULL
);
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (30, CAST('1995-07-14' AS Date), 9, 1, 460);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (10, CAST('1996-08-02' AS Date), 4, 2, 540);
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (40, CAST('1998-01-29' AS Date), 7, 2, 2400);
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (50, CAST('1998-02-03' AS Date), 6, 7, 600);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (60, CAST('1998-03-02' AS Date), 6, 7, 720);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (70, CAST('1998-05-06' AS Date), 9, 7, 150);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (20, CAST('1999-01-30' AS Date), 4, 8, 1800);

[@videohub9521]

You are a saviour!

[@meghnasoni]

Great video. Self joins are so powerful yet many people don't know enough about them. Thanks for the video Ankit.

[@ankitbansal6]

My pleasure

[@gagansingh3481]

Your every questions are awesome ankit , I start watching & practing 5 queries each day .....

[@ankitbansal6]

Great 😊

[@anshumansrivastava2801]

Hello Ankit,
Here is my approach.
select o.* from int_orders o left join int_orders o1
on o.salesperson_id=o1.salesperson_id
and o.amount

[@rajudasari8482]

wow, amazed again. Ankit could you please make a video on how to insert data from one table A into table B using SQL stored procedure, the procedure should be dynamic whenever we do any DML operations in table A, table B has to be updated the same as table A.

[@ankitbansal6]

Thank you. Ok will plan

[@AJOnline_]

Without using join clause. Much simpler. A lesser known trick in SQL.
select * from int_orders i1
where amount =
(select max(amount) from int_orders i2 where i1.salesperson_id = i2.salesperson_id)

[@Tusharchitrakar]

this is pretty common and its not less known. It's a sub query and its not allowed as per the problem statement.

[@akashanand1384]

Hi Ankit,
Isn't it possible to simply just take group by and max on amount:
Select order_number,date,ID,max(amount)
From
Group by 1,2,3 order by 3:

[@ankitbansal6]

You will get all orders in output

[@rockstaraishwary]

Yes. I alsu used that only. But as Ankit said, it resulted in all order ids along with max amount. That is unnecessary repeatation in data.

[@Alexpudow]

Great video to understand a self join! But I think it doesn't have anything in common with real tasks. I mean if we have one million order rows it will be really complicated to use this approach. And I most likely will choose sub query or window function.
Anyway thanks a lot, Ankit!

[@exanode]

Awesome. Learnt something new about something so obvious.

[@AjayYadav-gd8ys]

more simpler approach will joining two tables on a.salesperson_id = b.salesperson_id and b.amount> a.amount where b.amount is null
select a.* from int_orders a
left join int_orders b
on a.salesperson_id = b.salesperson_id and b.amount> a.amount
where b.amount is null ;

[@piyushjoshi3910]

my solution-
select s.* from int_orders as s join(
select salesPerson_id,max(amount) as max_amount from int_orders group by salesperson_id) as b on s.amount = b.max_amount

[@archis0605]

Very unique like question. It's Like "SONE PE SUHAGAA".

[@shubhankartiwari876]

Superb & Outstanding CONCEPT.
I WOULD LIKE TO KNOW --->
HOW CAN WE DO THE ROW WISE MATCHING
MEANS COMPARE FIRST ROW WITH Its BELOW ROWS.

[@ankitbansal6]

You can use lead lag function.

[@manjunathareddycheruku7482]

Very usefull video thanks sharing for your time

[@mohit231]

Wow, I watch your videos and learn something new

[@RaviTheVlogger]

Good. We can use window function aslo to get the desired result.

[@ankitbansal6]

You can not use as per question. Otherwise question is simple 😃

[@PriyaYadav-jh5wj]

Can be simply done using this way:
with max_sale as (
select salesperson_id, max(amount) as amt
from int_orders)
select a.order_number,a.order_date, a.cust_id,
b.salesperson_id, b.amt
from int_orders a
inner join
max_sale b
on b.salesperson_id = a.salesperson
AND b.amt = a.amount;

[@ankitbansal6]

It is mentioned that you should not use CTE

[@PriyaYadav-jh5wj]

@@ankitbansal6 oh noo..oops, my bad !
Yours is a great solution then :)

[@RamanGangwani]

select * from int_orders
where amount in
(select max(amount) from int_orders
group by salesperson_id)
order by order_number

[@swamivivekananda-cyclonicm8781]

Comgratulations on promotion :)

[@amitkumar-ip6yv]

thanks Ankit..good question

[@ankitbansal6]

🙏

[@SuperMohit95]

Beautiful solution!!

[@kalyankumar6159]

Hi ankit recently I faced an interview with Infosys. He asked me a question consider a arable has 10 records he asked me how will swap the 1 and 6 records in the table record : 1 have the value Infosys record 6 have the value google we must move the google to 1st place and Infosys to 6th place he asked me the query. Please do a video on this question

[@Lakshya-jz8mu]

select A.order_number,A.order_date,A.salesperson_id,A.amount
from int_orders as A left join
int_orders as B on A.salesperson_id = B.salesperson_id and A.amount < B.amount
where B.amount is null

[@amrutaborse2163]

select * from order234 a
where 1 =(select count(distinct amount) from order234 b
where a.amount

[@rawat7203]

No subquery bro

[@saurabhsoni1998]

select a.order_number, a.order_date, a.cust_id, a.salesperson_id, a.amount
from [dbo].[int_orders] a inner join [dbo].[int_orders] b
on a.salesperson_id = b.salesperson_id
group by a.order_number, a.order_date, a.cust_id, a.salesperson_id, a.amount
having a.amount >= max(b.amount)

[@arpanscreations6954]

This is brilliant.

[@pratikpednekar75]

Hi Ankit,
Here is my solution with using only join:-
select o1.*
from int_orders o1
left outer join int_orders o2
on o1.amount

[@2412_Sujoy_Das]

Simple and Brilliant.......... Pratik!!
Thanks for sharing

[@pinaakgoel2937]

Hi Ankit I did via following method, let me know if it is acceptable :-
select t1.* from int_orders as t1
inner join (select salesperson_id, max(amount) as max_amnt
from int_orders group by salesperson_id) t2
on t1.salerperson_id = t2.salesperson_id and t1.amount = t2.max_amnt

[@ankitbansal6]

With sub query this question is easy. Challenge is doing without subquery or cte or window functions

[@BengaluruGuy08]

Great explanation....

[@shwetasingh6576]

select order_number,order_date,cust_id,salesperson_id,max(amount) from int_orders
group by salesperson_id----is this works?

[@EL_News]

anki bhai why we cant use this below query ?
select order_number, order_date,cust_id, salesperson_id , max(amount)
from int_orders
group by salesperson_id ;

[@ankitbansal6]

We need full order details

[@AmanRaj-uf7wx]

@@ankitbansal6 we are getting the desired out also what will you with full order details.

[@shubhamprasad1200]

Hey Ankit , did this question using join with a different method. Will this work fine? Please check
Select a.*
From(select order_number as order_number_a, order_date as order_date_a, cust_id as cust_id_a,salesperson_id as salesperson_id_a, amount as amount_a
from [int_orders]) as a
inner join
(select salesperson_id,MAX(amount) as amount
from [int_orders]
group by salesperson_id) as b
on a.salesperson_id_a=b.salesperson_id and a.amount_a=b.amount

[@rahulmehla2014]

u are using sub query

[@nehajain9081]

y have u used left join instead of inner join?@Ankit

[@anitadevi365]

Nice concept.
Can you solve this as well.
Time status
10.01 on
10.02 on
10.03 on
10.04 off
10.07 on
10.08 on
10.09 off
o/p:
login logout count
10.01 10.04 3
10.07 10.09 2

[@ankitbansal6]

Thank you 😊 will post a video soon

[@PR-qj3ry]

Very nicely explained Ankit 👍🏻

[@ankitbansal6]

Thank you 😊

[@suneelsunkari4354]

Sir,
could you please let me know if we can join main table with below query on brackets based on salesperson_id
select salesperson_id,max(amount) from init_orders group by salesperson_id
after joining can we use normal filter to filter at row level like amount>= max_amount.
This was your earlier concept where we find minimum customer joining date using cte but this is join where we enclose above query and give alias
Kindly let me know if this is possible.

[@ankitbansal6]

This works but question is you can't use sub query or temp table

[@Guna_Learning2022]

select order_number,order_date,cust_id,a.salesperson_id,a.amount
from int_orders a join
(select salesperson_id,max(amount) as amount from int_orders
group by salesperson_id) b
on a.salesperson_id=b.salesperson_id and a.amount=b.amount
Order by A.order_number

[@sid101invalorant9]

Hi Ankit,
I tried the same problem with below query and got the same answer.
select a.salesperson_id, max(b.amount)
from [int_orders] a
left join [int_orders] b
on a.salesperson_id = b.salesperson_id
group by a.salesperson_id;
Would there be any problem in the result of this query if the data scenario changes?
or will it work the same as shown in video

[@ankitbansal6]

We need full order details for max amount. Not just sales person id ..

[@tarunsaini110]

@@ankitbansal6 ONCE WE HAVE THE SALES PERSON ID AND AMOUNT WE CAN JOIN BASED N BOTH OF THOSE AND GET ALL VALUES

[@chanduguttula25]

select a.* from int_orders a
left join (select max(amount) as max_amt,salesperson_id from int_orders group by salesperson_id) b ON
a.salesperson_id=b.salesperson_id and a.amount=b.max_amt where b.max_amt in (select amount from int_orders)
Is it correct sir?

[@suvratrai8873]

The result should be the same if we use inner join instead of left join, right?

[@Mahi_RSV]

yes!

[@mohitupadhayay1439]

Ankit bhai. Shouldn't there be a Where clause used instead of HAVING?
I think HAVING will work only after the GROUPBY has done it's work. In that case the results would be different?
Please help.

[@ankitbansal6]

You can't use aggregation in where

[@mohitupadhayay1439]

@@ankitbansal6 I understand that Ankit bhai. But in your video results Order 10 is not getting highlighted. Please check.

[@Datapassenger_prashant]

--Solved it without using CTE, Sub Query, Window functions and without watching the video..
will watch video after posting the solution to match the query..:D
Select * from int_orders b
where exists (
select
salesperson_id, max(a.amount) as larget_Value
from int_orders a
where b.salesperson_id = a.salesperson_id
group by salesperson_id
having max(a.amount) = b.amount
)

[@Datapassenger_prashant]

I guess, I messed up.. after watching the video.. as there is where exists subquery.. my bad.. 😮‍💨

[@Datapassenger_prashant]

Corrected in next attempt:
Select a.order_number, a.salesperson_id, a.amount from int_orders a
inner join int_orders b
on a.salesperson_id = b.salesperson_id
group by a.salesperson_id , a.amount, a.order_number
having max(b.amount) = a.amount;

[@pandeyRaman]

why > is used only = is sufficient

[@namansharma4038]

We could have used the group by clause also

[@adharshsunny5154]

good work as always :)

[@ankitbansal6]

Thank you 😊

[@saimahendrapatnaik2847]

Amazing...

[@adityaagrawal1731]

ankit pls give a better DDL im getting error in creating a table

[@ankitbansal6]

Sorry about it. I have updated it. Please try again.

[@adharshsunny2709]

Thanks man

[@innominatesoloist1597]

thanks

[@vijaypalmanit]

Fantastic

[@mohitupadhayay1439]

Spot on stuff! Ankit bhai can you prepare videos where businesses need some specific KPIs or Reports based on SQL. It can be related to HR, Purchase, Sales and so on.
Would be helpful! Thanks in Advance.

[@ankitbansal6]

Thanks Mohit. If you have any use case let me know. I will prepare the video

[@namanmakkar1205]

Can you write the solution using CTE. I tried but not able to find out. I am new to SQL.
with cte sales as select order_number,order_date,cust_id,salesperson_id,max(amount),rank() over(partition by salesperson_id order by order_number) as rn from orders;

[@ankitachatterjee8769]

with cte as(select order_number,order_date,cust_id,salesperson_id,amount,count(1) over(partition by salesperson_id) as cnt,
rank() over(partition by salesperson_id order by amount desc) as rn from int_orders)
select order_number,order_date,cust_id,salesperson_id,amount
from cte where rn=1 or cnt=1 order by order_number

[@abhay6276]

it is very hard to digest for me what you wrote 🙄🥺

[@mathavansg9227]

select order_number,order_date,cust_id,salesperson_id,amount from int_orders
group by salesperson_id HAVING max(amount) order by amount desc

[@dipanjan93]

Can't we do it this way?
Select a.order_number,
a.order_date,
a.cust_id,
a.salesperson_id,
b.amount
from int_orders as a
join
(Select salesperson_id, max(amount) as amount from int_orders group by salesperson_id) as b
on a.salesperson_id = b.salesperson_id and a.amount = b.amount
order by a.order_number

[@ankitbansal6]

As per problem sub query not allowed 😊

[@ankitbansal6]

@UCrmVcaahqeugmWiyIvEeJGQ you are right. Just equal to should be good. Thanks for pointing out.

[@adityabaha]

Super 👍

[@ankitbansal6]

🙏

[@hustlerguy6091]

I always try to do the problems without looking at solution first. Thanks Ankit for making these kind of videos. My solution is different, but I don't know if it is more efficient or not.
select o1.* from int_orders o1 LEFT JOIN int_orders o2 ON o1.salesperson_id=o2.salesperson_id AND o1.amount

[@faizraina7946]

can u explain why u did o2.order no is null

[@chandraprakash0211]

Can this work...
Select order_number, order_date, cust_id, salesperson_id, max(amount) as amt
From int_orders
Group by 1,2,3,4

[@ankitbansal6]

Nope. Order number will differ for multiple orders by a sales person

[@chandraprakash0211]

@@ankitbansal6 yes, Thank you for the feedback

[@mohitupadhayay1439]

I tried doing this with ROW number . Here's my query:
SELECT * , ROW_NUMBER() OVER(PARTITION BY salesperson_id, ORDER BY amount DESC) AS Ranking
from int_orders;
What is wrong with it?

[@ankitbansal6]

Window functions now allowed 😊

[@mohitupadhayay1439]

@@ankitbansal6 I just tried using it. That's why asking where's my fault?

[@dileepn2479]

The rectified one
select * from (SELECT * , ROW_NUMBER() OVER(PARTITION BY salesperson_id ORDER BY amount DESC) AS Ranking from int_orders)a where a.Ranking=1;
this should be solution using window functions ( i do understand window functions to be excluded)

[@rohitbande5758]

Can anyone please confirm whether below solution works for mentioned problem
select a.*
from
int_orders a left join int_orders b
on a.salesperson_id=b.salesperson_id and a.amount

[@SudhirKumar-rl4wt]

Awesome...my solution[provided MINUS is allowed] is a little lengthy :)

[@ankitbansal6]

Good effort

[@sindhujajittuka9020]

Unable to get with cte
Iam new to sql
Can anyone suggest best tutorial

[@deepankarpathak5224]

Hi, Can we do this,
select max(order_number) as order_number,max(order_date) as dates, max(cust_id) as cust_id, salesperson_id,max(amount) as amt
from int_orders
group by salesperson_id

[@ankitbansal6]

That will give wrong output

[@adityaagrawal1731]

select order_number,order_date,cust_id,salesperson_id,max(amount) as amount from int_orders1 group by salesperson_id
i mean you have no where mentioned that you want order by order_number

[@ankitbansal6]

We want order details of highest amount order by each sales person

[@yashsaxena7754]

Would this be acceptable?
select distinct t1.*
from int_orders t1 join int_orders t2
on t1.salesperson_id = t2.salesperson_id and t1.amount = (select max(amount) from int_orders t2 where t1.salesperson_id=t2.salesperson_id)

[@Tusharchitrakar]

no sub queries allowed

[@AmanRaj-uf7wx]

select i.* from int_orders as i
left join int_orders as o on i.salesperson_id= o.salesperson_id
group by i.order_number, i.order_date, i.cust_id, i.salesperson_id, i.amount
having i.amount >= max(o.amount)

[@abb_raj1107]

it can simply achieve by this way without join option, code below:
select salesperson_id,max(amount)
from int_orders
group by salesperson_id

[@ankitbansal6]

We need full order details in output

[@abb_raj1107]

@@ankitbansal6 got it bro thanks

[@rashmidutta7151]

why I dont get any output..with the same:
select o1.order_number,o1.order_date,o1.cust_id,o1.salesperson_id,o1.amount
from int_orders o1 left join int_orders o2 on
o1.salesperson_id=o2.salesperson_id
group by o1.order_number,o1.order_date,o1.cust_id,o1.salesperson_id,o1.amount
having o1.amount>max(o2.amount);
can anyone help me out here?

[@ManeshBPandu]

Is there any extra logic here
select salesperson_id,max(amount) from #int_orders group by salesperson_id i got only 4 rows as
salesperson_id (No column name)
1 460
2 2400
7 720
8 1800
this will work out right i might be wrong, is there any reason to that extreme i mean like having self join and comparing records and getting output. please correct me if i didn't understood the question

[@ankitbansal6]

We need full order details not Just 2 columns

[@ManeshBPandu]

@@ankitbansal6 ohh yeah yeah got it sorry my mistake. Thank you for the reply

[@SudhirKumar-rl4wt]

--find largest order by value for each person and order details
-- without subquery , cte , window function , temp table
drop table int_orders;
CREATE TABLE int_orders(
order_number number NOT NULL,
order_date date NOT NULL,
cust_id number NOT NULL,
salesperson_id number NOT NULL,
amount number NOT NULL
);
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (30, '1995-07-14', 9, 1, 460);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (10, '1996-08-02' , 4, 2, 540);
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (40, '1998-01-29' , 7, 2, 2400);
INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (50, '1998-02-03' , 6, 7, 600);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (60, '1998-03-02' , 6, 7, 720);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (70, '1998-05-06' , 9, 7, 150);
INSERT into int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (20, '1999-01-30' , 4, 8, 1800);
commit;
--solution
select max(ORDER_NUMBER) ORDER_NUMBER ,max(ORDER_DATE) ORDER_DATE ,max(CUST_ID) CUST_ID,SALESPERSON_ID,max(AMOUNT) AMOUNT
from int_orders
group by SALESPERSON_ID
having (count(1)=1)
union all
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
inner join int_orders b on a.salesperson_id=b.salesperson_id and a.amount > b.amount
MINUS
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
inner join int_orders b on a.salesperson_id=b.salesperson_id and a.amount < b.amount
ORDER_NUMBER ORDER_DATE CUST_ID SALESPERSON_ID AMOUNT
------------ ---------- ---------- -------------- ----------
30 1995-07-14 9 1 460
20 1999-01-30 4 8 1800
40 1998-01-29 7 2 2400
60 1998-03-02 6 7 720
SQL>

[@SudhirKumar-rl4wt]

smaller solution post just started watching the video .
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
inner join int_orders b on a.salesperson_id=b.salesperson_id and a.amount >= b.amount
MINUS
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
inner join int_orders b on a.salesperson_id=b.salesperson_id and a.amount < b.amount

[@ankitbansal6]

Thanks for posting 👏

[@SudhirKumar-rl4wt]

@@ankitbansal6 I think we can even further reduce the size of the query..first self join doesn't seem necessary..may be only directly selecting table minus 2nd itself should be fine

[@SudhirKumar-rl4wt]

because second self join gives all the smaller sales except largest one and substracting it from main table should be fine to give the desired result.

[@SudhirKumar-rl4wt]

SQL> --full table
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
MINUS
--all smallers except largest
select a.ORDER_NUMBER,a.ORDER_DATE,a.CUST_ID,a.SALESPERSON_ID,a.AMOUNT
from int_orders a
inner jSQL> oin int_orders b on a.salesperson_id=b.salesperson_id and a.amount < b.amount;
ORDER_NUMBER ORDER_DAT CUST_ID SALESPERSON_ID AMOUNT
------------ --------- ---------- -------------- ----------
30 14-JUL-95 9 1 460
40 29-JAN-98 7 2 2400
60 02-MAR-98 6 7 720
20 30-JAN-99 4 8 1800
SQL>