Видео

Here's my approach MySQL, with cte as (select *, date(activity_time) as date, lead(activity_time) over(partition by employee_id, date(activity_time) order by activity_time ) as nxt_time from swipe) select employee_id, date, SUM(TIME_TO_SEC(hrs)) / 3600 as Total_hrs, SUM(TIME_TO_SEC(Office_hrs)) / 3600 as Office_hrs from ( Select *, case when rn %2=0 then 0 else hrs end as Office_hrs from( select employee_id, date(activity_time) as date, timediff(nxt_time, activity_time) as hrs, row_number() over (partition by employee_id,date(activity_time)) as rn from cte where nxt_time is not null) as A ) B group by employee_id, date @ankit Please check and correct me if I'm wrong.

Thank you🙏

With all_matches as ( select team, SUM(matches_played) as total_matches_played from (select team_1 as team, COUNT(*) as matches_played from icc_world_cup_1 group by team_1 UNION all select team_2 as team, COUNT(*) as matches_played from icc_world_cup_1 group by team_2)a group by team), Winner as( select winner, COUNT(*) as wins from icc_world_cup_1 group by winner) Select team, total_matches_played, COALESCE(wins,0) as wins_match, total_matches_played-COALESCE(wins,0) as matches_lost from all_matches as a left join winner as w on A.team=w.winner order by wins_match desc;

Simple Solution 😀 with cte as (select employee_id, activity_type, convert(date, activity_time) as dates, convert(time, activity_time) as times, case when activity_type = 'logout' then lag(convert(time, activity_time), 1) over (partition by employee_id, convert(date, activity_time) order by employee_id) else null end as prev_times from swipe) select employee_id, dates, (datepart(hh,max(times)) - datepart(hh,min(times))) as total_spent_hrs, sum((datepart(hh,times)) - datepart(hh,prev_times)) as productive_hrs from cte group by employee_id, dates

--select * from swipe with cte as ( select * , activity_time::DATE as login_day, (activity_time_2 - activity_time) as time_inside from (select * , lead(activity_type , 1 ) over ( partition by employee_id order by activity_time ) as activity_type_2 , LEAD( activity_time ,1 ) over (partition by employee_id order by activity_time ) as activity_time_2 from swipe ) where activity_type = 'login' ) , info as ( select *, sum(time_inside) over(partition by employee_id,login_day order by login_day) as Time_spent ,count(1) over(partition by employee_id,login_day order by login_day) as swipes from cte ) select DISTINCT employee_id,login_day,time_spent from info select employee_id,login_day,max(swipes) from info group by employee_id ,login_day

with cte as(select DISTINCT * from (select *, Lead(action ,1) over(partition by emp_id order by time) as second , Lead(time,1) over(partition by emp_id order by time) as out_time from hospital ) as a where action = 'in' ) select emp_id , action , time from cte where second is null

with cte as(select DISTINCT * from (select *, Lead(action ,1) over(partition by emp_id order by time) as second , Lead(time,1) over(partition by emp_id order by time) as out_time from hospital ) as a where action = 'in' ) select count(emp_id) as 'total number of emp inside' from cte where second is null

with cte as( select seat_id, free,lead(free,1)over(order by seat_id) as new from cinema) select seat_id ,free from cte where new=1 and free=1;

Mine is 7.5 times

Mind blowing!! initially when i listen the problem statement ,i thought i couldn't thought i would be able to understand the solution itself. but the way we break it down is truely awesome :)

Partition employee_id, activity_type using row_number self join on login.row_number = logout.row_number hence we can join on 1st login with 1st logout group by and sum QUERY: with cte as (select *, row_number() over (partition by employee_id, activity_type order by employee_id, activity_time) as rn, activity_time :: date as datee from swipe) select inn.employee_id, inn.datee, sum(out.activity_time-inn.activity_time) from cte inn join cte out on inn.employee_id = out.employee_id and inn.rn = out.rn where inn.activity_type = 'login' group by inn.employee_id, inn.datee

Incase if the rows are way to high how do we dynamically bring them as column names, is there a way to do it? It's not possible to give names individually . In my case I should bring dates as column names from the row.

----query 1 select employee_id,cast( activity_time as date) as date,max(activity_time)-min(activity_time) as time_spend_at_office from swipe group by employee_id,cast( activity_time as date) order by employee_id,cast( activity_time as date); --query 2 WITH CTE AS ( select employee_id,cast(activity_time as date) as date,activity_type, activity_time-LAG(activity_time) OVER(partition by employee_id,cast( activity_time as date) order by activity_time) AS time_spend_in_one_swipe from swipe) select employee_id,date,SUM(time_spend_in_one_swipe) as actual_time_spend from CTE where activity_type='logout' group by employee_id,date;

My SQL sol: with cte_1 as( select a.company_id ,a.user_id, case when a.language = 'English' and b.language = 'German' then 1 else 0 end as flag from company_users a join company_users b on a.company_id = b.company_id and a.user_id = b.user_id and a.language < b.language ) select company_id,sum(flag) as user_count from cte_1 group by 1 having sum(flag) >=2

;with cte as ( select * ,lead(activity_type,1,activity_type) over(partition by employee_id, convert(date, activity_time) order by activity_time) lead_activity_type ,lead(activity_time,1,activity_time) over(partition by employee_id, convert(date, activity_time) order by activity_time) lead_activity_time ,convert(date, activity_time) activity_day ,datediff(hour, min(activity_time) over(partition by employee_id, datepart(day, activity_time)),max(activity_time) over(partition by employee_id, datepart(day, activity_time))) total_hours from swipe ) select c.employee_id ,activity_day ,total_hours ,sum(datediff(hour, activity_time, c.lead_activity_time)) productive_hours from cte c where activity_type = 'login' and lead_activity_type = 'logout' group by c.employee_id ,activity_day ,total_hours order by activity_day

SELECT emp_name FROM emp_manager e WHERE salary > ( SELECT salary FROM emp_manager WHERE emp_id = e.manager_id ); this is another approach

select emp.emp_name as Employee_name from emp_manager as emp inner join emp_manager as manager on emp.manager_id = manager.emp_id where emp.salary > manager.salary Thanks Ankit sr

with cte as( select *, case when lead(activity_type) over(partition by employee_id order by day(activity_time)) = 'logout' then datediff(hour,activity_time , lead(activity_time) over(partition by employee_id order by day(activity_time))) else null end as b from swipe ) select employee_id,cast(activity_time as date) as date,datediff(hour,min(activity_time),max(activity_time)) as login_time, sum(b) as productive_hrs from cte group by employee_id,cast(activity_time as date) order by employee_id

My solution in postgresql WITH DATA AS ( select * from ( SELECT *,ACTIVITY_TIME :: DATE AS ACTIVITY_DATE, LEAD(activity_time,1) over (partition by employee_id,activity_time::date order by activity_time) as logout_time FROM practise."swipe" ) where activity_type = 'login') SELECT EMPLOYEE_ID, ACTIVITY_DATE, ROUND(EXTRACT(EPOCH FROM (MAX(LOGOUT_TIME) - MIN(ACTIVITY_TIME)))/3600) AS OFFICE_HRS, SUM(ROUND(EXTRACT(EPOCH FROM (LOGOUT_TIME - ACTIVITY_TIME))/3600)) AS PRODUCTIVE_HRS FROM DATA GROUP BY EMPLOYEE_ID,ACTIVITY_DATE

Hi Ankit, I really appreciate your efforts. I learnt a lot from your videos and I've become confident in SQL Thanks a ton.

Thanks for sharing Ankit, I've learned so many things about SQL from you. Really grateful. with employee_activity as( select *, lead(activity_time) over(partition by employee_id, activity_time::date order by activity_time) log_out_time from swipe ) select employee_id, activity_time::date, extract(hour from max(log_out_time)-min(activity_time)) as total_working_hours, extract(hour from sum(log_out_time-activity_time)) as actual_working_hours from employee_activity where activity_type = 'login' group by 1,2

Hi really helpful logic Select* from production where sales>50 How to optimize queries above one This is interview question please guide me

Narayanmurthy's time pass activity to track employees.

Please check my solution in sql server select t1.employee_id,DATEdiff(hour,MIN(t1.activity_time),max(t2.activity_time)) total_hours, sum(DATEDIFF(hour,t1.activity_time,t2.activity_time)) as Inside_hours from ( select employee_id,activity_type,activity_time,ROW_NUMBER()over(partition by employee_id order by activity_time)rn from swipe where activity_type = 'login')t1 join ( select employee_id,activity_type,activity_time,ROW_NUMBER()over(partition by employee_id order by activity_time)rn from swipe where activity_type = 'logout')t2 on t1.employee_id = t2.employee_id and t1.rn = t2.rn group by t1.employee_id,day(t1.activity_time)

tried in sql developer create view minvalue as select distinct employee_id,min(activity_time) over (partition by to_char(activity_time,'dd'),employee_id)mintime from swipe where activity_type='login' order by employee_id create view maxvalue as select distinct employee_id,max(activity_time) over(partition by to_char(activity_time,'dd'),employee_id)maxtime from swipe where activity_type='logout' order by employee_id select m1.employee_id,m1.mintime,m2.maxtime, m2.maxtime - m1.mintime from minvalue m1 join maxvalue m2 on m1.employee_id=m2.employee_id and to_char(m1.mintime,'dd')=to_char(m2.maxtime,'dd') order by employee_id

Easy in dsa but difficult in sql😆. Here is my solution using self join - with cte1 as( select t1.customer,t1.start_loc from travel_data t1 left join travel_data t2 on t1.customer=t2.customer and t1.start_loc=t2.end_loc where t2.start_loc is null ) ,cte2 as ( select t1.customer,t1.end_loc from travel_data t1 left join travel_data t2 on t1.customer=t2.customer and t1.end_loc=t2.start_loc where t2.start_loc is null ) select cte1.*,cte2.end_loc from cte1 inner join cte2 on cte1.customer=cte2.customer

select a.*,b.total_hours from (with cte as (select *, cast (activity_time as date) as dates,lead(activity_time) over(partition by employee_id,cast(activity_time as date) order by activity_time) as logout from swipes ) select employee_id,dates,sum(extract(hour from logout) -extract(hour from activity_time) )as inside_hours from cte where activity_type='login' group by 1,2) a join ( with cte2 as (select employee_id,cast(activity_time as date),extract (hour from activity_time) from swipes group by 1,2,activity_time order by 1,2,3) select employee_id,activity_time as dates, max(extract)-min(extract) as total_hours from cte2 group by 1,2)b on a.employee_id=b.employee_id and a.dates=b.dates

with cte_1 as ( select *,ROW_NUMBER()over(partition by employee_id order by activity_time) as rk_out from swipe where activity_type='logout'), cte_2 as ( select *,ROW_NUMBER()over(partition by employee_id order by activity_time) as rk_in from swipe where activity_type='login') ,cte_3 as (select a.*,b.activity_type as login ,b.activity_time as login_time from cte_1 a inner join cte_2 b on a.employee_id=b.employee_id and a.rk_out=b.rk_in) ,cte_4 as (select employee_id ,cast(activity_time as date) as dt, DATEDIFF(hour,login_time,activity_time) as in_off from cte_3 ) select employee_id ,dt, sum (in_off) as in_time from cte_4 group by employee_id ,dt order by employee_id

thank u very easy

I faced this in Clairvoyant Data Analyst Interview 3 weeks back.

Thanks for the SQL session. Sharing other way for 2nd part of question using self join and group by: with tab as ( select s1.employee_id, s1.activity_time as login_tym, s2.activity_time as logout_tym from swipe s1 inner join swipe s2 on s1.employee_id=s2.employee_id and s1.activity_time<s2.activity_time and s2.activity_type<>s1.activity_type and s1.activity_type='login'), tab2 as ( select employee_id,login_tym,min(logout_tym) as log_oo from tab group by employee_id,login_tym) select employee_id, sum(datediff(hour,login_tym,log_oo)) as efficient_hr from tab2 group by employee_id; --output emp hr 1 12 2 6

after trying for hours i can solve the 1st part:(I am a new SQL learner. Need to improve myself ) with CTE as ( select employee_id,cast(activity_time as date) as activity_day, case when activity_type='login' then min(activity_time) end as login_time, case when activity_type='logout' then max(activity_time) end as logout_time from swipe group by cast(activity_time as date) ,employee_id, activity_type) select employee_id, activity_day,min(login_time) as login, max(logout_time) as logout, DATEDIFF(HOUR, min(login_time),max(logout_time)) as office_timings from CTE group by employee_id,activity_day;

with cte as (select * ,(case when revenue < lead(revenue, 1, revenue + 1) over(partition by company order by year) then 1 else 0 end) as flag from company_revenue) select company from company_revenue where company not in (select company from cte where flag = 0) group by company;

with cte as (select *, case when activity_type = 'login' then hour(lag_1) - hour(activity_time) end as hour_difference from ( select * , lead(activity_time) over(partition by employee_id order by activity_time) as lag_1 from swipe) as A where case when activity_type = 'login' then hour(lag_1) - hour(activity_time) end is not null), cte_2 as ( select employee_id, date(activity_time) as date_2, max(hour(LV)-hour(activity_time)) as diff_hr_2 from( select * , last_value(activity_time) over(partition by employee_id, date(activity_time)) as LV from swipe) as B group by employee_id, date(activity_time) ) select cte.employee_id, date(cte.activity_time) as date, diff_hr_2 as total_hours, sum(cte.hour_difference) as production_hours from cte join cte_2 on cte.employee_id = cte_2.employee_id and date(cte.activity_time) = cte_2.date_2 group by employee_id, date(activity_time), diff_hr_2 order by date(cte.activity_time) Did in MYSQL workbench. It is really good. first Cte was to calculate total hours. Cte_2 was to calculate production hours. Joined both CTE in the main query.

Hi Ankit, I have solved using below method,, I have used substring to get the number value from quarter and we know there are only 3 possibilities if we add those numbers and used them as condition in case statement select store, case when sum(cast(substring(quarter,2,1) as int)) = 7 then 'Q3' when sum(cast(substring(quarter,2,1) as int)) = 6 then 'Q4' when sum(cast(substring(quarter,2,1) as int)) = 8 then 'Q2' end as missing_quarter from stores group by store;

select * from swipe; WITH cte AS ( SELECT employee_id, activity_type, DATE(activity_time) AS date, TIME_TO_SEC(TIME(activity_time)) AS time_in_seconds FROM swipe ), time_diff AS ( SELECT employee_id, date, MAX(CASE WHEN activity_type = 'logout' THEN time_in_seconds END) - MIN(CASE WHEN activity_type = 'login' THEN time_in_seconds END) AS total_seconds, SUM(CASE WHEN activity_type = 'login' THEN time_in_seconds END) AS total_login_seconds, SUM(CASE WHEN activity_type = 'logout' THEN time_in_seconds END) AS total_logout_seconds FROM cte GROUP BY employee_id, date ) SELECT employee_id, date, ROUND(total_seconds / 3600) AS hours_worked, ROUND((total_logout_seconds - total_login_seconds) / 3600) AS productive_hours FROM time_diff ORDER BY date;

good one

in my microsoft sql management studio there is no option of sql server native client 11.0

Video quality is pathetic.

Thank you very much!

Most simple explanation. Thanks

This video is pretty simple to understand and this expertise will come with practice.. I hope all the folks practice this at least once a day.. thanks so much Ankit

/***Total hours***/ with cte as(SELECT *, row_NUMBER()OVER(partition by employee_id,extract(day from activity_time) order by activity_time) as rw from swipe) select employee_id,Date(activity_time) as Dates, round((max(activity_time) - min(activity_time))/10000,0) as Total_hrs from cte group by 1,2 order by 2; /***Actual hours***/ Select * from swipe; WITH CTE AS(Select employee_id, case when activity_type = 'login' then activity_time END as login, case when activity_type = 'logout' then activity_time END as logout from swipe),V1 AS( SELECT *, LEAD(LOGOUT)OVER(partition by employee_id)AS NXT FROM CTE),V2 AS( SELECT *, ROUND((NXT-LOGIN))/10000 AS DIFFERENCE FROM V1 WHERE LOGIN IS NOT NULL) SELECT employee_id,DATE(login) AS DATES,ROUND(SUM(DIFFERENCE),0)AS TotalHrs FROM V2 group by 1,2;

Good one as always Ankit! Here's my approach: =================================== with actual_working_hrs as (select employee_id, DATETRUNC(DAY, activity_time) [login date], SUM([status hours]) [actual login hours] from (select *, DATEDIFF(HOUR, activity_time,LEAD(activity_time, 1) over(partition by employee_id, DAY(activity_time), MONTH(activity_time) order by activity_time)) [status hours] from swipe) s1 where activity_type = 'login' group by employee_id, DATETRUNC(DAY, activity_time)), net_login as (select Employee_id, DATETRUNC(DAY, activity_time) [login date], DATEDIFF(HOUR, MIN(activity_time), MAX(activity_time)) [net login hours] from swipe group by Employee_id, DATETRUNC(DAY, activity_time)) select a.*, [net login hours] from actual_working_hrs a join net_login n on a.employee_id = n.employee_id and a.[login date] = n.[login date] ===================================

My solution with cte as ( select department_id,AVG(salary) as Avgsal, (select Company_sal=AVG(salary) from emp e where e.department_id<>e1.department_id ) Company_sal from emp e1 group by department_id) select * from cte where avgsal<Company_sal

Excited to share that now you can practice sql interview problems on namaste sql 😍😍: www.namastesql.com/coding-problems

with cte as(select order_id,customer_id,order_date,order_amount, row_number() over (partition by customer_id order by order_Date) as rk from customer_orders ) select order_date, sum(case when rk=1 then 1 else 0 end) as new, sum(case when rk>1 then 1 else 0 end) as repeat from cte group by order_date

with cte as ( select user_id,sum(case when filter_room_types like '%entire%' then 1 else 0 end) as entire, sum(case when filter_room_types like '%shared%' then 1 else 0 end) as shared, sum(case when filter_room_types like '%private%' then 1 else 0 end) as private from airbnb_searches group by user_id) select 'Entire' as value, sum(entire) as no_of_searches from cte union all select 'Shared' as value,sum(shared) as no_of_searches from cte union all select 'Private' as value,sum(private) as no_of_searches from cte order by no_of_searches desc