LEAD & LAG in SQL | Analytical Functions Advanced SQL | Ashutosh Kumar
HTML-код
- Опубликовано: 6 сен 2024
- 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 lead lag functions concepts in sql which comes in window functions.
👉 Link to excel file -
github.com/ash...
#code for 1st table
drop table if exists details;
CREATE TABLE details(train_no INT, station VARCHAR(200), Timing TIME);
INSERT INTO details(train_no , station , Timing)
VALUES(22863,'Howrah','10:50:00'),
(22863 ,'Kharagpur','12:30:00'),
(22863 ,'Balasore','13:52:00'),
(22863 ,'Cuttack','15:47:00'),
(22863 ,'Bhubaneswar','16:25:00'),
(12262 ,'Howrah','05:45:00'),
(12262 ,'Tatanagar','09:00:00'),
(12262 ,'Bilaspur','15:05:00'),
(12262 ,'Raipur','16:37:00'),
(12262 ,'Nagpur','20:55:00');
select * from details;
👉 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.co...
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
Great learning thanks
I tried to use this query
for Q-3
select name ,count(prevyr) as summ from(
select name,years,run,lag(run) over(partition by name order by years)-run as prevyr
from runs)a
where prevyr
👉 SQL Portfolio Project Indian Census- Part 1
ruclips.net/video/I3YvjFfn478/видео.html
👉 Advanced Sql Concepts Complete Playlist
ruclips.net/p/PLbTF1OfX62c3RQ_ZFfyNBWVPdz_OWTMLG
you are putting a lot of effort to taught us..kudos to you dear..god bless you with health and wealth...
Thanks
Bro ek hi baat bolunga yaar
Ki aap kitni tassali se padhate ho, ek ek concept clear ho gya!
Bro, You are jam ♥️
Thxx
try to write query in a way that it will be completely visible, at present only half query is visible, it is difficult to understand it in one go.
I will work on this
SELECT TIMEDIFF(end_time_column, start_time_column) AS time_difference
FROM your_table;
use this from getting difference
Thanks for the clear Explanation bro..
Hi Ashutosh, first of all this is great series.
Thank you so much for guiding us.
I want to ask one question, As given in the last question that fetch those queries where the runs have been increased for the continuously 3 years. If 5 was given in place of 3, So how can we write the query to find those runs whose are increasingly continuously by 5 years? Looking forward to hear from you! Thanks in advance.
can u elaborate
We can also do that through self join right?
Thank you Ashutosh.
why we used a in the last at 22:55
A is the name of Subquery
in mysql it's timediff(t1,t2)
Hi Ashutosh
Thank you for sharing such videos.
Wanted to ask why ain’t we using rows between function while calculating lead values. As you mentioned in the previous videos that we need to use rows between function when we use partition by. Please clarify
There is no hard and fast rule of applying rows between, as per the problem we are solving we need to apply I mean both are totally different things and does not go hand in hand
BHT SAHI H SIR...ADVANCE EXCEL KA BHI PADHA DIJIYE SIR PLS
Covered Kiya h na playlist me
finished watching
please provide the link for the cricket dataset. I am unable to find
Check description box
I'm searching for that particular video but struggling to find it. It will be great if you could explain that calculation in this video only. Video would be 10 mins lengthy but this would be a great video had you included the content of that particular video
what exactly ?
what about the year 2008 for which Rohit has scored more runs than in year 2009 ,
Hi bro excellent content,...but am unable to download the xlsx file from the github link!!! can you pls look into this
sir in this train timing analysis , i thought of some issue...c you did min of time so yu r getting oldest time of the table , but what when the train is being travelling over nights ,then dates changes so will it work then?, in pandas by using date time package that can be figured out anyway, but how to tackle that in sql.. kindly reply, if possible
hey great observation , what's your ttry?
@@AshutoshKumaryt actually sir in sql you cannot create a column which only have time, yeah ofcourse you can create but for that column varchar2 dtype can be used, but if you want to save it as datetime timestamp then you must have date as well as time. so at that time i simply created the column as varchar2, and as per the concern if train travelling have multiple dates, i.e started on some day and arriving on another day like that.. in that case you must have the complete date time stamp,where the difference would give you results in number of days. i hope im making sense
Bro we can use TimeDiff() function for 2)elapse time till now.
Yes
where is the video for time difference?plz send me the link?
Here it is ruclips.net/video/rIPFBsZZ-MI/видео.html
hii sir
the code written at 12:18 is not shown full, and also in time difference video full code is not covered, what all are that floor, %86400 and nested query.
i didnt get it fully
@samaramu32 1min=60 secs, then 60 mins = 1hr = 3600 secs, so, 24 hrs = 24*3600 secs = 86400 secs
introduction opening music is unpleasant, noisy
Sorry for this
where is the video for time difference?plz send me the link?
Hi here it is ruclips.net/video/rIPFBsZZ-MI/видео.html
@@AshutoshKumaryt thank you sir