Round 1: Technical Coding and Project related

Table 1: Employee
employee_id
employee_name
salary
level (1-3)
department_id

Table 2: Project
project_id
project_name
status (IN_PROGRESS, COMPLETED)
start_date (yyyy-mm-dd)
end_date (yyyy-mm-dd)

Table 3: Employee_Project
employee_id
project_id

1.Count of all active projects
2.Average number of days to complete a project
3.List of all employee_ids who are not a part of any active project
4.For all projects, list of employees who have the maximum salary within the project, along with the maximum salary

SELECT COUNT(*) AS ActiveProjects_count
FROM
Project
WHERE Status= 'IN_PROGRESS';

SELECT AVG(DATEDIFF(end_date, start_date))
avg_days_to_complete
FROM
PROJECT WHERE status='completed';

SELECT e.employee_id
FROM Employee e
WHERE e.employee_id NOT IN (
SELECT DISTINCT ep.employee_id
FROM Employee_Project
JOIN Project ON ep.project_id = p.project_id
WHERE p.status= 'IN_PROGRESS'
);

SELECT ep.project_id,
ep.employee_id,
ep.employee_name,
e.salary
FROM Employee_Project ep
JOIN Employee e ON ep.employee_id = e.employee_id
WHERE (ep.project_id, e.salary) IN (
SELECT
ep2.project_id,
MAX(e2.salary)
FROM Employee_Project ep2
JOIN Employee e2 ON ep2.employee_id =e2.employee_id
GROUP BY ep2.project_id
);

Python

def longest_common_prefix(strs):

    prefix = strs[0]
    
    for s in strs[1:]:
        while not  s.startswith(prefix):
            prefix = prefix[:-1]
            if not prefix:
                return ""
                
    return prefix
    
    

print(longest_common_prefix (['abcd', 'abc', 'abcdef']))

Round 2:

More into Project related and Spark and Hadoop focused as it more works related to Target Internal Hadoop Platform. Migration related Questions. Real time Scenarios, Data handling, Data Transformations