Відео

Hi Rahul, Excellent video. I have one doubt. How can we compare the string value is smaller or bigger?Can you explain me?

funny images😄

thank you

Perfect explanation thank you 🙏🏻

nice table

how to save union data into a new table?

I like the way you have provided table creation and insertion script . very thoughtful Sir.

Good one and helpful 👍🏻👍🏻 I dont know why viewers less here😔

Question - Why we make as a false? InputDF.show(false) I’m waiting for your response Thanks 🙏

Thanks for this video 🎉 Question - how can we save notebook for future purpose coz cluster is getting disconnected after 1-2 hours if it’s in idle state. Thanks

Thanks for video sir

with current_role as ( select a.emp_id,b.org_role_level as old_level from switch_info as a join role_info as b on a.emp_current_org = b.org_name and a.emp_current_role = b.org_role_name), new_role as ( select a.emp_id,b.org_role_level as new_level from switch_info as a join role_info as b on a.emp_next_org = b.org_name and a.emp_next_role = b.org_role_name) select e.*,c.old_level,n.new_level from switch_info as e join current_role as c on e.emp_id = c.emp_id join new_role as n on e.emp_id = n.emp_id where c.old_level < n.new_level

with cte1 as ( select emp_dept_id,avg(emp_salary) as avg_salry from emp_info_avg group by emp_dept_id),cte2 as ( select * from emp_info_avg) select a.* from cte2 as a join cte1 as b on a.emp_dept_id = b.emp_dept_id and a.emp_salary > b.avg_salry

select a.team_name as team_1 , b.team_name as team_2 from team_info as b , team_info as a where a.team_name <> b.team_name;

with cte as ( select p.person_id,p.person_name,p.person_group_id,e.exp_amount as total_expenditure, sum(e.exp_amount) over(partition by p.person_group_id) as total_group_amount, (sum(e.exp_amount) over(partition by p.person_group_id)/count(*) over(partition by p.person_group_id)) as equal_amt_share from person_info as p left join expenditure as e on p.person_id = e.exp_person_id) select *,(equal_amt_share - isnull(total_expenditure,0)) as pending_share from cte order by person_group_id,pending_share

select * , left(file_location,len(file_location) - CHARINDEX('\',reverse(file_location))) as [file_Path], Right(file_location,CHARINDEX('\',reverse(file_location))-1) as [file_name], RIGHT(file_location,CHARINDEX('.',reverse(file_location))) as file_extenstion from file_details;

'Promosm' 💪

Nice one

Thanks for the valuable session

Hi Rahul I just started learning data engineering staff your content is looking good, thank you looking forward to learn from you.

too great sir bring more.

SELECT type, sum(coalesce(time_res, 0)) as time_duration FROM electric_items GROUP by id, type;

I don’t understand English 😢

Hello Sir, I got the logic behind grouping the consecutive days. But I am trying to do it in another approach but after so many tries I didn't able to find any such logic apart from yours in whicu we can group the consecutive days. If possible could you please tell me other approach apart from the one which you discussed in the video?

this also works : SELECT match_no, COALESCE( mom_player, (SELECT top 1 mom_player FROM mom_2004 AS prev WHERE prev.match_no < m.match_no AND mom_player IS NOT NULL ORDER BY prev.match_no DESC) ) AS mom_player, match_opponent FROM mom_2004 AS m ORDER BY match_no;

Super, saw many videos but yours is amazing

SELECT type,sum( case when status = 'on' then -time_res else time_res end) as time_duration FROM electric_items GROUP by id, type;

1st time watching this type of case statement in sql server ....learnt something new today...thank you so much rahul for this wonderful question.

Hii.. rahul! This is my approach: with exp as(select exp_person_id,sum(exp_amount) as exp_amount from expenditure group by exp_person_id) select p.person_id,p.person_name,p.person_group_id, e.exp_amount as total_expenditure, sum(e.exp_amount)over(partition by person_group_id) as total_group_amount, avg(coalesce(e.exp_amount,0))over(partition by person_group_id) as equal_amount_share, avg(coalesce(e.exp_amount,0))over(partition by person_group_id)-(coalesce(e.exp_amount,0)) as pending_share from person_info p left join exp e on p.person_id=e.exp_person_id

I like the way you use the CTE structure!

Thank you for this very useful video!

Thank you for this very useful video!

Thank you for this very useful video!

These functions exists in other databases?

Thank you for this very useful video!

Thank you for this very useful video!

Thank you for this very useful video!

And if the first name is sometimes composite like: Johann Sebastian or the last name is composite too like: Perez Cruz? Thank you!

All the functions presented here are for MSS, where to find for other databases like DB2, SQLlite etc! Thank you!

Thank you for this very useful video!

Nice sir

select itemid,itemname, sum(case when purchase_month='january'then itemquantity end) as january, sum(case when purchase_month='febuary'then itemquantity end) as febuary, sum(case when purchase_month='march'then itemquantity end) as march, sum(case when purchase_month='april'then itemquantity end) as april, sum(case when purchase_month='may'then itemquantity end) as may, sum(case when purchase_month='june'then itemquantity end) as june from purchase_2019 group by itemid,itemname

select * from( SELECT PLAYER_ID, PLAYER_NAME, PLAYER_SCORE, MATCH_DATE, ROW_NUMBER() OVER(PARTITION BY PLAYER_ID ORDER BY PLAYER_SCORE DESC) AS RN, ROW_NUMBER() OVER(PARTITION BY PLAYER_ID ORDER BY PLAYER_SCORE asc) AS RNK FROM MATCH_SCORE)x where rn=3 or rnK=1

Here is mu solution - with cte as (select *, LAG(result) over (partition by empid order by quizdate) as previous , LEAD(result,1,result) over (partition by empid order by quizdate) as next_result from quiz) select *, (case when previous IS NULL and next_result = 'Fail' then 'Valid' when previous = 'Fail' and next_result = 'Fail' then 'Valid' when previous = 'Pass' and next_result = 'Fail' then 'Invalid' when previous = 'Fail' and next_result = 'Pass' then 'Valid' when previous IS NULL and next_result = 'Pass' then 'Valid' else 'Invalid' end) as result from cte order by empid desc

Hello sir I have used LAG function to solve this problem, but I feel that my solution is hard coded. I tried to use recursive CTE by giving a condition IS NULL and populate until it's not NULL. Can you please solve it using LAG and Recursive CTE if possible?? with cte as (select *, LAG(mom_player) over (order by match_no) new_player from mom_2004), cte2 as (select match_no, (case when mom_player IS Null then new_player else mom_player end) as mom_player, match_opponent from cte), cte3 as (select *, LAG(mom_player,1,mom_player) over (order by match_no) new_player from cte2) select match_no, (case when mom_player IS Null then new_player else mom_player end) as mom_player, match_opponent from cte3

Here is my solution, I tried to solve the two cases separately and the use union all to combine (select * , row_number() over (partition by player_name order by player_score desc ) as rw, COUNT(1) over (partition by player_name) as total_innings from match_score), cte_1 as (select * from cte where rw = 3 and total_innings >= 3), cte_2 as (select * from cte where rw = total_innings and total_innings<=2), cte_3 as (select * from cte_1 union all select * from cte_2) select * from cte_3 order by player_id

with cte as (select *,dense_rank()over(partition by player_name order by player_score) as rank_ from match_score) ,cte2 as(select player_name, max(case when rank_=3 then player_score when rank_=1 then player_score end ) player_score from cte group by player_name) select m.player_id,m.player_name,m.player_score,max(m.match_date) as date_ from match_score as m right join cte2 as c on m.player_score=c.player_score group by m.player_id,m.player_name,m.player_score

Thank you sir, for such scenarios Here is my solution using join and cte with cte as (select e.*, b.emp_salary as manager_salary from emp_details as e inner join emp_details as b on e.emp_mgr_id = b.emp_id) select emp_id, emp_name, emp_location, emp_salary, emp_mgr_id from cte where emp_salary > manager_salary

Best Playlist I have ever encountered👏

Sir, thank you for creating such scenarios, please keep them coming Meanwhile, here is my solution- with cte as (select *, SUM(weight) over (order by queue_position) as cumulative from user_queue) select top 1 * from cte where cumulative < 400 order by cumulative desc