Postgres 14

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE currency (
  iso_number CHARACTER VARYING(3) PRIMARY KEY,
  iso_code CHARACTER VARYING(3)
);

INSERT INTO currency(iso_number, iso_code) VALUES 
('208','DKK'), 
('752','SEK'), 
('572','NOK');

CREATE TABLE product (
  id SERIAL PRIMARY KEY,
  name CHARACTER VARYING(12),
  current_price INTEGER
);

INSERT INTO product(name,current_price) VALUES 
('icecream',200), 
('sunglasses',300);

CREATE TABLE sale (
  id SERIAL PRIMARY KEY,
  time_of_sale TIMESTAMP,
  currency_items_sold_in CHARACTER VARYING(3)
);

INSERT INTO sale(time_of_sale, currency_items_sold_in) 
VALUES 
(CURRENT_TIMESTAMP, '208'),
(CURRENT_TIMESTAMP, '752');

CREATE TABLE sale_lines (
  id SERIAL PRIMARY KEY,
  sale_id INTEGER,
  product_id INTEGER,
  price_paid INTEGER,
  quantity FLOAT
);

INSERT INTO sale_lines(sale_id, product_id, price_paid, quantity)
VALUES 
(1, 1, 200, 1.0),
(1, 2, 300, 1.0),
(2, 1, 100, 1.0),
(2, 1, 100, 1.0);

CREATE TABLE cash_transactions (
  id SERIAL PRIMARY KEY,
  sale_id INTEGER,
  received_currency_id CHARACTER VARYING(3),
  converted_currency_id CHARACTER VARYING(3),
  received_amount INTEGER,
  converted_amount INTEGER
);

INSERT INTO cash_transactions(sale_id, received_currency_id, converted_currency_id, received_amount, converted_amount)
VALUES
(1, '208', '208', 200, 200),
(1, '752', '208', 400, 300),
(2, '572', '208', 150, 100),
(2, '208', '208', 100, 100);

3 rows affected

2 rows affected

2 rows affected

4 rows affected

4 rows affected

WITH CTE_SALE AS (
  SELECT
   id as sale_id, 
   currency_items_sold_in AS iso_number
  FROM sale
)
SELECT curr.iso_code AS currency
, COALESCE(line.price_paid, 0)  as total_price_paid
, COALESCE(received.amount, 0)  as total_received_amount
, COALESCE(converted.amount, 0) as total_converted_amount
FROM currency AS curr
LEFT JOIN (
  SELECT s.iso_number
  , SUM(sl.price_paid) AS price_paid
  FROM sale_lines sl
  JOIN CTE_SALE s ON s.sale_id = sl.sale_id
  GROUP BY s.iso_number
) AS line 
  ON line.iso_number = curr.iso_number
LEFT JOIN (
  SELECT tr.received_currency_id as iso_number
  , SUM(tr.received_amount) AS amount
  FROM cash_transactions tr
  JOIN CTE_SALE s ON s.sale_id = tr.sale_id
  GROUP BY tr.received_currency_id
) AS received
  ON received.iso_number = curr.iso_number
LEFT JOIN (
  SELECT tr.converted_currency_id as iso_number
  , SUM(tr.converted_amount) AS amount
  FROM cash_transactions AS tr
  JOIN CTE_SALE s ON s.sale_id = tr.sale_id
  GROUP BY tr.converted_currency_id
) AS converted
  ON converted.iso_number = curr.iso_number;

currency	total_price_paid	total_received_amount	total_converted_amount
DKK	500	300	700
SEK	200	400	0
NOK	0	150	0