INNER JOIN (
              SELECT node_id,
                     SUM(r * weight) / (
                       SQRT(SUM(COALESCE(r, 1) * weight)^2 +
                         SUM(COALESCE(g, 1) * weight)^2 +
                           SUM(COALESCE(b, 1) * weight)^2)
                     ) AS r,
                     SUM(g * weight) / (
                       SQRT(SUM(COALESCE(r, 1) * weight)^2 +
                         SUM(COALESCE(g, 1) * weight)^2 +
                           SUM(COALESCE(b, 1) * weight)^2)
                     ) AS g,
                     SUM(b * weight) / (
                       SQRT(SUM(COALESCE(r, 1) * weight)^2 +
                         SUM(COALESCE(g, 1) * weight)^2 +
                           SUM(COALESCE(b, 1) * weight)^2)
                     ) AS b
                FROM node_populations
                  INNER JOIN populations ON node_populations.population_id = populations.id
                  LEFT OUTER JOIN (
                    VALUES
                      ('OG Kush', 0.258, 0.431, 0.69),
                      ('Landrace', 0.25, 0.549, 0.384),
                      ('Skunk', 0.839, 0.329, 0.329),
                      ('Berry', 0.624, 0.49, 0.627),
                      ('CBD', 0.929, 0.631, 0.459),
                      ('Hemp', 0.518, 0.529, 0.431)
                  ) AS colors(popname, r, g, b) ON colors.popname = populations.name
                WHERE populations.constellation_id = :constellation_id
                GROUP BY node_id
            )AS pops ON pops.node_id = nodes.id