Це відео не доступне.
Перепрошуємо.
PayPal Data Engineer SQL Interview Question (and a secret time saving trick)
Вставка
- Опубліковано 1 сер 2024
- 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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer #paypal
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
Love this❤
Incredible explanation!!!
Love You
So glad!
Great explanation sir, different approach to the same problem is very helpful. Thankyou so much!!
Most welcome!
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!!
That and conditions on left join 💫😍
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 ✌️
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
million dollar satisfaction when i used the 'mentos zindagi' approach at first
Awesome 😎
Bhai maza aya mentos zindagi se.
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 😅
How to calculate total login time and time logged out if there are login and logout time in between
Hello Sir, How to be Data Engineer as fresher? No one is hiring freshers. Please guide me
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???
could you please post the solution with mentos zindagi as well :)
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
sir please video on data analyst SQL interview questions
SQL is the same for everyone, all data roles.
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;
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
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
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!!!!
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
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;
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