Analytical Functions in oracle explained with real examples

This video explains analytical functions and how they are implemented in real projects.
Analytical functions are somewhat similar to aggregate functions,but they offer much more.
Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times
They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function.
I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions.
You can find the sample problems along with dataset in the below link.
www.internships...
#AnalyticalFunctions #OracleAnalyticalFunctions #TechCoach

[@nileshk1455]

I think these are one of the best videos available on RUclips. Appreciate your efforts in making these videos.. And many many thanks

[@TechCoach]

Thanks a lot for the kind words. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it.
ruclips.net/video/IP2Vn6jK8Hg/видео.html

[@tushargursale6060]

Hello
In your video ...at the point where you are explaining cumulative sum, you said order by doesn't matter but in fact it matters a lot if you don't specify order by clause it will not provide the cumulative sum of salaries instead it will give sum of salaries department wise.
Other than that it was a very helpful video thanks a lot man...keep up good work...

[@modakad]

Thanks for this comment. I too thought the same

[@TechCoach]

I made a mistake If I said that. My apologies.
Order does matter

[@sheebaashraf]

Excellent !!!. Thank you very much for deep explanation.
Solution of exercises 1:
select min(salary) over (partition by job_id) as min_sal
,a.* from emp a;
2:
select e.* ,(salary+salary*.10) as Bonus
from (
select lag(hire_date,1,null) over (partition by department_id order by hire_date ) as old_emp, a.* from emp a) e where e.old_emp is null;
3:select count(*) over (partition by manager_id ) as cntofReportee,a.* from emp_bk a
4:select a.*, avg(salary) over (partition by department_id) from emp a where
(months_between(sysdate ,hire_date) /12)> =12;
I am confused about order by ,when to use and when not .Please clear my doubt.

[@TechCoach]

I will review them real quick and will let you know :), for order by try practicing an example using lag and lead function it shall clear all your doubts :)

[@devinderbombrah2394]

The narrative is very well-paced, a very good presentation. Good to see such videos and hope we shall have more from you.

[@TechCoach]

Thanks Devinder for the kind words :)

[@sahilpundora8800]

Thank you for the video. Really helpful to understand OLAP basics.
On a light note, when you said at 18:35 "to come up with an easier answer to find the money spent by company as salary to employees", well, instead of cumulative, we can just use SELECT SUM(SALARY) FROM EMPLOYEE; and if we want department wise, we can use the same with GROUP BY clause. I believe there is a fine line where we should use all these OLAP functions or aggregate functions.

[@TechCoach]

Thanks for the kind words, I will review what I have mentioned at 18:35 :)

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@akshaykadbane9482]

select*from (
select lag(hire_date) over(partition by department_id order by hire_date) as old_emp,a.*, (salary+(0.1*salary)) as new_salary from employees a order by employee_id) where old_emp is null order by employee_id

[@aleksandraarsic1100]

A legend himself 🙏🏻 Big thanks!

[@TechCoach]

I am glad I could help :)

[@nishikantadik5817]

You are doing great . You are actually help me to understand it in easier way . 😃 Thank you so much brother.

[@TechCoach]

Thanks buddy,
Try these 3 videos as well on the same topic, they will help you a lot :)
ruclips.net/video/7sJ3FooAuBE/видео.html

[@mithaSantara]

Correction -- @12:05 you said Previous value -- so in LEAD() it should be Next row not previous value. if its a LAG() I can understand. Hope this is useful to the viewers.

[@karel8y]

I second this; it should be next row.

[@madhavikatta7346]

Vivek please continue your effort. Nice explanation.

[@TechCoach]

Thanks a lot Madhavi :)

[@patrickng6606]

Wow didn’t know analytical function is so powerful until now

[@TechCoach]

Thanks Patrick I am glad I could help

[@ShaikAbdulArif]

thanks for making best video, I haven't seen before, great job and appreciate your efforts

[@TechCoach]

I am glad shaik abdul arif I could be of any help. I have a small request, I am working on this new channel please do watch share and subscribe to it.
Your support is my motivation.
ruclips.net/video/Oz4TbQwrrOY/видео.html

[@rashisingh6985]

Best Explanation of Analytical functions. Thank you!

[@TechCoach]

Thanks a lot Rashi for the kind words :)

[@shivkanyashinde1607]

Yes correct , but what if we have 2 employees in same department hired on same date ...for example in this scenario department 80 hired 2 employees on same day so it should show 2 employees with analytical function as recent joinee but it shows only one .

[@TechCoach]

You are right Shivkanya, I mean a real project query will be a little more sophisticated covering such scenarios. But good catch :)

[@vikastiwari8935]

@@TechCoach so, what is the correct ans of Shivkanya's doubt ?

[@gauravdutta5486]

Hi Shivkanya ,
in this scenario it is good to use the dense_rank function , below query can be used :-
select * from (select e.* , dense_rank() over(partition by deptno order by hiredate desc) as recent_joinee from EMP e) where recent_joinee=1;
even if 10 people joined on the same date , there rank will be same

[@sravaniskitchen344]

@@TechCoach hi please answer shivakanyas doubt ..

[@vishvadeepmohanpandey129]

Very Good Explanation looking forward more videos like that. Given link is not working please provide link to download the data set

[@ssthanu]

Excellent. You made my life easy. Thanks

[@TechCoach]

Thanks for the kind words, I have a small request I am working on a new youtube channel " An Indian Abroad" .
I would really appreciate if you watch and subscribe to it.
ruclips.net/video/HBQHekM1U2c/видео.html

[@maheshnaidu1784]

Excellent Vivek.. The best explanation over analytical functions..
Thank a lot...

[@TechCoach]

Thanks Mahesh for the kind words.
I have a small request, I am working on this new channel please do watch and subscribe to it.
Your support is my motivation. ruclips.net/video/JobONiVWA9Q/видео.html

[@durweshsapkar9070]

Excellent Sir i am working on production environment i had faced so many times this difficulties bu after watching this i definetely improved my knowledge.
Thanks you so much please make more videos about debugging and give some shortcuts while using plsql developers

[@TechCoach]

Thanks durwesh for the kind words, I will certainly work on more videos.Meanwhile
I have a small request, I am working on this new channel please do watch and subscribe to it.
Your support is my motivation.
ruclips.net/video/Oz4TbQwrrOY/видео.html

[@ranajparida3902]

Find most recent employee
Select * from emp where hiredate in ( select max(hiredate) from emp group by deptno); - this is done in Sub Query. No need to introduce Self join with join condition.

[@rajkumarpanditYouTub]

Great Sir. Highly appreciated. The way you presented and talked highly impressive. #Respect.

[@TechCoach]

Thanks Raj for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
ruclips.net/video/IP2Vn6jK8Hg/видео.html

[@shivkanyashinde1607]

1 :
select * from (
select e.*,min(e.salary) over(partition by e.job_id) as min_salary from employees e order by e.job_id ) where salary = min_salary;
2:
select a.*,((a.salary * 10)/100) as bonus from (
select e.*,lag(e.hire_date) over(partition by e.department_id order by e.hire_date) as oldest_emp from employees e) a
where a.oldest_emp is null;

[@rohithreddy6895]

hi shivkanya you need to add order by hiredate desc to pick dept wise oldest_emp otherwise it would pick dept wise recent emp

[@keshavjain8644]

--Q1
select * from
(
select min(salary) over (partition by job_id) as min_sal
,a.* from employees a
) A
where salary = min_sal;
--Q2:
select e.* ,(salary*1.10) as Bonus
from (
select dense_rank() over (partition by department_id order by hire_date nulls last ) as old_emp_rank
, a.* from employees a
order by a.employee_id,department_id desc) e
where e.old_emp_rank = 1 ;
--Q3:
select count(*) over (partition by manager_id ) as cntofReportee,a.* from employees a;
--4:
select a.*, avg(salary) over (partition by department_id) from employees a where
(months_between(sysdate ,hire_date) /12)> =12;

[@shubhamthakur6256]

Brother, doing great job. Thanks a lot. Can you please also start making videos on plsql with real project examples as you always give??

[@TechCoach]

Sure Shubham I will work on them

[@amritamisra1878]

Vivek...too good...very good explanation...it's worth watching your videos

[@TechCoach]

Thanks amrita for the kind words,I have a small request
I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well.
ruclips.net/video/GnVn3mPBRz4/видео.html

[@ranajparida5412]

Hi Vivek,
I was looking the vedio for the analytic function vedio no 19 and it was really good and help full.
I jst want to ask you that you have not uploaded the next analytic vedio that might be helpful. I was searching that but still I am not getting it in your channel..
If possible please upload it for which i will get notification.
Really i am very much interested to get more and more knowledge on analytic function and much more about oracle...
If you will publish the vedio class wise or pattern wise for every concept then i would be a great help for the people like me those who are very much interested to get knowledge..
Please requesting you to upload the remaining vedio for analytic function for which it would be helpful.

[@TechCoach]

sure Ranaj, I will try to make some more, if you have specific question let me know .
I am working on this new youtube channel"An Indian Abroad", please support me by watching subscribing and sharing.
ruclips.net/user/AnIndianAbroadd

[@ranajparida5412]

I was totally viewing the vedio for partitions today...
So please upload the remaining vedio for analytic function...

[@TechCoach]

sure, I will be really happy if you subscribe to my new channel as well.
ruclips.net/user/AnIndianAbroadd

[@piyush2010n]

Amazing explanation

[@TechCoach]

Thanks buddy, I have 4 more videos with practice exercise on this that may interest you

[@piyush2010n]

Can u help me know the playlist name

[@TechCoach]

@@piyush2010n ruclips.net/video/7sJ3FooAuBE/видео.html
You can start with this one

[@venkatasaimounishennamuri1688]

It is very helpful !!! can't thankyou enough!

[@TechCoach]

Glad to hear that!

[@vigneshnagaraj8001]

That was an interesting piece of information. Many thanks!

[@ahmedosman6285]

Thank you for the excellent explanation,
I would like to ask you if the following query solve the fourth point in the exercise ?
select b.* , avg(salary) over (partition by job_id order by hire_date range 4380 preceding) as Average_Salary
from EMPLOYEES b
;

[@TechCoach]

Thanks for the kind words.I will review the query soon.
I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it.
ruclips.net/video/HBQHekM1U2c/видео.html

[@ursus9104]

So the main advantage is to avoid joins in your query, right. Now, I have a DataWarehouse 3rd normal form with 2000 tables, will AF be an advantage for me?

[@TechCoach]

3rd normal in DW , your data would be highly normalized.
If you fine yourself using self join often you can consider analytical functions.
But with 3NF you mayn't get too much help with analytical functions.

[@vedashwi]

thanks a lot..

[@TechCoach]

You are welcome buddy :)

[@gunalb1278]

Hi bro I am fresher 2021 passed out student and I finished Oracle developer certification.And how to became a SQL developer what are the ways to finding jobs?please reply bro...

[@TechCoach]

Hi Gunal,
The job market is hot so you can find a job if you bring some "Value"
Try learning SQL, Excel and one ETL or reporting tool, Polish your communication skills and apply at linkedln for every single job.
All the very best

[@sabilakhan3139]

ques: select oldest employee for each designation and give them 10% BONUS?
SELECT ID,NAME, ROLE_ID, SALARY , CREATED_DATE, OLDEST,
CASE WHEN OLDEST IS NULL THEN (SALARY*(10/100)) WHEN OLDEST IS NOT NULL THEN 0 END AS "BONUS" FROM
(
select a.id,a.name, a.role_id,a.salary, a.created_date, LAG(created_date) over (partition by role_id order by created_date) as "OLDEST"
from employees a)
order by id;
Please let me know if the above is right

[@prakashgour6087]

Thank you man..

[@TechCoach]

Thanks Prakash , i am glad I could help
Please consider subscribing to my other channel as well.
ruclips.net/video/6G1xGi-D5ow/видео.html

[@asavaripasalkar632]

where is the solution ? Please share it.. as I want to cross check my answers

[@TechCoach]

Let me pin the answers so we can verify.

[@asavaripasalkar632]

Ok

[@travelwithsrk]

Rest questions are simple but did not understand question no. 4.....in avg sal column, row who joined after 12 years should be null?

[@mangavadivel6013]

i tried question 2 with lag ... output not correct.
hire_date in employees table is not in proper asec order.

[@mangavadivel6013]

thanks man ... it would really helps .. can u please post more videos with real time scenarios on DWH

[@apharnashri]

awesome explanation on the analytical functions, Vivek !! Kudos !!

[@TechCoach]

Thanks a lot for the kind words :)

[@pradipawasthi2883]

Answer 1:-
select * from(
select a.*, min(a.salary) over(partition by job_id ) min_sal from employees a order by employee_id
) where min_sal in salary
Answer 2:-
select b.*,((b.salary*10)/100) as bonus from(
select lead(hire_date) over(partition by department_id order by hire_date desc) oldest_joinee, a.* from employees a order by employee_id
)b where oldest_joinee is null

[@ambarkumar7805]

Hi, at 5:15 I didn't gt why would we use a self join without self join we got the department wise avg sal

[@TechCoach]

I meant that analytical function helps your do things which would other require a self join.

[@nitingoalla]

Hey Vivek, you are doing a great job. Keep it up.
Also, have you posted the solutions for the practice questions anywhere?

[@TechCoach]

Thanks for the kind words, I have not posted the answers but I will try and post soon.
I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it.
ruclips.net/video/HBQHekM1U2c/видео.html

[@CodeSipSociety]

Best explanation 🔥

[@TechCoach]

Thanks Abhinav :)

[@vru5696]

what if I want lead(hiredate) -1 in output . how to do this?

[@yabuboys2879]

hi
your videos have good stuff, thanks for providing it in your channel.
i have moderate knowledge in sql,plsql'
but i don,t know how real time applications are build.
will you please make a video in it.

[@TechCoach]

I would recommend watching my videos on DWH,ODS and star snow flake schema to get a basic idea, I will try to make some more

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@Cookpe]

could you please guide for 2nd sample question. i'll try this but it gives min hire_date with all employees.

[@santhoshgayakvad1013]

Vivek , great explanation :) and good/simple examples to make us understand. Helps amateurs like me too to catch it quickly :)

[@TechCoach]

+Santhosh Gayakvad Thanks a lot buddy for the kind words :)

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@vishwanathnikam8024]

SELECT COUNT(*) over (partition by MANAGER_ID),EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM Employees order by EMPLOYEE_ID ;
is this the correct answer for question #3 ?

[@pritamdutta8092]

it will count of manager

[@srikamalit]

Video content is good. Using ORDER BY in the output would ease the readability and understanding.

[@TechCoach]

Thanks a lot for the kind words:)

[@giridandi7480]

Very nice

[@varunguleria5960]

@Tech Coach: instead of using Lead , we could have used max of hire_Date partition by dept. to find recently hired person dept wise.
WHAT SAY?

[@TechCoach]

Yup you are right Varun, In fact in this video I have shown 3 ways of writing this query along with a lot of different queries
ruclips.net/video/oiN8rfrWMwU/видео.html

[@kumar-bi1to]

Excellent 👌

[@vikastiwari6564]

Can you please let us know that how to install the same set of data and table in personal laptop?
This video is so knowledgeable and it helped a lot in order to Analytical functions!! Would like to appreciate your efforts!!!

[@TechCoach]

Sure Vikas , I will be uploading a video later today.

[@vikastiwari6564]

@@TechCoach Waiting for it

[@itzzzzpandit]

Hello. At 17.40 scenario, please try without giving order by clause. i think output will be different. Thanks

[@TechCoach]

you need to specify order by clause in almost every analytical function other wise it will give you an error.
Please help me in building my new channel by subscribing sharing and watching.
ruclips.net/user/AnIndianAbroadd

[@khobareamol7215]

@@TechCoach But I don't think below will give error,
SELECT SUM(SALARY) OVER( ) CUM_SAL, E.* FROM EMPLOYEES E

[@TechCoach]

@@khobareamol7215 yes sir, you are right that's why I wrote "Almost".

[@manishbatta6603]

bro best video .Subscribed it best content on youtube regarding SQL

[@TechCoach]

Thanks manish for the kind words, I have a small request.
I am working on this new RUclips channel.I would really appreciate if you watch and subscribe to it.
Your support is my motivation.
ruclips.net/video/0sLU8YaYr4w/видео.html

[@pankajrao6486]

Thanks a lot for detailed explanation....

[@TechCoach]

Thanks Pankaj for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it.
ruclips.net/video/IP2Vn6jK8Hg/видео.html

[@bhavanapriyankazanavarapu869]

you are explaining awsome sir...

[@TechCoach]

Bhavana Priyanka zanavarapu Thanks a lot for the kind words :)

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@1212wolverine]

superb explanation. Thank you.

[@TechCoach]

Thanks a lot for the kind words :),
I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad".
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@mangavadivel6013]

also i tried 2nd question from ur assignment (oldest employee department wise) using min(hire_date) ... output comes with multiple different date for each department.

[@ranajparida3902]

Bro the recent employee,we can get by max(hiredate)

[@aishwaryapattnaik3082]

Can you post the answers too for the sample problems :)
Do you have any forum to contact in case of any doubts.

[@abhishekvedula340]

clear as crystal...thank you :)

[@TechCoach]

Thanks Abhishek for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
ruclips.net/video/IP2Vn6jK8Hg/видео.html

[@sameerkumarpatro5569]

One question - For cumulative salary you didn't mention the significance of "Order By" in over() clause, which is most imp. This would explain how sum function provides cumulative sum.

[@TechCoach]

Thanks sameer, I will review what I have mentioned.
I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it.
ruclips.net/video/11DK-oyRql0/видео.html

[@rafdnaah6078]

You can use this query to find the recent joinee:
SELECT department_id
, max (hire_date) recent_hiredate
, min (employee_id) keep (dense_rank first order by hire_date desc) recent_employee_id
FROM employees GROUP BY department_id ORDER BY department_id;
for a more detailed resultset, use this
SELECT department_id
, MAX(hire_date) recent_hiredate
, MAX(employee_id) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_emp_id
, MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_last_name
, MAX(first_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_first_name
FROM employees GROUP BY department_id ORDER BY department_id;

[@sakshinema8602]

Commendable. Thanks a lot for this video :)

[@TechCoach]

Thanks a lot Sakshi, this video on Indexing may interest you as well :).
ruclips.net/video/0X9bbtwTnuE/видео.html

[@daves4026]

Great tutorial

[@TechCoach]

Thanks Dave for the kind words :)

[@kashifihsan]

Hi, can you plz make a video on listagg.

[@TechCoach]

Please check this video it should help .
ruclips.net/video/-DDBt48G2ug/видео.html
I have a small request I am working on this new youtube channel
"An Indian Abroad"
It will really help me if you watch and subscribe to it
ruclips.net/user/AnIndianAbroadd

[@ankitapathak2371]

Very nice sir 👍

[@TechCoach]

Thanks a lot Ankita for the kind words :)

[@rhari09]

Hi.., the explanation is clear.
But can you please tell on sum(salary) using order by employee gives sum of last two employees like window function.
Similarly i am confused on window as avg is done three rows where we require only for two rows
Please explain
Does order by clause has these effects?

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@pritamdutta8092]

Answer for homework:
1.select minimum_salary over (partitions by designation) as minimum_salary_by_des, e.* from (select min(salary) over(partition by department order by salary) as minimum_salary , a.* from employee a ) e;
2.select max(hire_date) over (partition by department order by hire_date) as oldest_employee , a.* from employee a ...after this how to proceed ?
3.select count(employee_id) over(partition by manager_id) as total_number_of_employee , a.* from employee a
I know i am wrong on 2 and 3 one Please correct me what is the exact answer. I dont have toad or plsql developer with my right now.

[@sehajshangari5707]

select * from emp where (empno,deptno,sal) in (
select empno,deptno, min(sal) over (partition by job order by sal) T from emp a)
select a.*,sal*1.1 as bonus from emp a where (empno,deptno,hiredate) in
(select empno,deptno,min(hiredate) over (partition by deptno order by hiredate) H from emp)
select a.*,count(empno) over (partition by mgr) from emp a order by mgr asc

[@ishikabansal9231]

If we need the count of distinct entries of a table without using the column name, can we do it using Analytic functions without any join?

[@TechCoach]

Select count() over (),e.* from employees.
I am guessing this is what you are looking for ishika

[@ishikabansal9231]

@@TechCoach It will give me the total count of the rows of the table but I need distinct rows, can you tell me how can I implement that?
I need a simplified version of this command :
select count(*) from (select distinct(*) from employees)

[@fathimarizwana9909]

thank u for ur useful video...in the sample scenario u asked to , Select the details of employees with minimum salary on their designation...i got the query as follows ---- select a.*,min(salary) over (partition by job_id) from employees a order by job_id;but it still retrieves multiple JOB_IDs with all salary ....BUt it need to the minimum salary according to each designation...how to correct it??pls calrify

[@TechCoach]

I will take a look at the query Fathima and will get back to you soon, you haven't specified the order by clause along with partition by but that shouldn't cause any issues.
Meanwhile I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
ruclips.net/video/IP2Vn6jK8Hg/видео.html

[@ashutoshsharma3396]

sum(salary) : it should give total sum of salary department wise, for cumulative sum i think we add 'rows unbounded preceding' in analytical function, can you please clear my doubt.

[@TechCoach]

Great Question Ashutosh.
First Explanation:
once you have used partition by department that means the sum function will be applied on individual departments, now here order by is of vital importance actually because of it the 1st row doesn't have any preceding row so we will get just its salary sum, the 2nd row will have 1 preceding row so it will have the sum of 1st and itself and so on. Try running the query on your own it will give you the correct values.
Rows unbounded preceding : yes you are right there are multiple ways to achieve the same result you can use Rows unbounded preceding clause instead of partition by departments to obtain the same result, In Rows unbounded preceding the order by clause is used to describe the window instead of ordering.
sum(salary) over (order by dept_no rows unbounded preceding) cum_sum
will give you cum_sum of salary department_wise, Because here it will continue to do cum_sum as long as it find the same dept_no, As soon the dept_no changes it will again cum_sum for that dept.So here order by is acting as a windowing function instead of ordering.
I hope this clarifies your doubt, I have a small request I am working on this new youtube channel "An Indian Abroad "
and it will really help me if you watch and subscribe to it.
ruclips.net/video/HBQHekM1U2c/видео.html
Please share it with your friends as well, your support is my motivation.

[@samvedna7153]

Do provide feedback.
1.select min(salary) over(partition by job_id) as min_sal , a.* from employees a
order by a.job_id;
2.select employee_id,department_id, hire_date,salary , salary * 1.1 from
(select a.*,rank() over(partition by department_id order by hire_date ) as old_emp from employees a
order by a.department_id,a.hire_date)
where old_emp=1;
3. select count(employee_id) over(partition by manager_id) as count_emp , a.* from employees a
order by a.manager_id;
4.select avg(salary) over(partition by job_id) ,a.* from employees a where add_months(trunc(sysdate) , -12*12) > a.hire_date

[@siprabehera4680]

2.select * from (select lag(hiredate) over (partition by deptno order by hiredate) old_emp,sal*.10 bonous_sal,e.* from emp e) where old_emp is null
try this

[@amareshgangal7]

Very Good Series..

[@TechCoach]

Thanks Amaresh for the kind words :), I hope you share it with your team and friends as well :)

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@satheeshy2k]

Hi ..nice video about analyitcal functions..thank you...instead of "lead(hire_date)" can'nt we use "max(hire_date)" after partitioning by as below :
select MAX(hire_date) OVER(PARTITION BY department_id) ,a.* from employee a

[@TechCoach]

HI Sateesh in this particular case max can be used, but max and lead are very different functions in general.
Let's say I am trying to analyze how my aircarft flies throughout the day.
Source|Dest|dept|arrival|aircraft_type
Delhi|Mumbai|7:00|9:00|787
Chennai|Hyderabad|14:00|16:00|787
Mumbai|Chennai|10:00|12:00|787
Hyderabad|Delhi|17:00|19:00|787
I can use lead function here to identify my aircraft positions which can't be done using max.
I hope this clarifies the difference.
I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it.
ruclips.net/video/11DK-oyRql0/видео.html

[@satheeshy2k]

Got u !!! Appreciate your quick response

[@TechCoach]

Thanks Satheesh I am happy I could help.

[@PinkFloydTheDarkSide]

@@TechCoach - But your logic of using LEAD will fail if there are multiple people with the same hire date in the same department. I used RANK() which I think works better here because the same RANK is assigned to multiple rows if they have the same value for the concerned column.

[@sivajajula2808]

many many thanks

[@TechCoach]

Thanks Siva for the kind words :)

[@AdityaVermaopenheartedme]

Hi great video on analytical functions, just got one issue.
In the video, at 22:18 you said that it is going to compute the average salary of Neena by taking average of three salaries, whereas the function is row 2 preceding, should not it be calculating the average salaries only using only 2 prior salaries, the salary of Neena and only one before her?
Please give clarification.

[@TechCoach]

Hi Aditya, why don't you try running the statement in oracle once? I think that will clarify all your doubts.
If you still have any questions please do let me know. I will be happy to help :)

[@AdityaVermaopenheartedme]

@@TechCoach Sure! Thanks for your reply. Also, if you have time please make an extended video on this topic, explaining everything, every function.
Thanks

[@TechCoach]

@@AdityaVermaopenheartedme Sure I will work on it :)

[@AdityaVermaopenheartedme]

@@TechCoach could you please recommend some good resource?

[@TechCoach]

@@AdityaVermaopenheartedme you can check asktom on google, there is a course on udemy that explains analytical functions in detail as well. I will try posting some practice scenarios in the next couple of days.

[@nikhar1234tare]

Is it possible to find nth highest/lowest salary by using analytical function?

[@TechCoach]

yes it is and it's pretty easy,
select * from (
select rank() over (order by salary) as rank1,e.* from employees
) where rank1=n
please let me know if it helps.
I have a small request I am working on this new channel "An Indian Abroad "
I will really appreciate if you watch and subscribe to it.
ruclips.net/video/IR6mVE181a4/видео.html

[@niharranjandash7004]

NO

[@a_mhite2]

@@TechCoach Small mistake in above query.. Corrected in below..
Select * FROM
(Select RANK() OVER(Order By Salary ASC) as Latest_Rank, e.* From employees e) Where Latest_Rank=&N;

[@malliarjun5250]

@@a_mhite2
Don't use rank function if you use rank
24000 1
17000 2
17000 2
14500 4
If u want 3rd rank salary we don't get it
So use dense_rank function
Then we can query for whatever rank we want
24000 1
17000 2
17000 2
14500 3

[@malliarjun5250]

@@a_mhite2
Use desc in order by that too in dense_rank() function

[@enockoloo3814]

this is great

[@TechCoach]

Thanks Enock for the kind words :)

[@pankajkharade6936]

superb

[@TechCoach]

Thanks Pankaj for the kind words :)

[@iticcl3565]

Hi.. have any idea to know how can i find Query to generate Employee absent report for a given range of dates
but.. not possible to query.. can you help me.

[@TechCoach]

Can you please elaborate a lil more?

[@TechCoach]

I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad".
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@iticcl3565]

im working in RMG sector in Bangladesh as a IT person.. every month many people absent continuously like 5 or more days.. so i calculate in those employe who absent continuously basent five or more days in a month ... i can't do this. I need your help to find out this.

[@TechCoach]

Can be done using analytical function or a stored procedure both. I can share it with you after 15 days as I am on vacation right now.

[@iticcl3565]

can u share me your mail address. i send you query with database..

[@sudheereddy6182]

Can I have the ddl & dml for the tables explained above pls ...

[@TechCoach]

Hi Sudhee, Unfortunately I don't have it handy right now, Majority of the tables used here are standard HR schema tables which comes by default when you install oracle express edition.
I would recommend installing oracle express edition and trying the queries shown in video on those tables.
I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@hanifouladgar7842]

Where can I find the data and the answer to the questions?

[@TechCoach]

Hi Hani, most of the answers are posted in the comments section.

[@akshaykumarjain7011]

Thanks

[@TechCoach]

I am glad I could help Akshay :)

[@akshaykumarjain7011]

@@TechCoach Your welcome sir 🙏

[@mangavadivel6013]

can you provide answers for those 4 assignment questions.

[@TechCoach]

@Manga , Quite a few people havd answered them in comments. If you don't find them helpful please let me know I will be more than happy to provide them

[@mangavadivel6013]

@@TechCoach i tried question 2 with lag ... output not correct.

[@mangavadivel6013]

I tried round (avg(salary)). it didnot work. why?

[@TechCoach]

Can you post the query please?

[@mangavadivel6013]

@@TechCoach select e.*, round(avg(salary)) over(partition by department_id order by salary ) avg_sal from employees e ;
shows from keyword not found where expected.

[@shivkanyashinde1607]

query should be like
select e.*,
round(avg(e.salary) over(partition by e.department_id order by e.hire_date)) as avg_sal from employees e;

[@shivkanyashinde1607]

have to use round function to complete analytical function not only for the avg function

[@rubang973]

Grt❤️

[@deeek92]

Can you please post the answers as well

[@TechCoach]

Deepankar Kotnala Sure I will post them soon :)

[@deeek92]

Tech Coach okay..thanks :)
We want to match our queries with the solutions provided by you :)

[@TechCoach]

I would love your support for my new channel, Please watch subscribe and share with your friends.
ruclips.net/channel/UCoLdaMxhfDgdgIAcRGlUvkg

[@RenuSharma-rx8yt]

Pls anyone tell me how to solve this --> Q. The percentage of people who were handraisers for each campaign. For reference this is the table - Table: user_handraisers
+--------------------------+--------+---------+
| name | type | comment |
+--------------------------+--------+---------+
| campaign_date | string | |
| userid | string | |
| campaign_id | string | |
+--------------------------+--------+---------+

[@shanmugaraj09]

Crazy 😘

[@TechCoach]

Thanks Shanmuga for the kind words I have a small request
I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well.
ruclips.net/video/GnVn3mPBRz4/видео.html

[@navk4960]

video blurr

[@TechCoach]

Can you please try watching the video at 1080p?

[@navk4960]

@@TechCoach my display on laptop is set with highest 1366 X 768

[@navk4960]

watched other video of yours, didn't see any issues with that..

[@TechCoach]

Oh I am sorry :(

[@pramodchamute8882]

Not a good explain

[@TechCoach]

I am sorry pramod, I will try to do better next time.

[@ilovemyindia54]

Sir apka number do

[@KrishnaPaneri]

Thanks... Nicely explained

[@TechCoach]

Thanks a lot for the kind words.
I would really appreciate if you help me in my new Initiative "An Indian Abroad"
by subscribing viewing and sharing.
ruclips.net/user/AnIndianAbroadd