$10$ Meesho Business Analyst

$2.$ Idencify all possible bugs in below Scenario based sql code $-$ Part $2$

You have three tables: projects, tasks, and employees. Your task is to write an SQL query that:

Calculates the total number of tasks for each project and the average number of tasks per project.

Lists each project's name, the name of the project manager, and the total number of tasks.

For each employee assigned to a project, shows:

Their name and the number of tasks they are responsible for.

Their rank within the project based on the number of tasks $($most tasks ranks first$).$

The number of tasks of the employee just above and just below them in the ranking.

Even if a project has no tasks, it should still be included in the result with NULL for the average number of tasks.

WITH $tas{k}_c$ounts $AS$

SELECT

$t.projec{t}_{i}d,$

$t.employe{e}_{i}d,$

COUNT $($t$.$task$\_$id$)$ AS task$\_$count,

ROW$\_$NUMBER$()$ OVER $($PARTITION BY t$.$project$\_$id ORDER BY COUNT $($t$.$task$\_$id$)$ DESC$)$ AS rank, LEAD$($COUNT $($t$.$task$\_$id$),1)$ OVER $($PARTITION BY t$.$project$\_$id ORDER BY COUNT $($t$.$task$\_$id$)$ ASC$)$ AS Lead$\_$taSkS,