Aggregation and Window Functions Together in a Single SQL | Advance SQL

In this video we will learn how we can use aggregation and window functions together in a single SQL query. This is very useful concept.

dataset link:

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[akashsonone]

Hi Ankit, CSV files seems deleted, is it?

[lakshitsharma]

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<6

[vibhavaribellutagi]

Thanks a lot for this amazing video. BTW, the dataset link isnt working. :)

[NITIANShubham]

Hey @Ankit thanks for posting this. In today's morning I was scrolling YouTube for this type of questions.

[mohdbelalrahman]

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....

[kingslyroche]

Nice.
Do we have any advantage of doing it this way? other than removing cte in the code.

[manjumohan]

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<=5;

[ahmedtariqsilat]

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

[prudentaml]

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

[hasanmougharbel]

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..

[qazianeel]

I can't believe you explain this topic so easily 😲 Thanks

[vandanaK-mhzo]

Insights - when you do GROUP BY and RANK() in the same query, order of execution will be as follows :
GROUP BY -> AGGREGATION FUNCTION --> RANK()

[kunalthakur]

Thank u Ankit for your efforts . if you can provide tables data for used in those videos then very helpful for us

[raviraj-xque]

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

[learn_with_rudraa]

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

[mubashirali]

Thank you for this playlist ankit bhai.. Really helpful

[as-youtuber]

Bro when are we starting our python series. Waiting for so long 😭

[rabink.]

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;

[chobblegobbler]

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?

[SACHINKUMAR-pxkq]

Thankyou so much Sir for this Amazing Class