CTE_learn.sql

/* Inferred Table */
CREATE TABLE users (
	id    serial PRIMARY KEY,
	email text
);

CREATE TABLE tasks (
	id         serial PRIMARY KEY,
	name  	   text,
	project_id NUMERIC,
	user_id    NUMERIC
);

CREATE TABLE project (
	id    serial PRIMARY KEY,
	title text,
);

/* Formatted Original Code */
WITH users_tasks AS ( 
	SELECT
		users.id AS user_id, 
		users.email, 
		ARRAY_AGG( tasks.name ) AS task_list, 
		projects.title
	FROM
		users, tasks, project
	WHERE
		users.id = tasks.user_id AND projects.title = tasks.project_id
	GROUP BY
		users.email, projects.title 
),
--- Calculates the total tasks per each project
total_tasks_per_project AS ( 
	SELECT
		project_id, 
		COUNT(*) AS task_count
	FROM
		tasks
	GROUP BY
		project_id 
),
--- Calculates the projects per each user
tasks_per_project_per_user AS ( 
	SELECT
		user_id, 
		project_id, 
		COUNT(*) AS task_count
	FROM
		tasks
	GROUP BY
		user_id, project_id 
),
--- Gets user ids that have over 50% of tasks assigned
overloaded_users AS ( 
	SELECT
		tasks_per_project_per_user.user_id,
	FROM
		tasks_per_project_per_user, total_tasks_per_project
	WHERE
		tasks_per_project_per_user.task_count > ( total_tasks_per_project / 2 ) --??
) 
SELECT
	email,
	task_list,
	title
FROM
	users_tasks,
	overloaded_users
WHERE
	users_tasks.user_id = overloaded_users.user_id
);


/* bugfix above */

--- Created by Craig Kerstiens 11/18/2013
--- Query highlights users that have over 50% of tasks on a given project
--- Gives comma separated list of their tasks and the project

--task list and project title per user
WITH users_tasks AS (
    SELECT 
       users.id as user_id,
       users.email,
       array_agg(tasks.name) as task_list,
       projects.title
    FROM
       users,
       tasks,
       project
    WHERE
        users.id = tasks.user_id
        projects.title = tasks.project_id
    GROUP BY
        users.email,
        projects.title
),
--total tasks per project
total_tasks_per_project AS (
    SELECT 
         project_id,
         count(*) as task_count
    FROM tasks
    GROUP BY project_id
),
--projects per user
tasks_per_project_per_user AS (
    SELECT 
         user_id,
         project_id,
         count(*) as task_count
    FROM tasks
    GROUP BY user_id, project_id
),
--- Gets user ids that have over 50% of tasks assigned
overloaded_users AS (
	SELECT 
  		tasks_per_project_per_user.user_id
  	FROM tasks_per_project_per_user,			--Q: here, do they join be project_id implicit ??
         total_tasks_per_project
    WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project.task_count / 2)
)
SELECT 
   users_tasks.email,
   users_tasks.task_list,
   users_tasks.title
FROM 
   users_tasks,
   overloaded_users
WHERE
   users_tasks.user_id = overloaded_users.user_id
;



/* Ingredients 
total_tasks_per_project AS (
  	SELECT 
         project_id,
         count(*) as task_count
	FROM tasks
	GROUP BY project_id
),
tasks_per_project_per_user AS (
  	SELECT 
         user_id,
         project_id,
         count(*) as task_count
  	FROM tasks
	GROUP BY 
  		user_id, project_id
),
overloaded_users AS (
	SELECT tasks_per_project_per_user.user_id,
    FROM tasks_per_project_per_user,
         total_tasks_per_project
    WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
)
*/



--http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/

WITH users_tasks AS ( 
	SELECT
		users.email, 
		ARRAY_AGG( tasks.name ) AS task_list, 
		projects.title
	FROM
		users,
		--same thing as explicit join
	    tasks, 
	    project
	WHERE
		users.id = tasks.user_id
		--bind user to task
	 	projects.title = tasks.project_id
		--bind project to task
	GROUP BY
		users.email, 
		projects.title 
)
SELECT * FROM users_tasks;