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

In this video we will discuss a PayPal data engineer sql interview problem. We will solve it with 2 methods and also going to solve it with a twist.

00:00 : understanding the problem
03:10 : the secret trick
06:30 : aam zindagi solution (normal life)
16:28 : mentos zindagi solution (mentos life)
23:35 : the twist ;)

data:
employee_checkin_details:
employeeid ,entry_details, timestamp_details
1000 , login , 2023-06-16 01:00:15.34
1000 , login , 2023-06-16 02:00:15.34
1000 , login , 2023-06-16 03:00:15.34
1000 , logout , 2023-06-16 12:00:15.34
1001 , login , 2023-06-16 01:00:15.34
1001 , login , 2023-06-16 02:00:15.34
1001 , login , 2023-06-16 03:00:15.34
1001 , logout , 2023-06-16 12:00:15.34

employee_details:
employeeid , phone_number , isdefault
1001 ,9999 , false
1001 ,1111 , false
1001 ,2222 , true
1003 ,3333 , false

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer #paypal

[ankitbansal]

Hit the like button on video for more interview problems 😊

[aryakaagnihotri]

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

[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.

[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

[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.

[Mathematica]

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;

[HarshKukreja]

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 ❤

[rishavvv]

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

[avi]

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

[mdshahbaz]

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

[yashkanojiya]

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;

[nachiketpalsodkar]

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

[gtg_gaming]

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

[sanachunarkar]

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

[rohankalvala]

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

[ashisanshuman]

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;

[Vaibha]

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

[rawat]

Hi Sir
My Way:

with cte as(
select employeeid, count(*) 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
group by employeeid)
select t1.employeeid, t1.totalentry, t1.totallogin, t1.latestlogin, t1.latestlogout, t2.phone_number
from cte t1 left join (
select * from (
select *,
rank() over(partition by employeeid order by added_on desc) as rn
from employee_details)temp
where (rn=1 and isdefault = 'false') or (rn=1 and isdefault = 'true')
) t2
on t1.employeeid = t2.employeeid

[meghnasoni]

Create statement:


CREATE TABLE employee_checkin_details
(
employeeid INT,
entry_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