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;
@harshitsalecha2216 сағат бұрын
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;
@varunas978423 сағат бұрын
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 ================================
@datasculptor289523 сағат бұрын
Nice, but wrong.
@varunas978423 сағат бұрын
@@datasculptor2895 I did get the exact answer as you expected...could you please explain how is it wrong?? I'm curious
@varunas978423 сағат бұрын
..except for the fact I havent used dense rank but used rank ..the rest sd be fine isn't it??
@datasculptor289522 сағат бұрын
@@varunas9784 exactly you need to use dense rank 👍👍
@varunas978420 сағат бұрын
@@datasculptor2895 Got it, Thanks!
@srinivasulum414Күн бұрын
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
@datasculptor2895Күн бұрын
Wrong solution 😞
@Tech_world-bq3mw11 сағат бұрын
@@datasculptor2895 how? explain here
@DarnasiChakravarthyКүн бұрын
Bro your videos are very crystal clear , please gohead
@datasculptor289523 сағат бұрын
I will try my best
@rajkumarpanigrahi2013Күн бұрын
Brother please make a video on some pl/SQL question
@datasculptor2895Күн бұрын
Every question that I post can be solved using plsql
@varunas9784Күн бұрын
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 ========================================
@harshbhoyar7176Күн бұрын
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)
@Ayush-vu6bl2 күн бұрын
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?
@datasculptor28952 күн бұрын
I will make a video on this.
@Ayush-vu6bl2 күн бұрын
@@datasculptor2895 yes please
@Ayush-vu6bl2 күн бұрын
@@datasculptor2895 yes please :)
@Savenature6353 күн бұрын
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;
@132barcelona3 күн бұрын
Please add the table and insert values here, so that we can also practice
@datasculptor28953 күн бұрын
It’s there in the description of the video
@rajkumarpanigrahi20134 күн бұрын
Brother please solve some questions of pl/sql
@datasculptor28954 күн бұрын
You want the solutions in pl/sql?
@vijaygupta70594 күн бұрын
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
@Sirigineedi_Navann4 күн бұрын
Fantastic brother 👏👏
@bankimdas95174 күн бұрын
Thanks for making video on power bi. Please bring more questions on it.
@hairavyadav65794 күн бұрын
Please provide script to practice
@datasculptor28954 күн бұрын
It’s in the description of this video
@hairavyadav65794 күн бұрын
@@datasculptor2895 Got it thanks My solution : with cte as( select c1.name, (c.revenue - c.expenses) as profit from company c join company_name c1 on c.company_id = c1.id) select name,profit from (select name,profit, row_number() over(order by profit desc) as rnk from cte) sal where rnk<=3; with cte as( select c1.name, (c.revenue - c.expenses) as profit,row_number() over(order by (c.revenue - c.expenses) desc) rnk from company c join company_name c1 on c.company_id = c1.id) select name,profit from cte where rnk<=3;
@UnrealAdi2 күн бұрын
@@datasculptor2895 It doesn't contain sales values!
@namangarg70235 күн бұрын
Very good and tricky
@datasculptor28955 күн бұрын
Thanks. Please like share and subscribe
@vijaygupta70595 күн бұрын
Thanks for the details explanation
@varunas97845 күн бұрын
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' ========================================
@madhustips83045 күн бұрын
excellent
@Ayush-vu6bl5 күн бұрын
Are these type of questions asked to freshers for an interview?
@datasculptor28955 күн бұрын
No. For freshers this should be sufficient kzfaq.info/get/bejne/jeB1mqt4safZj2Q.htmlsi=5IBGtwObKeUDZYW2
@Ayush-vu6bl4 күн бұрын
@@datasculptor2895 Thanks 👍
@srinivasulum4146 күн бұрын
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
@ajittiwari45046 күн бұрын
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;
@anilkumark35736 күн бұрын
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
@datasculptor28956 күн бұрын
Watch this kzfaq.info/get/bejne/iKeff66qsZqoqJ8.html
@rohithr91226 күн бұрын
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
@datasculptor28956 күн бұрын
Nice. Please subscribe to my channel
@nd92677 күн бұрын
------------------------------ --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)
@arjundev49088 күн бұрын
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;
@arjundev49088 күн бұрын
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;
@ajittiwari45048 күн бұрын
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;
@FromPlanetZX8 күн бұрын
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;
@sunilnair8468 күн бұрын
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)
@dugginenichandrababu96159 күн бұрын
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;
@vijaygupta70599 күн бұрын
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
@datasculptor28959 күн бұрын
Nice solution!!
@motiali68559 күн бұрын
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;
@Hope-xb5jv9 күн бұрын
;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 */
@user-gq6cg3ls7f9 күн бұрын
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
@AbinashPatra-n2i10 күн бұрын
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
@Damon-00711 күн бұрын
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;
@zaravind429311 күн бұрын
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
@king-hc6vi11 күн бұрын
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
@dasubabuch159611 күн бұрын
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;
@devarajululanka642711 күн бұрын
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
@mallenisaidinesh410011 күн бұрын
group by employeeid is ok right why both employeeid,employeename
@datasculptor289511 күн бұрын
We need both the columns in output
@ajittiwari450412 күн бұрын
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;
@tarungangadhar1413 күн бұрын
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
@tarungangadhar1413 күн бұрын
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