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

[@ankitbansal6]

Hit the like button on video for more interview problems 😊

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

[@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 😅

[@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 ✌️

[@rajkumarrajan8059]

Incredible explanation!!!
Love You

[@ankitbansal6]

So glad!

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

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

[@avi8016]

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

[@ankitbansal6]

Most welcome!

[@leojoyjr2755]

That and conditions on left join 💫😍

[@paressh]

Bhai maza aya mentos zindagi se.

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

[@Jignesh_Kachhad]

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

[@urvxfvdzrnp]

Love this❤

[@sanachunarkar6606]

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

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

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

[@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!!!!

[@rawat7203]

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

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

[@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;

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

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

[@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;