Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Home
Talentd Logo
Talentd

Your trusted platform to ace any job interviews, craft the perfect resumes, and land your dream jobs.

P
Featured on
Product Hunt
▲455
All services are online

Products

  • Resume Review
  • Company Prep Pack
  • DSA Corner
  • Jobs
  • Internships
  • Fresher Jobs
  • Roadmaps
  • Tax Calculator

Resources

  • Articles
  • DRDO Internships

Support

  • Contact Us

DSA & Interview Prep

  • DSA Questions
  • DSA Sheets
  • Company Questions
  • Topics

Company

  • Companies Hiring
  • About
  • Contact
  • Advertisement

Legal

  • Privacy Policy
  • Terms & Conditions
  • Refund Policy
  • Delivery Policy

Popular Skills

Browse All Skills →

Popular Tags

Browse All Tags →

© 2025 Talentd.in - All rights reserved

Privacy PolicyTerms & Conditions
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
DSA Corner
DashboardQuestionsTopicsCompaniesSheets

Talentd Logo
Talentd

Your trusted platform to ace any job interviews, craft the perfect resumes, and land your dream jobs.

P
Featured on
Product Hunt
▲455
All services are online

Products

  • Resume Review
  • Company Prep Pack
  • DSA Corner
  • Jobs
  • Internships
  • Fresher Jobs
  • Roadmaps
  • Tax Calculator

Resources

  • Articles
  • DRDO Internships

Support

  • Contact Us

DSA & Interview Prep

  • DSA Questions
  • DSA Sheets
  • Company Questions
  • Topics

Company

  • Companies Hiring
  • About
  • Contact
  • Advertisement

Legal

  • Privacy Policy
  • Terms & Conditions
  • Refund Policy
  • Delivery Policy

Popular Skills

Browse All Skills →

Popular Tags

Browse All Tags →

© 2025 Talentd.in - All rights reserved

Privacy PolicyTerms & Conditions
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
DSA Corner
DashboardQuestionsTopicsCompaniesSheets
Talentd Logo
Talentd

Your trusted platform to ace any job interviews, craft the perfect resumes, and land your dream jobs.

P
Featured on
Product Hunt
▲455
All services are online

Products

  • Resume Review
  • Company Prep Pack
  • DSA Corner
  • Jobs
  • Internships
  • Fresher Jobs
  • Roadmaps
  • Tax Calculator

Resources

  • Articles
  • DRDO Internships

Support

  • Contact Us

DSA & Interview Prep

  • DSA Questions
  • DSA Sheets
  • Company Questions
  • Topics

Company

  • Companies Hiring
  • About
  • Contact
  • Advertisement

Legal

  • Privacy Policy
  • Terms & Conditions
  • Refund Policy
  • Delivery Policy

Popular Skills

Browse All Skills →

Popular Tags

Browse All Tags →

© 2025 Talentd.in - All rights reserved

Privacy PolicyTerms & Conditions
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
DSA Corner
DashboardQuestionsTopicsCompaniesSheets
Talentd Logo
Talentd

Your trusted platform to ace any job interviews, craft the perfect resumes, and land your dream jobs.

P
Featured on
Product Hunt
▲455
All services are online

Products

  • Resume Review
  • Company Prep Pack
  • DSA Corner
  • Jobs
  • Internships
  • Fresher Jobs
  • Roadmaps
  • Tax Calculator

Resources

  • Articles
  • DRDO Internships

Support

  • Contact Us

DSA & Interview Prep

  • DSA Questions
  • DSA Sheets
  • Company Questions
  • Topics

Company

  • Companies Hiring
  • About
  • Contact
  • Advertisement

Legal

  • Privacy Policy
  • Terms & Conditions
  • Refund Policy
  • Delivery Policy

Popular Skills

Browse All Skills →

Popular Tags

Browse All Tags →

© 2025 Talentd.in - All rights reserved

Privacy PolicyTerms & Conditions
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
Jobs
Learning
Career Tools
Talentd Logo
Talentd#1 Freshers Platform
DSA Corner
DashboardQuestionsTopicsCompaniesSheets
Back to Problems

1075. Project Employees I

Easy64.2% Acceptance
Database
Asked by:
A
Amazon
F
Facebook
ProblemSolutions (4)VideosCompanies (2)Notes

Problem Statement

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
Each row of this table contains information about one employee.

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Project table:
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+
Output: 
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.50          |
+-------------+---------------+
Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50
Talentd Logo
Talentd

Your trusted platform to ace any job interviews, craft the perfect resumes, and land your dream jobs.

P
Featured on
Product Hunt
▲455
All services are online

Products

  • Resume Review
  • Company Prep Pack
  • DSA Corner
  • Jobs
  • Internships
  • Fresher Jobs
  • Roadmaps
  • Tax Calculator

Resources

  • Articles
  • DRDO Internships

Support

  • Contact Us

DSA & Interview Prep

  • DSA Questions
  • DSA Sheets
  • Company Questions
  • Topics

Company

  • Companies Hiring
  • About
  • Contact
  • Advertisement

Legal

  • Privacy Policy
  • Terms & Conditions
  • Refund Policy
  • Delivery Policy

Popular Skills

Browse All Skills →

Popular Tags

Browse All Tags →

© 2025 Talentd.in - All rights reserved

Privacy PolicyTerms & Conditions

Approach

In #1075 Project Employees I, the goal is to compute the average experience years of employees assigned to each project. The information is split across two tables: one mapping employees to projects and another containing employee details. To solve this, the key idea is to combine data from both tables and then calculate an aggregate value.

Start by using a JOIN operation to connect the project assignments with the employee records based on the employee ID. Once the relevant data is merged, group the results by project_id. This allows you to apply the AVG() aggregate function on the employees' experience years for each project. Many solutions also round the result to two decimal places for consistent formatting.

This approach leverages relational database operations efficiently, using grouping and aggregation to summarize employee experience across projects. The database engine handles the heavy lifting during the grouping stage.

Complexity

ApproachTime ComplexitySpace Complexity
JOIN with GROUP BY and AVG aggregationO(n log n)O(n)

Video Solution Available

Learn With Chirag

View all video solutions

Solutions (4)

Using SQL JOIN and Aggregation

This approach involves joining the Project and Employee tables based on employee_id and then grouping by project_id to calculate the average experience years for employees in each project.

Time Complexity: O(n), where n is the number of rows in the joined table. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

1SELECT Project.project_id, ROUND(AVG(Employee.experience_years), 2) AS average_years FROM Project INNER JOIN Employee ON Project.employee_id = Employee.employee_id GROUP BY Project.project_id;

Explanation

In this solution, we use an INNER JOIN to combine the Project table with the Employee table on employee_id. This joins each employee to their respective projects. Next, we group the resulting records by project_id and calculate the average of experience_years for each group. The ROUND function is used to round the result to two decimal places.

Subquery with Aggregation

In this approach, we find the average experience years using a subquery to aggregate data from the Employee table after filtering it by matching project_id from the Project table.

Time Complexity: O(n * m), where n is the number of projects and m is the number of employees per project on average. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

1SELECT P.project_id, (SELECT ROUND(AVG(E.experience_years), 2) 

SQL JOIN with Average Function

This approach leverages SQL JOIN to combine the Project and Employee tables, followed by the use of AVG function to calculate the average experience years for each project, and finally rounding the result to 2 decimal places using the ROUND function.

Time Complexity: O(N), where N is the total number of rows in the joined table.
Space Complexity: O(M), where M is the number of unique projects.

1SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p 

Subquery for Aggregation

This approach utilizes a subquery to first collect the employee experience for each project and then calculates the average in the outer query.

Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.

1SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM (SELECT p.project_id,

Video Solutions

Watch expert explanations and walkthroughs

Project Employees I | Leetcode 1075 | Crack SQL Interviews in 50 Qs #mysql #leetcode

Learn With Chirag
6:125,454 views

Asked By Companies

2 companies
A
Amazon
F
Facebook

Prepare for Interviews

Practice problems asked by these companies to ace your technical interviews.

Explore More Problems

Notes

Personal Notes

Jot down your thoughts, approach, and key learnings

0 characters

Similar Problems

Combine Two TablesEasy
Second Highest SalaryMedium
Nth Highest SalaryMedium
Rank ScoresMedium
More similar problems

Related Topics

Database

Problem Stats

Acceptance Rate64.2%
DifficultyEasy
Companies2

Practice on LeetCode

Solve with full IDE support and test cases

Solve Now

Frequently Asked Questions

What SQL concepts are used in Project Employees I?

This problem mainly uses SQL JOIN operations and aggregate functions like AVG(). It also requires GROUP BY to calculate the average experience for each project separately.

What is the optimal approach for Project Employees I?

The optimal approach is to join the Project and Employee tables using employee_id, then group the results by project_id. After grouping, apply the AVG() aggregate function to compute the average experience years for employees in each project.

Is Project Employees I asked in FAANG-style interviews?

Yes, similar SQL aggregation and join problems frequently appear in technical interviews, especially for data-related roles. They test understanding of relational databases, grouping, and aggregate queries.

What data structure or database operation is key for Project Employees I?

The key operation is a relational JOIN between two tables combined with GROUP BY aggregation. These operations allow the database to combine related rows and compute summary statistics efficiently.

FROM
Employee E
WHERE
E
.
employee_id
IN
(
SELECT
employee_id
FROM
Project
WHERE
project_id
=
P
.
project_id
)
)
AS
average_years
FROM
Project P
GROUP
BY
P
.
project_id
;

Explanation

This solution first selects the distinct project_id from the Project table. For each project, a subquery is run which selects experience_years from the Employee table but filtered by employees that are part of the current project. The average experience of these employees is calculated by the subquery and rounded to two decimal places.

JOIN
Employee e
ON
p
.
employee_id
=
e
.
employee_id
GROUP
BY
p
.
project_id
;

Explanation

In this solution, we use a JOIN operation to link the Project and Employee tables through employee_id. We then utilize the AVG function on the experience_years column to compute the average for each project_id. The GROUP BY clause ensures aggregation by project, and the ROUND function is used to format the average to two decimal places.

e
.
experience_years
FROM
Project p
JOIN
Employee e
ON
p
.
employee_id
=
e
.
employee_id
)
subquery
GROUP
BY
project_id
;

Explanation

The inner subquery first performs a JOIN to collect employee experience associated with each project. The outer query then aggregates this data using the GROUP BY clause on project_id and computes the average experience using AVG, rounded to two decimal places with ROUND.