𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: 
You have 2 minutes to solve this SQL query. 
Find the employee(s) who have worked on the highest number of distinct projects. 

Assume the table structure: employee_projects(employee_id, project_id)

𝗠𝗲: Challenge accepted! 💪

SELECT
    employee_id,
    total_projects
FROM (
    SELECT
        employee_id,
        COUNT(DISTINCT project_id) AS total_projects,
        DENSE_RANK() OVER (
            ORDER BY COUNT(DISTINCT project_id) DESC
        ) AS rnk
    FROM employee_projects
    GROUP BY employee_id
) ranked
WHERE rnk = 1;

💡 Explanation: 
This query counts the number of unique projects each employee has worked on and identifies those with the highest count.

• COUNT(DISTINCT project_id) counts unique projects for each employee
• GROUP BY employee_id creates one record per employee
• DENSE_RANK() ranks employees based on the number of projects
• The outer query returns all employees tied for the highest number of projects

This question tests your understanding of: 
✅ COUNT(DISTINCT) 
✅ GROUP BY 
✅ Window Functions DENSE_RANK 
✅ Ranking Aggregated Results 

🎯 Expected Output Example 
Employee ID | Total Projects 
101 | 12 
205 | 12 

Both employees have worked on the highest number of distinct projects.

🚀 Alternative Without Window Functions

SELECT
    employee_id,
    COUNT(DISTINCT project_id) AS total_projects
FROM employee_projects
GROUP BY employee_id
HAVING COUNT(DISTINCT project_id) = (
    SELECT MAX(project_count)
    FROM (
        SELECT
            COUNT(DISTINCT project_id) AS project_count
        FROM employee_projects
        GROUP BY employee_id
    ) t
);

This solution uses nested subqueries and MAX() instead of window functions.

🚀 Tip for SQL Job Seekers: 
Many interview questions involve ranking aggregated results, such as: 
Highest number of projects, Most orders, Maximum sales, Highest attendance, Most logins 

Practice combining GROUP BY with window functions like DENSE_RANK() to solve these efficiently.

❤️ React with ❤️ for more interview challenges!