Postgres 12

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?.
select version();


version


PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE blueberry_order -- a table is a set which is always singular
                             -- except where this would be a keyword i.e. "order"
(
    bo_id SERIAL NOT NULL,  -- should now use GENERATED ALWAYS AS IDENTITY
    bo_timestamp TIMESTAMP NOT NULL DEFAULT NOW(),  -- with time zone messes up the generated field, you could use a trigger?
    weight_g INTEGER CHECK (weight_g > 0),
    price_usd NUMERIC NOT NULL CHECK (price_usd > 0),
    purchase_month INTEGER GENERATED ALWAYS AS (EXTRACT (MONTH FROM bo_timestamp)::INT) STORED,
    -- can't use VIRTUAL
    CONSTRAINT blueberry_orders_pk PRIMARY KEY (bo_id)
);
--
-- purchase time is the same for all orders - it's
-- not relevant to the question at hand!
--
INSERT INTO blueberry_order (bo_timestamp, weight_g, price_usd) VALUES
('2020-06-05 10:15:54', 10, 100),
('2020-06-03 10:15:54', 15,  75),
('2020-05-03 10:15:54',  5,  45),
('2020-05-09 10:15:54',  7,  70),
('2020-05-15 10:15:54', 20, 150),
('2020-05-25 10:15:54',  5,  30),
('2020-04-05 10:15:54', 25, 200),
('2020-04-05 10:15:54', 15, 125),
('2020-04-05 10:15:54',  5,  25), -- discount for previous bulk purchases!
('2020-04-05 10:15:54', 10,  80),
('2020-03-05 10:15:54', 20, 100),
('2020-03-05 10:15:54', 15, 150),
('2020-03-05 10:15:54', 25, 175);

13 rows affected

-- SELECT bo_timestamp, weight_g AS w_g, price_usd AS p$ FROM blueberry_order; -- note appearance of purchase_month data!
-- SELECT TO_CHAR(TO_DATE (purchase_month::text, 'MM'), 'Month') AS m_name
-- FROM blueberry_order;
--
-- price per gramme MARK I - how much you paid per gramme per month
-- if the price was for the ORDER in total!
--
SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::text, 'MM'), 'Month') AS m_name, 
  ROUND(SUM(price_usd::NUMERIC)/(SUM(bo.weight_g::NUMERIC)), 2)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY m_name DESC;


m_name
round


May
7.97

March
7.08

June
7.00

April
7.82


-- 
-- price per gramme MARK II - how much was paid per gramme per month 
-- IF the price was per gramme!
--

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::text, 'MM'), 'Month') AS m_name,
  ROUND(SUM(bo.weight_g * bo.price_usd)::NUMERIC/(SUM(bo.weight_g::NUMERIC)), 2)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY bo.purchase_month DESC;


m_name
round


June
85.00

May
104.46

April
141.82

March
143.75


--
-- total payments - How much was paid per month IF 
-- the price was per gramme!
--

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::text, 'MM'), 'Month') AS m_name,
  SUM(bo.weight_g * bo.price_usd)
FROM blueberry_order bo
GROUP BY bo.purchase_month
ORDER BY bo.purchase_month DESC;


m_name
sum


June
2125

May
3865

April
7800

March
8625


SELECT COUNT((EXTRACT(MONTH FROM bo.bo_timestamp))::INT) 
FROM blueberry_order bo
GROUP BY ((EXTRACT(MONTH FROM bo.bo_timestamp))::INT)


count


3

4

4

2


--
-- Average paid per order per month IF the
-- price is for the order overall

SELECT 
  TO_CHAR(TO_DATE (bo.purchase_month::text, 'MM'), 'Month') AS m_name,
  ROUND(SUM(bo.price_usd)::NUMERIC/
    COUNT((EXTRACT(MONTH FROM bo.bo_timestamp))::INT), 2) AS avg
FROM blueberry_order bo
GROUP BY bo.purchase_month, EXTRACT(MONTH FROM bo.bo_timestamp)
ORDER BY EXTRACT(MONTH FROM bo.bo_timestamp) DESC;


m_name
avg


June
87.50

May
73.75

April
107.50

March
141.67