SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time

SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time 🔥🔥

Google wants to know how many days of bench time each consultant had in 2021. Being "on the bench" means you have a gap between two client engagements. Assume that each consultant is only staffed to one consulting engagement at a time. Write a query to pull each employee ID and their total bench time in days during 2021.
Assumptions:
All listed employees are current employees who were hired before 2021. The engagements in the consulting_engagements table are complete for the year 2021.


-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
🔴 Instagram :

🔴 Twitter:
-------------------------------------------------------------------------
🔴 Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table Staffing (
employee_id int,
is_consultant bit,
job_id int)

Insert into Staffing values(111,1,7898)
Insert into Staffing values(121,0,6789)
Insert into Staffing values(111,1,9020)
Insert into Staffing values(156,1,4455)
Insert into Staffing values(111,1,8885)

Create Table Consulting_engagements(
job_id int,
client_id int,
start_dates date,
end_dates date,
contract_amount int
)

Insert into Consulting_engagements values (6789,20045,'06/01/2021 00:00:00','11/12/2021 00:00:00',33040.00)
Insert into Consulting_engagements values (8885,20022,'07/05/2021 00:00:00','07/31/2021 00:00:00',4670.00)
Insert into Consulting_engagements values (9020,20345,'08/14/2021 00:00:00','10/31/2021 00:00:00',22370.00)
Insert into Consulting_engagements values (4455,20001,'01/25/2021 00:00:00','05/31/2021 00:00:00',31839.00)
Insert into Consulting_engagements values (7898,20076,'05/25/2021 00:00:00','06/30/2021 00:00:00',11290.00)
Insert into Consulting_engagements values (3462,20099,'09/15/2021 00:00:00','11/15/2021 00:00:00',240000.00)
Insert into Consulting_engagements values (2354,20001,'10/14/2021 00:00:00','12/31/2021 00:00:00',54000.00)

#GoogleDataEngineer #GoogleInterview #GoogleSQLInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #ITJunction4all

[dileep]

Hi Bro, These were questions asked in Amazon for business analyst ..please make some video on these ...hope this would be helpful for freshers and 1-2 years experience..
1.Table College marks - student, subject, dept, marks
write a query which gives dept, sub, Average marks per subject, avg marks per dept

2.Call center data - call id, call in time, agent id, customer id, Feedback Rating (1-10) has NULLs also in this column
* Write a query which gives agent_id, month, percentages of positive, negative, nuetral feedbacks. Exclude NULL records

[faizraina]

bro datediff always throws error . this is data lemur question and i tried to solve the quetion using your approach through datediff but its showing "column "day" does not exist." kindly reply

[bishwarup]

with cte as(
select c.*, s.employee_id, s.is_consultant from Consulting_engagements c
left join staffing s on c.job_id = s.job_id
where is_consultant = 1),
cte2 as(
select *, datediff(end_dates, start_dates)+1 as days_in_proj from cte
order by employee_id, job_id)
select employee_id, 365-sum(days_in_proj)
from cte2
group by 1;

[schymi]

Alternative solution without CTE or sub-query. Just simple join and group by:
select
s.employee_id
, bench_days = 365 - SUM(DATEDIFF(DAY, c.start_dates, c.end_dates)+1)
from Staffing s
join Consulting_engagements c on s.job_id = c.job_id
where s.is_consultant = 1
group by employee_id

[shivarajhalageri]

Join employee table and engagement table, , , on job id and gp by employee id and order by end date then apply lead function on this as Ld then subdtract Ld and end date as diff and then add the difference that gives the answer.