clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40515 in the last week).

CREATE TABLE bought_products (id int, name text, customer_id int, product_id int, foo int, bar int, blarg int); INSERT INTO bought_products (id, name , customer_id, product_id, foo, bar, blarg) VALUES (1, 'test', 123, 321, NULL, '1' , NULL), (2, 'tast', 123, 231, '5' , NULL, '20'), (3, 'tost', 456, 321, NULL, '3' , NULL); CREATE TABLE Price (id int, item text, product_id int, cost numeric); INSERT INTO Price VALUES (1, 'Foo', 321, 10), (2, 'Bar', 321, 5), (3, 'BASE', 321, 100), (4, 'BASE', 231, 50), (5, 'Blarg', 231, 0.5), (6, 'Foo', 231, 1);
3 rows affected
6 rows affected
 hidden batch(es)


SELECT customer_id AS customer , string_agg(DISTINCT product_id::text, ', ') AS products , sum(cost * CASE p.item WHEN 'BASE' THEN 1 WHEN 'Foo' THEN b.foo WHEN 'Bar' THEN b.bar WHEN 'Blarg' THEN b.blarg END) AS revenue FROM bought_products b LEFT JOIN price p USING (product_id) -- WHERE p.item = ANY ('{BASE,Foo,Bar,Blarg}') -- optional GROUP BY 1;
customer products revenue
123 231, 321 170.0
456 321 115
 hidden batch(es)