🚀 Welcome to Data Sculptor , the go-to destination for mastering SQL, BI, and all things data-related! 📊
Are you gearing up for a career in data analytics, business intelligence, or database management? Look no further! Our channel is dedicated to providing you with in-depth insights, hands-on tutorials, and expert tips to ace your SQL and BI interviews.
Subscribe now and embark on a journey of continuous learning and growth in the vast realm of SQL, BI, and data management. Whether you're a seasoned professional or just starting, Data Sculptor is your compass in navigating the exciting and dynamic field of data!
🔗 Subscribe, hit the notification bell, and let's unlock the power of data together! 💡📈 #DataInsights #SQLInterview #BIInterview #DataAnalyticsMastery
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
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;
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
@@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;
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' ========================================
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;
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
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