Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH product_group AS (

SELECT 1 AS id, 'Nice' AS group_name UNION ALL

SELECT 2, 'Very Nice'

product AS (

SELECT 1 AS prod_id, 'something' AS name, 2 AS group_id UNION ALL

SELECT 2, 'psp3', 1 UNION ALL

SELECT 3, 'bundle1', 2 UNION ALL

SELECT 4, 'bundle2', 1

version AS (

SELECT 1 AS id, 2 AS prod_id, 1.0 AS version UNION ALL

SELECT 2, 2, 1.1 UNION ALL

SELECT 3, 3, 2.3 UNION ALL

SELECT 4, 1, 0.1 UNION ALL

SELECT 5, 4, 0.4 UNION ALL

SELECT 6, 1, 0.2

product_group_child as (

SELECT 1 AS pgt_child_id, 2 AS group_id, 'root2' as default_something UNION ALL

SELECT 2, 1, 'root1'

product_child as (

SELECT 1 AS pt_child_id, 2 AS prod_id, 'override2' as default_something

version_child as (

SELECT 1 AS vt_child_id, 3 AS version_id, 'winner' as default_something

pivot as (

SELECT 1 AS p_id, 3 AS version_id, 2 as prod_id UNION ALL

SELECT 2, 3, 1 UNION ALL

SELECT 3, 5, 1

)

SELECT DISTINCT ON (pg.id, p.prod_id)

group_name	prod_name	version	something
Nice	psp3	1.1	override2
Nice	bundle2	0.4	root1
Very Nice	something	0.2	root2
Very Nice	bundle1	2.3	winner