CTEs (Common Table Expressions)

Long complex SQL queries aren’t always the most readable thing. CTEs (as a gross over simplification) are somewhat akin to a view, but only during that specific query. In effect you can build up multiple different CTEs that reference earlier ones you created, thus making more composable and readable SQL. You can also recursively call them, allowing you to do things that you would otherwise need procedural language for.

The broad structure of a CTE is:

WITH your_cte_name AS (
    SELECT foo, bar
    FROM your_table
)

You can chain them together in a basic form such as:

WITH your_cte_name AS (
    SELECT foo, bar
    FROM your_table
),
cte_number_2_name AS (
    SELECT foo
    FROM your_cte_name
)

You can find a more fully formed below, or read further over at craigkerstiens.com:

WITH user_task AS 
(
    SELECT 
        u.id AS user_id,
        p.id AS project_id,
        u.email,
        array_agg(t.name) AS task_list,
        p.title
    FROM
        project p,
        task t,
        user_ u
    WHERE
            u.id = t.user_id
        AND p.id = t.project_id
    GROUP BY
        u.id,
        p.id
),

--- Calculates the total task per each project
total_task_per_project AS 
(
    SELECT 
        t.project_id,
        count(*) as task_count
    FROM 
        task t
    GROUP BY 
        t.project_id
),

--- Calculates the projects per each user
task_per_project_per_user AS 
(
    SELECT 
        t.user_id,
        t.project_id,
        count(*) as task_count
    FROM 
        task t
    GROUP BY 
        t.user_id, 
        t.project_id
),

--- Gets user ids that have over 50% of task assigned
overloaded_user AS 
(
    SELECT 
        tpu.user_id,
        tpu.project_id
    FROM 
        task_per_project_per_user tpu,
        total_task_per_project pp
    WHERE
            tpu.project_id = pp.project_id
        AND tpu.task_count > (pp.task_count / 2)
)

SELECT 
    ut.email,
    ut.task_list,
    ut.title
FROM 
     user_task ut,
     overloaded_user ou
WHERE
         ut.user_id = ou.user_id
     AND ut.project_id = ou.project_id
;