Acies Global SQL Interview Question | Employee Swipe in and Swipe out

[@ankitbansal6]

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

[@myjourney352]

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

[@saralavasudevan5167]

Great solve Ankit! Below was my approach:
with total_time as
(
select *,
min(activity_time) over(partition by employee_id, datepart(day,activity_time)) as login_time,
max(activity_time) over(partition by employee_id, datepart(day,activity_time)) as logout_time,
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))) as time_logged,
case when activity_type = 'login' then lead(activity_time,1) over(partition by employee_id, datepart(day,activity_time) order by employee_id) end as prod_login
from swipe
)
select employee_id, cast(activity_time as date) as thedate, time_logged as total_hours,
sum(datediff(hour, activity_time, prod_login)) as productive_hours
from total_time
group by employee_id, cast(activity_time as date), time_logged

[@princesrivastava4475]

nice

[@princesrivastava4475]

I can relate to this que. as we all are doing this in office.
Thank you so much Ankit Sir

[@Shubhamjaiswal-ex2oc]

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

[@sushantdalela7350]

I have use in below query in Mysql
with cte as(
select *, cast(activity_time as date) as activity_day,
lead(activity_time,1) over(partition by employee_id, cast(activity_time as date) order by activity_time) as logout_time
from swipe)
select employee_id, activity_day,
sum(TIME_FORMAT(TIMEDIFF(logout_time,activity_time), '%H hours, %i minutes')) AS time_taken
from cte
where activity_type = 'login'
group by employee_id, activity_day

[@sukanyaiyer2671]

--Alternative way--
select employee_id,
date,
total_logout-total_login as productive_hrs,
totalhrs
from(
select employee_id,
cast(activity_time as date) as date,
SUM(case when activity_type ='login' then datepart(hour,activity_time) end) as total_login,
SUM(case when activity_type ='logout' then datepart(hour,activity_time) end) as total_logout,
datepart(hour,max(activity_time) - min(activity_time)) as totalhrs
from swipe

group by employee_id, cast(activity_time as date)
) as a

[@arpanscreations6954]

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

[@mrx...223]

Are u fresher? And what is the intensity of other questions?? Are they tough compare to this one.
And further to sql what other tools and technology question they ask you?

[@arpanscreations6954]

@@mrx...223 I'm having 4 YOE. other questions were medium level

[@mrx...223]

@@arpanscreations6954
Excel, sql and what other skills they asked questions to you?

[@arpanscreations6954]

@@mrx...223 other than SQL they asked about pyspark, python.

[@ethyria7685]

Q2.
SELECT employee_id, CAST(activity_time as date) as day, SUM(diff) as productive_hrs
FROM
(SELECT *,
dateDIFF(hh, (activity_time), LEAD(activity_time)OVER(PARTITION BY employee_id ORDER BY activity_time)) as diff
FROM swipe) a
WHERE activity_type = 'login'
GROUP BY employee_id, CAST(activity_time as date)

[@user-dw4zx2rn9v]

Mysql solution:
select state,
max(case when max_city = population then city end) as maxi_city,
max(case when min_city = population then city end) as maxi_city
from (
select state,city, population,
max(population) over (partition by state) as max_city,
min(population) over (partition by state) as min_city
from city_population
) as x
group by state

[@debabratabar2008]

My solution --> used join with login & logout activity . its a bit of complex
select
a.e_date,
a.employee_id,
max(b.activity_time) - min(a.activity_time) as tot_ofc_time,
sum ( ( b.activity_time- a.activity_time)) as tot_act_time
from (
select
date(activity_time ) as e_date,
employee_id ,
activity_type ,
activity_time ,
rank() over(partition by date(activity_time ) , employee_id order by activity_time )
from swipe
where activity_type = 'login'
) a join (
select
date(activity_time ) as e_date,
employee_id ,
activity_type ,
activity_time,
rank() over(partition by date(activity_time ) , employee_id order by activity_time )
from swipe
where activity_type = 'logout' )
b on a.e_date = b.e_date
and a.employee_id = b.employee_id
-- and a.activity_time < b.activity_time
and a.rank = b.rank
group by 1 ,2
order by 1, 2 ;

[@Aman-lv2ee]

I have solved in this way:
with temp as (
Select *,
case when activity_type = 'logout' then lag(activity_time)over(partition by employee_id, date(activity_time) order by activity_time asc) end as lg,
datediff(hour, case when activity_type = 'logout' then lag(activity_time)over(partition by employee_id, date(activity_time) order by activity_time asc) end, activity_time) as diff
from swipe
)
select
employee_id, date(ACTIVITY_TIME), sum(diff) as productive_time, datediff(hour,min(activity_time), max(activity_time)) as total_time_spent
from temp
group by employee_id, date(ACTIVITY_TIME);

[@vinil9212]

with cte as (
select *,cast(activity_time as date) as activity_date, LEAD(activity_time) OVER (PARTITION BY employee_id ORDER BY activity_time asc) AS logout_time
from swipe
),
cte2 as (
select employee_id, activity_time,logout_time,activity_date from cte
WHERE activity_type= 'login'),
total as(
select employee_id,activity_date, datediff(hour,min(activity_time),max(logout_time)) as total_hour from cte2
group by employee_id,activity_date),
hour_cloked as (
select employee_id,activity_date, sum(datediff(hour,activity_time,logout_time)) as total_hour from cte2
group by employee_id,activity_date)
select employee_id,sum(total_hour) from hour_cloked
group by employee_id;

[@karthikeyanr1171]

with cte as (
select employee_id,activity_type, date(activity_time) as activity_date, activity_time, row_number() over(partition by employee_id, activity_type, date(activity_time) order by activity_time) as rnk
from `swipe`
)
,cte1 as(
select a.employee_id, a.activity_time as login_date, b.activity_time as logout_date, a.activity_date from cte a inner join cte b on a.employee_id=b.employee_id and a.activity_date=b.activity_date and a.rnk=b.rnk and a.activity_time < b.activity_time)
select employee_id, activity_date, extract(hour from max(logout_date)-min(login_date)) as total_hour,
sum(date_diff(logout_date, login_date, hour)) as inside_hour from cte1
group by 1,2

[@Chathur732]

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.

[@tanisha.j16]

can we do this using first_value and last_value window function? I am getting wrong answer, can you explain if it's possible?

[@mithunmahato309]

Narayanmurthy's time pass activity to track employees.

[@ankitbansal6]

haha

[@mukeshkumaryadav350]

In postgresql
with cte as(
SELECT *, DATE(activity_time) as date,
lead(activity_time,1) OVER(PARTITION BY employee_id,DATE(activity_time) ORDER BY activity_time) as log_out_time
from swipe)
SELECT employee_id, activity_time::DATE,
EXTRACT(HOUR FROM (max(log_out_time)-min(activity_time))) as total_time,
EXTRACT(hour from SUM(log_out_time-activity_time)) as office_time
from cte
WHERE activity_type='login'
GROUP by 1,2

[@iamanuj8942]

good one

[@RaviKanth-fx1pt]

Thank you very much!

[@ajithshetty1684]

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

[@arpanscreations6954]

My Solution without using any window function or cte:
select
employee_id
,cast(activity_time as date) as working_day
, datediff(hour, min(activity_time), max(activity_time) ) as total_hours
,SUM(
case
when activity_type = 'login' then -DATEDIFF(hour, '1970-01-01 00:00:00', activity_time)
else DATEDIFF(hour, '1970-01-01 00:00:00', activity_time) end
) as working_hours
from swipe
group by employee_id,cast(activity_time as date)

[@varunanr5433]

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.

[@theraizadatalks14]

Using FIRST_VALUE & LAG in SQLSERVER:
go
with cte as (
Select *,
first_value(activity_time) over(Partition by employee_id, Convert(date,activity_time) order by activity_time ) as login_time,
first_value(activity_time) over(Partition by employee_id, Convert(date,activity_time) order by activity_time desc) as logout_time,
case when activity_type='logout' then lag(activity_time,1,NULL) over(Partition by employee_id, Convert(date,activity_time) order by
activity_time ) else NULL end as prev_login
from swipe
)
Select Employee_id,Convert(date,activity_time) as activity_Date,
MAX(Datediff(hour, login_time, logout_time)) as total_hr,
SUM(Datediff(hour, prev_login, activity_time)) as actual_hr
from cte

[@SHUBHAM_707]

#** Simplest Method **#
select employee_id, FORMAT(activity_time, 'yyyy/MM/dd') Date,
DATEDIFF(HOUR, min(activity_time), max(logout_time)) total_hrs,
sum(DATEDIFF(HOUR, activity_time, logout_time)) Effective_Hrs
from (select *, lead(activity_time,1) over(Partition By employee_id order by activity_time) logout_time from swipe) a
where activity_type = 'login'
group by employee_id, FORMAT(activity_time, 'yyyy/MM/dd')

[@imranpathan8352]

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

[@shekhar1523]

instead of * you can chose column names its reduce load on server

[@piyushbamboriya1288]

Create index on sales column

[@LearnSQL2024]

@@piyushbamboriya1288 partial?? means on sales>50

[@coding6763]

New course when ?

[@throughmyglasses9241]

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

[@mathman298]

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

[@goundlarajugoud3756]

THE ABOVE PROBLEM SOLVE IN ORACLE SQL

[@addhyasumitra90]

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;

[@nd9267]

;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

[@vinil9212]

I think the question has been repeated

[@vaibhavverma1340]

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

[@sakshinaik05]

Given a list of pizza toppings, consider all the possible 3-topping pizzas, and print out the total cost of those 3 toppings. Sort the results with the highest total cost on the top followed by pizza toppings in ascending order.
Break ties by listing the ingredients in alphabetical order, starting from the first ingredient, followed by the second and third.
P.S. Be careful with the spacing (or lack of) between each ingredient. Refer to our Example Output.
Notes:
Do not display pizzas where a topping is repeated. For example, ‘Pepperoni,Pepperoni,Onion Pizza’.
Ingredients must be listed in alphabetical order. For example, 'Chicken,Onions,Sausage'. 'Onion,Sausage,Chicken' is not acceptable.
pizza_toppings Table:
Column Name Type
topping_name varchar(255)
ingredient_cost decimal(10,2)
pizza_toppings Example Input:
topping_name ingredient_cost
Pepperoni 0.50
Sausage 0.70
Chicken 0.55
Extra Cheese 0.40
Example Output:
pizza total_cost
Chicken,Pepperoni,Sausage 1.75
Chicken,Extra Cheese,Sausage 1.65
Extra Cheese,Pepperoni,Sausage 1.60
Chicken,Extra Cheese,Pepperoni 1.45
Explanation
There are four different combinations of the three toppings. Cost of the pizza with toppings Chicken, Pepperoni and Sausage is $0.55 + $0.50 + $0.70 = $1.75.
Additionally, they are arranged alphabetically; in the dictionary, the chicken comes before pepperoni and pepperoni comes before sausage.
Below are the Create And Insert Table Scripts
CREATE TABLE PizzaToppings (
topping_name VARCHAR(50) NOT NULL,
ingredient_cost DECIMAL(5,2) NOT NULL
);
-- Insert statements for pizza toppings data
INSERT INTO PizzaToppings (topping_name, ingredient_cost) VALUES
('Pepperoni', 0.50),
('Sausage', 0.70),
('Chicken', 0.55),
('Extra Cheese', 0.40),
('Mushrooms', 0.25),
('Green Peppers', 0.20),
('Onions', 0.15),
('Pineapple', 0.25),
('Spinach', 0.30),
('Jalapenos', 0.20);

[@rohitsharma-mg7hd]

wanted to solve it using joins,
did it with join :
with cte as (
select *,row_number() over (partition by employee_id,activity_type,activity_time::date order by employee_id,activity_type,activity_time ) from swipe
order by employee_id,activity_time,row_number),
cte2 as
(select c1.activity_time::date,c1.employee_id,sum(c1.activity_time-c2.activity_time) as productive_time from cte c1 inner join cte c2 on
c1.employee_id=c2.employee_id and c1.row_number=c2.row_number
and c1.activity_type!=c2.activity_type and c1.activity_time::date=c2.activity_time::date
and c1.activity_time>c2.activity_time
group by c1.activity_time::date,c1.employee_id
order by c1.employee_id,c1.activity_time::date),
cte3 as
(select employee_id,activity_time::date,max(case when activity_type='logout' then activity_time end)-min(case when activity_type='login' then activity_time end) as total_time
from swipe
group by employee_id,activity_time::date
order by employee_id,activity_time::date)
select c1.employee_id,c1.activity_time,c1.productive_time,c2.total_time
from cte2 c1 inner join cte3 c2
on c1.employee_id=c2.employee_id and c1.activity_time=c2.activity_time

[@ankitbansal6]

Good Attempt but can be simplified.

[@rohitsharma-mg7hd]

@@ankitbansal6 sure , i got your point. yes number of cte can be reduced in this

[@likinponnanna8990]

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

[@pradeepvadlakonda007]

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;

[@prasadjawale2675]

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

[@sivasrimakurthi206]

as always, Ankit Bansal is master in solving these queries and explain in clear method.....🦸

[@kishorereddy3155]

your mail id..?

[@ankitbansal6]

[e-mail]