PayPal Data Engineer SQL Interview Question (and a secret time saving trick)

[@aryakaagnihotri3210]

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

[@ankitbansal6]

Excellent

[@ankitbansal6]

Hit the like button on video for more interview problems 😊

[@SaiKiranNemali]

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

[@MixedUploader]

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.

[@ankitbansal6]

Excellent!!

[@lucifer_878]

million dollar satisfaction when i used the 'mentos zindagi' approach at first

[@ankitbansal6]

Awesome 😎

[@paressh]

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.

[@ankitbansal6]

You are a rockstar 💪😎

[@sandhyarani2342]

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🙏

[@rohitgade2382]

Brother what is difficulty level of SQL questions that u being asked is this type of questions like in this video asked in interview?

[@agarwalaarzoo3738]

Sir can you please give me some tips how you got Data Engineer although you are 10th pass

[@manashjyotiborah530]

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 😅

[@HarshKukreja052]

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 ❤

[@ankitbansal6]

Excellent!

[@RohitKumar-zm3nw]

very good effort.

[@sanachunarkar6606]

please share the mentos zindagi solution

[@rajkumarrajan8059]

Incredible explanation!!! Love You

[@ankitbansal6]

So glad!

[@mdshahbaz4766]

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???

[@avi8016]

Great explanation sir, different approach to the same problem is very helpful. Thankyou so much!!

[@ankitbansal6]

Most welcome!

[@Mathematica1729]

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;

[@ankitbansal6]

Excellent ✌️

[@Jignesh_Kachhad]

Hello Sir, How to be Data Engineer as fresher? No one is hiring freshers. Please guide me

[@leojoyjr2755]

That and conditions on left join 💫😍

[@paressh]

Bhai maza aya mentos zindagi se.

[@rishavvv6441]

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

[@urvxfvdzrnp]

Love this❤

[@sanachunarkar6606]

could you please post the solution with mentos zindagi as well :)

[@yashkanojiya1146]

My solution select c.employeeid,count(c.employeeid) as total_entry, (select max(phone_number) from employee_details e where e.employeeid = c.employeeid and isdefault =1) as default_phone_num, count(case when entry_details = 'login' then 1 else null end ) as total_login, count(case when entry_details = 'logout' then 1 else null 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 group by c.employeeid;

[@gtg_gaming]

How to calculate total login time and time logged out if there are login and logout time in between

[@avinashmirchandani]

sir please video on data analyst SQL interview questions

[@ankitbansal6]

SQL is the same for everyone, all data roles.

[@rohankalvala7191]

can i do it in the my sql as well or else only in ms sql?

[@ankitbansal6]

MySQL is also fine

[@rohankalvala7191]

thanks for your replay sir@@ankitbansal6

[@rohanchoudhary672]

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

[@nachiketpalsodkar4356]

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

[@ankitbansal6]

Brilliant 👍

[@stat_life]

it is wrong ig when i ran it on my sql server

[@nachiketpalsodkar4356]

@@stat_life check the table names i have used diff table name for the twist one & its working

[@stat_life]

@@nachiketpalsodkar4356 yeah i corrected that in my query but still error

[@nachiketpalsodkar4356]

@@stat_life Kindly write the query line by line again also i checked it at my side & its working absolutely fine buddy!!!!

[@ashisanshuman8873]

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;

[@Vaibha293]

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'

[@KoushikT]

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

[@kapilsaini2741]

@ankitbansal6 please rate this solution

[@sahilummat8555]

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

[@aishwaryasavant]

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

[@ଲିପ୍ସା]

# 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;

[@meghnasoni]

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