Simplest Solution: select category, product_name, sales from ( Select category, product_name, sum(sales) as sales, rank() over(partition by category order by sum(sales) desc) rnk from [dbo].[Sample - Superstore.xls - Orders] group by category, product_name ) x where x.rnk
Hi Ankit, your videos are excellent. They cleared most of my doubts. Can you make video on denormalization or normalization(breaking a row to multiple rows) of table through sql? Also on unions ? Thanks
Hey there, god bless your efforts. I am still learning about sql by my own and having today a simple question. I learned that Processes (also known as “procedures”) may take values and give out parameters. Are processes opposite of a function? Is there any related functions used to execute a process? Thanks..
using dense_rank: with top_sales_per_cat as( select category, Product_Name , sum(Sales) as Total_Sales, DENSE_RANK() over(partition by Category order by SUM(sales) desc) as rn from market_star_schema.`sample - superstore` group by Category,Product_Name ) select * from top_sales_per_cat where rn
ankit bhaiya , ek question pucha aaj mere se in sql interview - 4 columns hn ID, s1, s2,s3(ye teen subjects hn jike marks diye rhenge) mjhe top student nikalna h jiska s1+s2+s3 max ho using dense_rank(). so maine ye query try ki - SELECT id, s1+s2+s3 as summ, dense_rank() as rank over(order by summ desc ) FROM students-; but ye kaam nhi kiya, can u pls tell kya solution hoga iska
@ravi Raj , sum is work with column data not with row data Which means u can't add subj marks for each student individually by using sum() function. @ankit bansal , will help us to find a proper solution for it Thanks in advance 🤗
Create table #TopMarks ( ID int, S1 INT,S2 INT, S3 INT) INSERT INTO #TopMarks Values (1,100,200,300), (2,100,20,30), (3,200,200,300) Select * from #TopMarks Select ID,(S1+S2+S3) as [Top], RANK() over ( order by S1+S2+S3 desc) from #TopMarks
hi ankit sir, Im getting issue in importing data in mysql workbench out of all the rows only 27 or 140 is importing in my sql workbench. i have checked everthying data types and file formate everything..? what to do
Hi your vdos are very beneficial. And watch ur vdos frequently and your suggestions on linkdin...i am a mechanical engineer working in a psu. May be you know that all psus are in remote location far from city life no social life. I want transition from power engineer to data science or data analyst..i have learnt baisc of python,sql some of libraries like pandas,numpy....pls guide me with some suggestions.... And, is it easy or possible to get a job work from home in dataanalyst. Thanks....
Hi Ankit, could you please tell why dense_rank not worked in your previous problem( air bnb cross apply). I posted there but no response for any one.
select value as rm_type, count(*) as no_search, dense_rank() over( order by count(*) desc) as rnk from airbnb cross apply string_split(filter_room_types, ',') group by value having rnk = 1;
Hi , I see your videos are very good and I do have one business problem , pls write the sql . In put Caller Reciever call_duration(sec) Raj Ram 30 Ram Raj 60 Robert Raheem 50 Arun Raj 45 Raj Arun 25
Out put Caller1 Caller2 no_of_calls total_duration Raj Ram 2 90 Arun Raj 2 70 Robert Raheem 1 50 pls let me know if you need more details.
select least(caller,receiver) as Person1,greatest(caller,receiver) as Person2 ,count(1) as no_of_times,total_sec from cte group by 1,2; Script : create table caller_receiver( caller varchar(20), receiver varchar(20), call_duration_sec int ); Insert into caller_receiver values ('Raj','Ram',30); Insert into caller_receiver values ('Ram','Raj',60); Insert into caller_receiver values ('Robert','Raheem',50); Insert into caller_receiver values ('Arun','Raj',45); Insert into caller_receiver values ('Raj','Arun',25);
select least(caller,receiver) as Person1,greatest(caller,receiver) as Person2 ,count(1) as no_of_times,sum(call_duration_sec) from caller_receiver group by Person1,Person2;
Simplest Solution:
select category, product_name, sales from
(
Select category, product_name, sum(sales) as sales,
rank() over(partition by category order by sum(sales) desc) rnk
from [dbo].[Sample - Superstore.xls - Orders] group by category, product_name
) x
where x.rnk
Hi Ankit, CSV files seems deleted, is it?
Very nice vdo 😇😇 ❤❤
Hey @Ankit thanks for posting this. In today's morning I was scrolling RUclips for this type of questions.
Awesome
I can't believe you explain this topic so easily 😲 Thanks
Most welcome 😊
Nice.
Do we have any advantage of doing it this way? other than removing cte in the code.
Nothing more advantageous. Just wanted to tell you another way of doing it.
I just fix the sql as their was typo .initially I tried using cTE later I tried without CTE
Thank you for this playlist ankit bhai.. Really helpful
Most welcome 😊
Hi Ankit,
your videos are excellent. They cleared most of my doubts.
Can you make video on denormalization or normalization(breaking a row to multiple rows) of table through sql?
Also on unions ?
Thanks
Insights - when you do GROUP BY and RANK() in the same query, order of execution will be as follows :
GROUP BY -> AGGREGATION FUNCTION --> RANK()
Hi Ankit,
Great video
Kindly create a video on SQL cursors.
I was going through your channel for cursor video but couldn't find it
Nice explanation 👌 👍 👏
Thank u Ankit for your efforts . if you can provide tables data for used in those videos then very helpful for us
Thanks a lot for this amazing video. BTW, the dataset link isnt working. :)
Hey there, god bless your efforts.
I am still learning about sql by my own and having today a simple question.
I learned that Processes (also known as “procedures”) may take values and give
out parameters.
Are processes opposite of a function?
Is there any related functions used to execute a process? Thanks..
using dense_rank:
with top_sales_per_cat as(
select
category,
Product_Name ,
sum(Sales) as Total_Sales,
DENSE_RANK() over(partition by Category order by SUM(sales) desc) as rn
from
market_star_schema.`sample - superstore`
group by
Category,Product_Name
)
select * from top_sales_per_cat
where rn
Thankyou so much Sir for this Amazing Class
Most welcome
Thanks for this
ankit bhaiya , ek question pucha aaj mere se in sql interview - 4 columns hn ID, s1, s2,s3(ye teen subjects hn jike marks diye rhenge) mjhe top student nikalna h jiska s1+s2+s3 max ho using dense_rank().
so maine ye query try ki - SELECT id, s1+s2+s3 as summ, dense_rank() as rank over(order by summ desc ) FROM students-;
but ye kaam nhi kiya, can u pls tell kya solution hoga iska
with cte as(
select *, s1+s2+s3 as max_marks from students)
select *, dense_rank() over(order by max_marks desc) as rn from cte;
@ravi Raj , sum is work with column data not with row data
Which means u can't add subj marks for each student individually by using sum() function.
@ankit bansal , will help us to find a proper solution for it
Thanks in advance 🤗
Create table #TopMarks ( ID int, S1 INT,S2 INT, S3 INT)
INSERT INTO #TopMarks Values (1,100,200,300), (2,100,20,30), (3,200,200,300)
Select * from #TopMarks
Select ID,(S1+S2+S3) as [Top],
RANK() over ( order by S1+S2+S3 desc)
from #TopMarks
hi ankit sir,
Im getting issue in importing data in mysql workbench out of all the rows only 27 or 140 is importing in my sql workbench. i have checked everthying data types and file formate everything..? what to do
Hi your vdos are very beneficial. And watch ur vdos frequently and your suggestions on linkdin...i am a mechanical engineer working in a psu. May be you know that all psus are in remote location far from city life no social life. I want transition from power engineer to data science or data analyst..i have learnt baisc of python,sql some of libraries like pandas,numpy....pls guide me with some suggestions....
And, is it easy or possible to get a job work from home in dataanalyst. Thanks....
Hi Ankit, could you please tell why dense_rank not worked in your previous problem( air bnb cross apply). I posted there but no response for any one.
select value as rm_type, count(*) as no_search,
dense_rank() over( order by count(*) desc) as rnk
from airbnb cross apply string_split(filter_room_types, ',')
group by value
having rnk = 1;
Bro when are we starting our python series. Waiting for so long 😭
Next video on python. Promise
@@ankitbansal6 thank you
Nice
I want to get count of missing states for an column by using count and lag in same query with group by.. Is the scenario possible?
Can you elaborate with some data
data set link not working deleted
@ankit bansal sir please share csv file
Hi , I see your videos are very good and I do have one business problem , pls write the sql .
In put
Caller Reciever call_duration(sec)
Raj Ram 30
Ram Raj 60
Robert Raheem 50
Arun Raj 45
Raj Arun 25
Out put
Caller1 Caller2 no_of_calls total_duration
Raj Ram 2 90
Arun Raj 2 70
Robert Raheem 1 50
pls let me know if you need more details.
select least(caller,receiver) as Person1,greatest(caller,receiver) as Person2 ,count(1) as no_of_times,total_sec
from cte
group by 1,2;
Script :
create table caller_receiver(
caller varchar(20),
receiver varchar(20),
call_duration_sec int
);
Insert into caller_receiver values ('Raj','Ram',30);
Insert into caller_receiver values ('Ram','Raj',60);
Insert into caller_receiver values ('Robert','Raheem',50);
Insert into caller_receiver values ('Arun','Raj',45);
Insert into caller_receiver values ('Raj','Arun',25);
select least(caller,receiver) as Person1,greatest(caller,receiver) as Person2 ,count(1) as no_of_times,sum(call_duration_sec)
from caller_receiver
group by Person1,Person2;
Sir pls start a paid course on sql