Rows Between in SQL | Analytical Functions Advanced SQL | Ashutosh Kumar
HTML-код
- Опубликовано: 3 июл 2022
- Sql one of the most important language asked in most of the analytics interviews,in this series i have discussed some advanced level sql concepts that are frequently asked in data analyst,business analyst interviews. In this video i have covered rows between concept in the advanced sql which comes in window functions.
👉 How To Install SQL Server - Complete Process
• How To Install SQL Ser...
👉 Link to excel file -
github.com/ashutoshkr103/Micr...
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
---------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 How to Learn SQL
• How to learn sql for b...
👉 How to become a business analyst complete roadmap-
• Business Analyst Compl...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 Rank ,Dense Rank, Row Number in sql -
• RANK - DENSE RANK - RO...
👉 Cross join in sql
• CROSS JOIN SQL
👉 union join in sql
• UNION IN SQL
👉 left join in sql
• LEFT JOIN IN SQL
👉 Right join in sql
• RIGHT JOIN IN SQL
👉 Inner join in sql
• INNER JOIN IN SQL
👉 Introduction to tables and databases in sql -
• INTRO TO TABLES AND DA...
👉 Aggregate Function in sql
• AGGREGATE FUNCTION IN SQL
👉 Functions in sql-
• IMPORTANT FUNCTIONS IN...
👉 String Function in sql
• STRING FUNCTIONS IN SQL
👉 CRUD operations in sql
• CREATE- READ- UPDATE-D...
👉 Autoincrement in sql
• Auto Increment in SQL ...
👉 Primary Key in sql-
• PRIMARY KEYS IN SQL - SQL
👉 Null and Default values in sql-
• NULL AND DEFAULT VALUE...
👉 Data types in sql-
• Data types in Sql - SQL
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.com/forms/d/e/1FA...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutosh.analytics
💻Linkedin- / ashutoszh
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for data science,
sql for data analytics,
sql practise questions,
sql practise questions and solutions,
sql tutorials for beginners,
sql problems for data engineers,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics,
sql problems easy,
sql problem medium,
sql problems hard,
sql window functions,
sql advanced questions,
rank functions in sql,
lag lead in sql,
sql interview questions and answers,
sql interview questions,
sql questions asked in interviews,
hackerrank sql solutions,
hackerearth sql solutions,
leetcode sql solution
🏷️HashTags
#sql #interviews #questions #solutions
👉 SQL Portfolio Project Indian Census- Part 1
ruclips.net/video/I3YvjFfn478/видео.html
Thank you so much, nicely expalined
Your SQL videos are proving quite valuable. Thanks!
Glad you like them!
The way you taught which is phenomenal
Thanks a lot sir....After watching your videos i am becoming confident on adv SQL day by day...
thats what i want ,
nice video
That's a really great explanation brother with the diagram, I can understand it easily with that diagram thank you for that
This video cleared my confusion on rows range ...Thankyou👍👍
You are welcome 😊
Thank you so much. You saved my life today.
Haha great thanks
Very good explanation ,I tried many blogs and youtube videos but all my concept cleared in your video.
Thanks
Your videos are great and way of teaching is superb!!
Glad you like them!
Thank you brotherman have a healthy life ahead
Thanks
i love it. please i want to buy you chocolate for this awesome video😍
Thank you so much 😀
Thank you so much❤ brother
Thanks
In this case we have sum of values then what about subtraction of values to find which greater, like in sales comparing yesterdays and today's sales and showing the improvement or the loss of sales how would we do that
How it would work for the first row,if the case is you have to take two preceeding or 3 following.?
What if there is duplicate dates, then how this rows between will work?
nicely expalined, can you explain window fns with range clause??
Thanks
brother subquery part is missing in the series m searching for it ...if possible requesting you to add it up
Sure
could you make use case of these concepts.
My project videos contains use of these concepts
-- 29 cost
select distinct country_id,
max(m_cnt) over(partition by country_id order by country_id)m_count,
max(f_cnt) over(partition by country_id order by country_id)f_count,
max(other_cnt) over(partition by country_id order by country_id)other_count,
total
from
(select distinct country_id, cust_gender,
sum(case when cust_gender = 'M' then 1 else 0 end) over(partition by country_id,cust_gender order by country_id)m_cnt,
sum(case when cust_gender = 'F' then 1 else 0 end) over(partition by country_id,cust_gender order by country_id)f_cnt,
sum(case when cust_gender = 'M' then 0 when cust_gender = 'F' then 0 else 1 end) over(partition by country_id,cust_gender order by country_id)other_cnt,
count(country_id) over(partition by country_id order by country_id)total
from customer
order by country_id)
order by country_id;
--14 cost
select distinct country_id,
sum(case when cust_gender = 'M' then 1 else 0 end)
over(partition by country_id order by cust_id rows between unbounded preceding and unbounded following)male_cnt,
sum(case when cust_gender = 'F' then 1 else 0 end)
over(partition by country_id order by cust_id rows between unbounded preceding and unbounded following)female_cnt,
sum(case when cust_gender = 'M' then 0 when cust_gender = 'F' then 0 else 1 end)
over(partition by country_id order by cust_id rows between unbounded preceding and unbounded following)others_cnt,
sum(case when cust_gender = 'M' then 1 when cust_gender = 'F' then 1 when cust_gender is null then 1 else 0 end)
over(partition by country_id order by cust_id rows between unbounded preceding and unbounded following)total_cnt
from customer
order by country_id;
THANKYOU SIR ,BECAUSE OF YOUR EXPLAINATION I WAS ABLE TO CUT DOWN THE COST OF CPU TO MORE THAN HALF. BUT THEN GOT TO KNOW THAT WE CAN USE OVER() WITHOUT ORDER BY CLAUSE IN IT SO TRIED THIS BELOW QUERY THAT WORKS FINE FOR SAME COST BUT MORE CONSICE
how can i get the running sum of only 8 column above row 10
you need to mention 8
For the last example can we not simply sum up the sales columns instead of using a complex function
It's not easy at it looks
Our current row will always first row ?
No the current row is the row on which evaluation is taking place
over explaination...haan aa jao naa seedha baat pe ki kaise karte h. 4 baar problem statement kyu batana😂😂😂
Sorry for the bad experience will try to improve