WITH cte1 AS (SELECT m.name,COUNT(*) as employee_under FROM employee as m INNER JOIN employee as e ON e.managerid=m.id GROUP BY m.name) SELECT name FROM (SELECT name, RANK() OVER(ORDER BY employee_under DESC) as ranks FROM cte1) as a WHERE ranks=2;

WITH RECURSIVE cte1 AS (SELECT product, Quantity,1 as number FROM products UNION SELECT product,Quantity,number+1 as number FROM cte1 c1 WHERE c1.number<Quantity) SELECT product,number as quantity FROM cte1 ORDER BY product,number DESC;

Good one as always! My attempt on SQL server: ================================ with reportee_count as (select e1.id, e1.[name], count(e2.managerid) [no of reportees] from employee e1 left join employee e2 on e1.id = e2.managerid group by e1.[name], e1.id) select [name] from (select *, dense_rank() over(order by [no of reportees] desc) [rank] from reportee_count) s1 where [rank] = 2 ================================

with cte as ( SELECT m.id,e.department,m.name FROM employeed e join employeed m on e.managerid=m.id ) select distinct name from (select *,count(*)over(partition by id)as cnt from cte )a where cnt=2

Bro your videos are very crystal clear , please gohead

Brother please make a video on some pl/SQL question

Thanks for bringing this on your video from my previous company! 😝 My attempt on SQL server: ======================================= select ProductID, [Name] from (select *, 100.0*sum(sales) over(order by sales desc)/ (select sum(sales) from products) [cuml_percentage] from products) s1 where [cuml_percentage] <= 80 ========================================

Can you please tell me if this is correct or not ? select * from friends; with cte as( select f.id, f.friend_id, r.rating as idrating , ra.rating as friendrating from friends f left join Ratings r on f.id=r.id left join ratings ra on f.friend_id = ra.id), cte2 as ( select *, case when idrating<friendrating or (friend_id is null and idrating<85) then 0 else 1 end as flag from cte ) select id from cte2 where id not in(select id from cte2 where flag=0)

Are these questions asked from freshers and can you please suggest the type of questions that are basically asked from freshers from powerbi and excel if one is sitting for a Data Analyst role or they just questions from sql?

My Approach in MySql : select Customerid,max(case when type like '%Cellular%' then phonenumber else null end) as Cellular, min(case when type like '%Work%' then phonenumber else null end) as Work, max(case when type like '%Home%' then phonenumber else null end) as Home from phonedirectory group by 1;

Please add the table and insert values here, so that we can also practice

Brother please solve some questions of pl/sql

My solution using MSSQL : with cte as ( SELECT * ,sum(sales)over(order by sales desc rows between unbounded preceding and 0 following ) as running_total ,0.8 * sum(Sales)over(order by (select null)) as eighty_percent FROM Products ) Select productid, Name from cte where running_total<=eighty_percent

Fantastic brother 👏👏

Thanks for making video on power bi. Please bring more questions on it.

Please provide script to practice

Very good and tricky

Thanks for the details explanation

Good one! Please keep them coming. here's my attempt on SQL server: ======================================== SELECT ID FROM (SELECT F1.ID, CASE WHEN MAX(r2.rating) OVER(PARTITION BY f1.id ORDER BY f1.id) > r1.rating THEN 'N' ELSE 'Y' END as [flag WITH FRIENDS], CASE WHEN (MAX(R1.RATING) OVER(PARTITION BY F1.FRIEND_ID)) = R1.RATING THEN 'Y' ELSE 'N' END AS [FLAG WITH NO FRIENDS] FROM #FRIENDS f1 LEFT JOIN #ratingS r1 ON f1.id = r1.id LEFT JOIN #ratingS r2 ON f1.friend_id = r2.id ) S1 WHERE [flag WITH FRIENDS] = 'Y' AND [FLAG WITH NO FRIENDS] = 'Y' ========================================

excellent

Are these type of questions asked to freshers for an interview?

with cte as ( SELECT product,Quantity,1 as num FROM Products_Rec union all SELECT product,Quantity,num+1 as num FROM cte where num<quantity ) select product,num from cte order by product,num desc

with recursive cte as ( select 1 as num union all select num+1 from cte where num< ( select max(quantity) from products) ) select p.product, c.num as Quantity from cte c inner join products p on p.quantity >= c.num order by p.product, num desc;

Way of output should be Mobile 1 Mobile 1 Mobile 1 TV 1 TV 1 TV1 TV1 TV1 Tablet 1 Tablet 1 Tablet 1 Tablet 1

with cte as( select Product,1 as Quantity from Products union all select c.Product,c.Quantity+1 from cte as c join Products as p on c.Product = p.Product where c.Quantity < p.Quantity) select * from cte order by Product,Quantity desc

------------------------------ --Solution1 ---------------------------- ;with cte as ( select r.id, f.friend_id,r.rating, ra.rating friend_rating ,row_number() over(partition by r.id order by ra.rating desc) rn from ratings r join Friends f on r.id = f.id left join ratings ra on ra.id = f.friend_id ) select id from cte where rn = 1 and(rating > friend_rating or friend_rating is null and rating > 85) ------------------------------ --Solution 2 ---------------------------- ;with cte as ( select r.id, f.friend_id,r.rating, ra.rating friend_rating ,min(case when r.rating > ra.rating then 1 when r.rating > 85 and ra.rating is null then 1 else 0 end) over(partition by r.id) rating_ind from ratings r join Friends f on r.id = f.id left join ratings ra on ra.id = f.friend_id ) select id from cte where rating_ind = 1 ------------------------------ --Solution 3 ---------------------------- ;with cte as ( select r.id, f.friend_id,r.rating, ra.rating friend_rating ,case when r.rating > ra.rating then 1 when r.rating > 85 and ra.rating is null then 1 else 0 end rating_ind from ratings r join Friends f on r.id = f.id left join ratings ra on ra.id = f.friend_id ) select id from cte where id not in (select id from cte where rating_ind = 0)

WITH CTE AS(SELECT NULL AS Product,REGION,SUM(sales) AS SALES FROM SALES group by 2) SELECT C1.Product,NULL AS REGION,SUM(C1.SALES) AS SALES FROM CTE AS C1 JOIN CTE AS C2 ON C1.REGION <> C2.REGION group by 1 UNION SELECT * FROM CTE UNION select * from sales UNION SELECT Product,NULL AS REGION,SUM(SALES) AS SALES from sales group by 1 ORDER BY 1;

WITH CTE AS(SELECT DISTINCT order_id,product_id, CASE WHEN product_id = 100 OR product_id = 200 THEN 'YES' ELSE 'NO' END AS STAT from OrderDetails) SELECT order_id,count(product_id) AS distinct_products FROM CTE WHERE STAT = 'YES' group by 1 having count(product_id) > 1;

with cte as ( select f.id, f.Friend_ID, min(case when r.rating>r1.rating then 1 when f.friend_id is null and r.rating> 85 then 1 else 0 end) over (partition by f.id ) as a from friends f left join ratings r on f.id=r.id left join ratings r1 on f.friend_id = r1.id) select id from cte where a=1;

Using MIN() aggregate function to eliminate id = 1 seems simplier. or are you not using it to avoid performance issue. with cte as ( Select A.id, B.rating, A.friend_id, C.rating AS Frnd_rating , MIN(CASE WHEN B.rating > C.rating THEN 1 WHEN friend_id IS NULL AND b.rating > 85 THEN 1 ELSE 0 END) OVER(Partition by A.id) as flag from Friends A LEFT JOIN Ratings B ON A.id = B.id LEFT JOIN Ratings C ON A.friend_id = C.id ) Select id from cte where flag = 1;

My solution: select id from ( Select A.id, Max(B.rating) as Mainrating, Max(C.rating) as Friendrating from Friends A left Join Ratings B on A.id = B.id left join Ratings C on A.friend_id = C.id group by A.id) x where Mainrating > Friendrating or (Friendrating is null and Mainrating > 85)

Thanks for the Knowledge share Oracle db. solution: SELECT birthday, listagg(studentname,',') within GROUP (ORDER BY birthday) Comma_seperated FROM students1 WHERE birthday IN ( SELECT birthday FROM Students1 GROUP BY birthday HAVING COUNT(*)>1 ) GROUP BY birthday;

my solution in MSSQL DB : with cte as ( SELECT f1.*, r1.rating as myrating , r2.rating as frinds_rating FROM Friends as f1 left join Ratings as r1 on f1.id=r1.id left join Ratings as r2 on f1.friend_id=r2.id ),cte2 as ( Select *, coalesce( max(frinds_rating)over(partition by id order by frinds_rating desc) , 85) as rn from cte ) Select id from cte2 where myrating>rn group by id

with cte as( SELECT A.id,B.rating as Rating,C.rating AS FRIEND_RATING, case when B.RATING>C.RATING THEN 1 WHEN A.friend_id IS NULL AND B.RATING>85 THEN 1 ELSE 0 END AS FLAG, count(*) OVER(partition by id) AS CNT FROM Friends A LEFT JOIN Ratings B ON A.id = B.id LEFT JOIN RATINGS C ON A.friend_id=C.ID ) SELECT ID FROM CTE WHERE FLAG=CNT;

;with cte as ( select f1.id,r1.rating,f1.friend_id,r2.rating as Friend_rating, ROW_NUMBER()over(partition by f1.id order by r2.rating desc) as r from friends f1 left join Ratings r1 on f1.id = r1.id left join ratings r2 on f1.friend_id = r2.id ),cte2 as (select case when rating > Friend_rating then id when Friend_rating is null and rating > 85 then id else null end as id from cte where r = 1 ) select id from cte2 where id is not null /* -------also we can remove cte2 and add below query in where condition and case when rating > Friend_rating then id when Friend_rating is null and rating > 85 then id */

with cte as( select *, case when Total_Marks = LAG(total_marks) over (partition by student_name order by year,total_marks) then Total_Marks when Total_Marks > LAG(total_marks) over (partition by student_name order by year,total_marks) then LAG(total_marks) over (partition by student_name order by year,total_marks) end flag from StudentDetails_Sun ) select * from cte where flag is not null

With CTE as ( select EmployeeID,EmployeeName,Min(startdate) as startdate,Max(isnull(enddate,Getdate())) as end_date , DATEDIFF(year,Min(startdate), Max(isnull(enddate,Getdate()))) as Total_year_exp from EmployeeJobHistory group by EmployeeID,EmployeeName ) select EmployeeID,EmployeeName,Total_year_exp , Case when Total_year_exp < 5 then 'Junior' when Total_year_exp > 4 and Total_year_exp < 10 then 'Senior' when Total_year_exp > 10 then 'Veteran' end as Level from CTE

Ms sql with cte as(select EmployeeID, EmployeeName, sum(datediff (year, startdate, isnull(enddate, getdate() ))) exp from EmployeeJobHistory group by EmployeeID, EmployeeName) select *, case when exp>7 then 'vetern' when exp between 4 and 7 then 'senior' else 'junior' end level from cte Order by EmployeeID;

select employeeid,employeename, sum(datediff(year,startdate,isnull(enddate,getdate()))) total_exp, case when sum(datediff(year,startdate,isnull(enddate,getdate()))) <5 then 'Junior' when sum(datediff(year,startdate,isnull(enddate,getdate()))) <10 then 'senior' else 'veteran' end from EmployeeJobHistory group by employeeid,employeename

WITH CTE AS (SELECT employeeid, employeename, SUM(CASE WHEN iscurrentorganization = '1' THEN (current_date - startdate) ELSE (enddate - startdate) END)/360 AS tenure FROM EmployeeJobHistory GROUP BY employeeid, employeename ORDER BY employeeid) SELECT employeeid, employeename, CASE WHEN tenure <=4 then 'Junior' WHEN tenure between 4 and 7 then 'Senior' ELSE 'Veteran' END as Post FROM CTE; Postgres

with t as ( select employeeid,employeename,companyname,startdate,enddate from employeejobhistory order by employeename, startdate, enddate ), t1 as ( select employeeid,employeename,companyname,startdate,nvl(enddate,sysdate) as enddate from t ), t2 as ( select employeeid,employeename,startdate,enddate, round(months_between(enddate,startdate)/12) as total_experience from t1 ), t3 as ( select employeeid,employeename, sum(total_Experience) as Total_Experience from t2 group by employeeid,employeename ) select employeeid,employeename, Total_Experience, case when Total_Experience <= 4 then 'Junior' when Total_Experience > 4 and Total_Experience < 7 then 'Senior' when Total_Experience > 7 then 'Veteran' else 'None' end as Levels from t3;

with cte as ( select employeeid, employeename, sum(datediff(year,startdate,coalesce(enddate,getdate()))) as total_year from EmployeeJobHistory group by employeeid, employeename ) select employeeid, employeename, case when total_year < = 3 then 'junior' when total_year between 4 and 7 then 'senior' when total_year > = 7 then 'veteran' else 'nothing' end 'total_experince' from cte

group by employeeid is ok right why both employeeid,employeename

with tbl as ( with recursive cte as ( select '2024-01-01' as num, employee_name from (select distinct employee_name from employee_attendance)z union all select adddate(num, interval 1 day), employee_name from cte where num< (select max(date) from employee_attendance) ) select * from cte order by employee_name) select t.num as date, t.employee_name from tbl t left join employee_attendance e on t.employee_name = e.employee_name and t.num=e.date where e.date is null order by t.employee_name;

with ct as(select*,dense_rank() over(order by age desc) as rank from data where category='adult'), ct2 as(select*,dense_rank() over(order by age asc) as rank from data where category = 'Child') select ct.name as adultname,ct2.name as childname from ct left join ct2 on ct.rank = ct2.rank using sql

nd = df.join(df1,df['cid']==df1['id'],how='inner').drop(df['cid']) nd = nd.withColumn("Profit",col('rev')-col('exp')) nd.select('name','Profit').orderBy(col('Profit').desc()).show(3) using pyspark