the motivation you get when you solved the question by youself and used the 'mentos zindagi' approach at first .... thank you so much sir for these amazing tutorials
Thanks Ankit, your videos help us a lot. Solution for TWIST scenario Without using Ranking with default_ph_num as ( select distinct employeeid, case when count(case when isdefault='false' then isdefault else null end) = count(isdefault) then FIRST_VALUE(phone_number) over(partition by employeeid order by added_on desc) else phone_number end default_phone from employee_details_twist group by employeeid, phone_number, added_on ) select cte.employeeid, dp.default_phone, count(entry_details) totalentry, sum(case when entry_details='login' then 1 else 0 end) as totallogin, sum(case when entry_details='logout' then 1 else 0 end) as totallogout, max(case when entry_details='login' then timestamp_details else null end) as latestlogin, max(case when entry_details='login' then timestamp_details else null end) as latestlogout from employee_checkin_details cte inner join default_ph_num dp on cte.employeeid=dp.employeeid group by cte.employeeid, dp.default_phone
Ankit after solving all the previous videos from this playlist, I have been able to solve this question like mentos life. Thanks a lot to you man for building our sql base so solid. It really feels great now.
Congratulations sir,can you please provide your road map and how you got job what are thes skills you gained to ge this job Please reply it will me helpful for people having career gap like me🙏
Thank you Sir! once again. I have reached up to that level in one year of watching your videos where I directly think solutions like "Mentos zindagi"😅 I can directly think of approach in my mind on how will I solve it and you do the same. Lots of success and best wishes to you ❤
Wow. I can't believe I solved this myself and later checked with the soln and I solved it by the shorter method (mentos zindagi). I have been following your playlist for SQL . I have completed the Medium level interview questions playlist and now focusing on complex queries playlist. Your videos helped me build that thinking skill or intuition to solve problems in a shorter way. Thanks! SELECT c.employeeid,ed.phone_number as e_default_phone_no,COUNT(1) AS total_entries, SUM(CASE WHEN entry_details='login' then 1 else 0 end) as total_login, SUM(CASE WHEN entry_details='logout' then 1 else 0 end) as total_logout, MAX(CASE WHEN entry_details='login' then timestamp_details end) as latest_login, MAX(CASE WHEN entry_details='logout' then timestamp_details end) as latest_logout FROM employee_checkin_details c LEFT JOIN employee_details ed ON c.employeeid=ed.employeeid and isdefault='true' GROUP BY c.employeeid;
Sir when solving the first time it's some we are understanding by your explanation but it seem to be very deficult at first time... My question is by seeing the question nothing is coming in mind means how to solve how resolve how to break the problem statement.. And can we able to solve the other problems by practicing more and more???
Hello this is my approach with base as( select employeeid,phone_number as default_number from tableName1 where isdefault='true'),base1 as( select employeeid,count(entry_details) as total_entry, sum(case when entry_details='login' then 1 else 0 end) as total_logins, sum(case when entry_details='logout' then 1 else 0 end) as total_logouts, max(case when entry_details='login' then timestamp_details end) as latest_login, max(case when entry_details='logout' then timestamp_details end) as latest_logout from tableName group by employeeid) select ifnull(e.default_number,'none') as default_number,c.* from base as e right join base1 as c on e.employeeid=c.employeeid
Solution for TWIST statement Using Ranking ; WITH cte as ( Select e2.employeeid, e2.isdefault,e2.phone_number,e2.added_on , COUNT(entry_details) as totalentry , COUNT(CASE WHEN entry_details = 'login' THEN timestamp_details END) as totallogin , COUNT(CASE WHEN entry_details = 'logout' THEN timestamp_details END) as totallogout , MAX(CASE WHEN entry_details = 'login' THEN timestamp_details END) as latestlogin , MAX(CASE WHEN entry_details = 'logout' THEN timestamp_details END) as latestlogout , DENSE_RANK() over(PARTITION BY e2.employeeid ORDER BY e2.added_on DESC) as RNK from employee_checkin_details as e1 LEFT JOIN employee_details_twist as e2 on e1.employeeid = e2.employeeid group by e2.employeeid,e2.isdefault,e2.phone_number,e2.added_on ) select * from cte where RNK = 1
with cte as( select employeeid, count(entry_details) Total_enters , max(case when entry_details = 'login'then timestamp_details end)MaxLogin, max(case when entry_details = 'logout'then timestamp_details end)MaxLogout, sum(case when entry_details = 'login' then 1 end) Totallogins, sum(case when entry_details = 'logout' then 1 end) Totallogouts from employee_checkin_details group by employeeid ) select * from cte c left join employee_details e on c.employeeid=e.employeeid and isdefault = 'true'
The solution in MySQL Syntax: SELECT ec.employeeid, MAX(ed.phone_number) AS employee_default_phone_number, COUNT(*) AS totalentry, SUM(entry_details = 'login') AS totallogin, SUM(entry_details = 'logout') AS totallogout, MAX(CASE WHEN entry_details = 'login' THEN timestamp_details END) AS latestlogin, MAX(CASE WHEN entry_details = 'logout' THEN timestamp_details END) AS latestlogout FROM employee_checkin_details ec LEFT JOIN employee_details ed ON ec.employeeid = ed.employeeid AND ed.isdefault = 'true' GROUP BY ec.employeeid;
My Solution select ec.employeeid, max(ed.phone_number) as employee_default_phone_number, count(1) as totalentry, sum(case when entry_details = 'login' then 1 else 0 end) as totallogin, sum(case when entry_details = 'logout' then 1 else 0 end) as totallogout, max(case when entry_details = 'login' then timestamp_details else null end) as latestlogin, max(case when entry_details = 'logout' then timestamp_details else null end) as latestlogout from employee_checkin_details ec left join (select * from employee_details where isdefault = 'true') ed on ec.employeeid = ed.employeeid group by ec.employeeid
with count_cte as ( select *, count(entry_detail) over (partition by entry_detail, id) as activity_count from emp_login_details order by id, entry_detail, time_done desc ), time_cte as ( select *, max(time_done) over (partition by entry_detail, id) as latest_time from count_cte ), latest_time_cte as ( select * from time_cte where time_done = latest_time ) select lat_cte.*, emp_det.phone_number from latest_time_cte as lat_cte full outer join employee_details as emp_det on lat_cte.id = emp_det.emp_id and emp_det.isdefault = true where lat_cte.id is not null order by entry_detail
with final as ( select employeeid,count(1) as totalentry, sum(case when entry_details='login' then 1 else 0 end) as totallogin, sum(case when entry_details='logout' then 1 else 0 end) as totallogout, max(case when entry_details='login' then timestamp_details else 0 end ) as latestlogin, max(case when entry_details='logout' then timestamp_details else 0 end ) as latestlogout from employee_checkin_details group by employeeid) ,cte2 as( select f.*,ed.isdefault,ed.phone_number,ed.added_on,rank() over (partition by ed.employeeid order by ed.added_on desc) as rn from final f left join employee_details ed on f.employeeid=ed.employeeid) select * from cte2 where rn=1 select * from employee_details
# before joining, the table, filtered only rows with isdefault=true SELECT a.employeeid , b.phone_number, count(*) AS total_entry , SUM( CASE WHEN entry_details='login' THEN 1 ELSE 0 END ) AS Total_login, SUM( CASE WHEN entry_details='logout' THEN 1 ELSE 0 END ) AS Total_logout, MAX( CASE WHEN entry_details='logout' THEN timestamp_details END) AS 'latest_logout', MAX( CASE WHEN entry_details='login' THEN timestamp_details END) AS 'latest_login' FROM employee_checkin_details AS a LEFT JOIN (SELECT * FROM employee_details WHERE isdefault=true) AS b ON a.employeeid = b.employeeid GROUP BY a.employeeid;
In case of the default flag = false for 1000 employed, what happens if the phone number added recently is not a default phone number but the one added previously is the default number. In this case the row number would have rank for the latest record with had For employeeid = 1001 what if the records are in the order where the default phone number is not the one which had been added recently. For instance, if for any employee the data is in below order the query i believe would return incorrect results. What are your thoughts on this? employed employeeid phone_number added added_on 1000 9999 false 2023-01-01 1000 1111 true 2023-01-02 1000 2222 false 2023-01-03
Hit the like button on video for more interview problems 😊
the motivation you get when you solved the question by youself and used the 'mentos zindagi' approach at first ....
thank you so much sir for these amazing tutorials
Excellent
Thanks Ankit, your videos help us a lot.
Solution for TWIST scenario Without using Ranking
with default_ph_num as (
select distinct employeeid,
case when count(case when isdefault='false' then isdefault else null end) = count(isdefault)
then FIRST_VALUE(phone_number) over(partition by employeeid order by added_on desc) else
phone_number end default_phone
from employee_details_twist
group by employeeid, phone_number, added_on
)
select cte.employeeid, dp.default_phone, count(entry_details) totalentry,
sum(case when entry_details='login' then 1 else 0 end) as totallogin,
sum(case when entry_details='logout' then 1 else 0 end) as totallogout,
max(case when entry_details='login' then timestamp_details else null end) as latestlogin,
max(case when entry_details='login' then timestamp_details else null end) as latestlogout
from employee_checkin_details cte
inner join default_ph_num dp
on cte.employeeid=dp.employeeid
group by cte.employeeid, dp.default_phone
Ankit after solving all the previous videos from this playlist, I have been able to solve this question like mentos life. Thanks a lot to you man for building our sql base so solid. It really feels great now.
Excellent!!
million dollar satisfaction when i used the 'mentos zindagi' approach at first
Awesome 😎
Your videos helped to gain knowledge about SQL.
I'm a 35 year old 10th passed guy who got a Data Engineer role recently.
If you can do, i can do.
You are a rockstar 💪😎
Congratulations sir,can you please provide your road map and how you got job what are thes skills you gained to ge this job
Please reply it will me helpful for people having career gap like me🙏
Brother what is difficulty level of SQL questions that u being asked is this type of questions like in this video asked in interview?
Sir can you please give me some tips how you got Data Engineer although you are 10th pass
Aur yaha hum IIT se mtech karke har interview me select hoke bhi last me reject ho rhe ye bolke ki they have got someone with experience 😅
Thank you Sir! once again.
I have reached up to that level in one year of watching your videos where I directly think solutions like "Mentos zindagi"😅 I can directly think of approach in my mind on how will I solve it and you do the same.
Lots of success and best wishes to you ❤
Excellent!
very good effort.
please share the mentos zindagi solution
Wow. I can't believe I solved this myself and later checked with the soln and I solved it by the shorter method (mentos zindagi). I have been following your playlist for SQL . I have completed the Medium level interview questions playlist and now focusing on complex queries playlist. Your videos helped me build that thinking skill or intuition to solve problems in a shorter way.
Thanks!
SELECT c.employeeid,ed.phone_number as e_default_phone_no,COUNT(1) AS total_entries,
SUM(CASE WHEN entry_details='login' then 1 else 0 end) as total_login,
SUM(CASE WHEN entry_details='logout' then 1 else 0 end) as total_logout,
MAX(CASE WHEN entry_details='login' then timestamp_details end) as latest_login,
MAX(CASE WHEN entry_details='logout' then timestamp_details end) as latest_logout
FROM employee_checkin_details c
LEFT JOIN employee_details ed ON
c.employeeid=ed.employeeid and isdefault='true'
GROUP BY c.employeeid;
Excellent ✌️
Incredible explanation!!!
Love You
So glad!
Sir when solving the first time it's some we are understanding by your explanation but it seem to be very deficult at first time...
My question is by seeing the question nothing is coming in mind means how to solve how resolve how to break the problem statement..
And can we able to solve the other problems by practicing more and more???
That and conditions on left join 💫😍
Great explanation sir, different approach to the same problem is very helpful. Thankyou so much!!
Most welcome!
Bhai maza aya mentos zindagi se.
Hello this is my approach
with base as(
select employeeid,phone_number as default_number
from tableName1
where isdefault='true'),base1 as(
select employeeid,count(entry_details) as total_entry,
sum(case when entry_details='login' then 1 else 0 end) as total_logins,
sum(case when entry_details='logout' then 1 else 0 end) as
total_logouts,
max(case when entry_details='login' then timestamp_details end) as latest_login,
max(case when entry_details='logout' then timestamp_details end) as latest_logout
from tableName
group by employeeid)
select ifnull(e.default_number,'none') as default_number,c.* from base as e right join base1 as c on e.employeeid=c.employeeid
Hello Sir, How to be Data Engineer as fresher? No one is hiring freshers. Please guide me
Love this❤
could you please post the solution with mentos zindagi as well :)
How to calculate total login time and time logged out if there are login and logout time in between
sir please video on data analyst SQL interview questions
SQL is the same for everyone, all data roles.
Solution for TWIST statement Using Ranking
;
WITH cte as (
Select e2.employeeid, e2.isdefault,e2.phone_number,e2.added_on
, COUNT(entry_details) as totalentry
, COUNT(CASE WHEN entry_details = 'login' THEN timestamp_details END) as totallogin
, COUNT(CASE WHEN entry_details = 'logout' THEN timestamp_details END) as totallogout
, MAX(CASE WHEN entry_details = 'login' THEN timestamp_details END) as latestlogin
, MAX(CASE WHEN entry_details = 'logout' THEN timestamp_details END) as latestlogout
, DENSE_RANK() over(PARTITION BY e2.employeeid ORDER BY e2.added_on DESC) as RNK
from employee_checkin_details as e1 LEFT JOIN employee_details_twist as e2
on e1.employeeid = e2.employeeid
group by e2.employeeid,e2.isdefault,e2.phone_number,e2.added_on )
select *
from cte
where RNK = 1
Brilliant 👍
it is wrong ig when i ran it on my sql server
@@stat_life check the table names i have used diff table name for the twist one & its working
@@nachiketpalsodkar4356 yeah i corrected that in my query but still error
@@stat_life Kindly write the query line by line again also i checked it at my side & its working absolutely fine buddy!!!!
can i do it in the my sql as well or else only in ms sql?
MySQL is also fine
thanks for your replay sir@@ankitbansal6
with cte as(
select employeeid,
count(entry_details) Total_enters ,
max(case when entry_details = 'login'then timestamp_details end)MaxLogin,
max(case when entry_details = 'logout'then timestamp_details end)MaxLogout,
sum(case when entry_details = 'login' then 1 end) Totallogins,
sum(case when entry_details = 'logout' then 1 end) Totallogouts
from employee_checkin_details
group by employeeid )
select *
from cte c
left join employee_details e on c.employeeid=e.employeeid and isdefault = 'true'
The solution in MySQL Syntax:
SELECT
ec.employeeid,
MAX(ed.phone_number) AS employee_default_phone_number,
COUNT(*) AS totalentry,
SUM(entry_details = 'login') AS totallogin,
SUM(entry_details = 'logout') AS totallogout,
MAX(CASE WHEN entry_details = 'login' THEN timestamp_details END) AS latestlogin,
MAX(CASE WHEN entry_details = 'logout' THEN timestamp_details END) AS latestlogout
FROM employee_checkin_details ec
LEFT JOIN employee_details ed
ON ec.employeeid = ed.employeeid AND ed.isdefault = 'true'
GROUP BY ec.employeeid;
My Solution
select ec.employeeid,
max(ed.phone_number) as employee_default_phone_number,
count(1) as totalentry,
sum(case when entry_details = 'login' then 1 else 0 end) as totallogin,
sum(case when entry_details = 'logout' then 1 else 0 end) as totallogout,
max(case when entry_details = 'login' then timestamp_details else null end) as latestlogin,
max(case when entry_details = 'logout' then timestamp_details else null end) as latestlogout
from employee_checkin_details ec
left join (select * from employee_details where isdefault = 'true') ed
on ec.employeeid = ed.employeeid
group by ec.employeeid
@ankitbansal6 please rate this solution
with count_cte as
(
select *,
count(entry_detail) over (partition by entry_detail, id) as activity_count
from emp_login_details
order by id, entry_detail, time_done desc
), time_cte as (
select *,
max(time_done) over (partition by entry_detail, id) as latest_time
from count_cte
), latest_time_cte as (
select * from time_cte
where time_done = latest_time
)
select lat_cte.*, emp_det.phone_number
from latest_time_cte as lat_cte
full outer join employee_details as emp_det
on lat_cte.id = emp_det.emp_id and emp_det.isdefault = true
where lat_cte.id is not null
order by entry_detail
with final as (
select employeeid,count(1) as totalentry,
sum(case when entry_details='login' then 1 else 0 end) as totallogin,
sum(case when entry_details='logout' then 1 else 0 end) as totallogout,
max(case when entry_details='login' then timestamp_details else 0 end ) as latestlogin,
max(case when entry_details='logout' then timestamp_details else 0 end ) as latestlogout
from employee_checkin_details
group by employeeid)
,cte2 as(
select f.*,ed.isdefault,ed.phone_number,ed.added_on,rank() over (partition by ed.employeeid order by ed.added_on desc) as rn
from final f
left join employee_details ed on f.employeeid=ed.employeeid)
select * from cte2 where rn=1
select * from employee_details
# before joining, the table, filtered only rows with isdefault=true
SELECT a.employeeid , b.phone_number, count(*) AS total_entry ,
SUM(
CASE
WHEN entry_details='login' THEN 1
ELSE 0
END
) AS Total_login,
SUM(
CASE
WHEN entry_details='logout' THEN 1
ELSE 0
END
) AS Total_logout,
MAX( CASE WHEN entry_details='logout' THEN timestamp_details END) AS 'latest_logout',
MAX( CASE WHEN entry_details='login' THEN timestamp_details END) AS 'latest_login'
FROM employee_checkin_details AS a
LEFT JOIN (SELECT * FROM employee_details WHERE isdefault=true) AS b
ON a.employeeid = b.employeeid
GROUP BY a.employeeid;
In case of the default flag = false for 1000 employed, what happens if the phone number added recently is not a default phone number but the one added previously is the default number. In this case the row number would have rank for the latest record with had
For employeeid = 1001
what if the records are in the order where the default phone number is not the one which had been added recently.
For instance, if for any employee the data is in below order the query i believe would return incorrect results.
What are your thoughts on this?
employed
employeeid phone_number added added_on
1000 9999 false 2023-01-01
1000 1111 true 2023-01-02
1000 2222 false 2023-01-03
Create statement:
CREATE TABLE employee_checkin_details
(
employeeid INT,
entry_details VARCHAR(512),
timestamp_details VARCHAR(512)
);
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1000', 'login', '2023-06-16 01:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1000', 'login', '2023-06-16 02:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1000', 'login', '2023-06-16 03:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1000', 'logout', '2023-06-16 12:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1001', 'login', '2023-06-16 01:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1001', 'login', '2023-06-16 02:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1001', 'login', '2023-06-16 03:00:15.34');
INSERT INTO employee_checkin_details (employeeid, entry_details, timestamp_details) VALUES ('1001', 'logout', '2023-06-16 12:00:15.34');
CREATE TABLE employee_details
(
employeeid INT,
phone_number INT,
isdefault VARCHAR(512)
);
INSERT INTO employee_details (employeeid, phone_number, isdefault) VALUES ('1001', '9999', 'false');
INSERT INTO employee_details (employeeid, phone_number, isdefault) VALUES ('1001', '1111', 'false');
INSERT INTO employee_details (employeeid, phone_number, isdefault) VALUES ('1001', '2222', 'true');
INSERT INTO employee_details (employeeid, phone_number, isdefault) VALUES ('1003', '3333', 'false');
QUERY:
select cd.employeeid,
MIN( phone_number),
COUNT(DISTINCT timestamp_details),
COUNT( CASE WHEN entry_details = 'login' THEN timestamp_details ELSE null END) as tot_login,
COUNT( CASE WHEN entry_details = 'logout' THEN timestamp_details ELSE null END) as tot_logout,
MAX( CASE WHEN entry_details = 'login' THEN timestamp_details ELSE null END) as latest_login,
MAX( CASE WHEN entry_details = 'logout' THEN timestamp_details ELSE null END) as latest_logout
from employee_checkin_details cd
LEFT JOIN employee_details d
ON cd.employeeid = d.employeeid and isdefault='true'
GROUP BY 1