Raise Prices: How Many Customers Can You Lose?

How it works — every step, readable

1. 01Assumptions — edit these

   SELECT * FROM (VALUES
     ('Current price', 100, 'USD'),
     ('Cost per unit', 60, 'USD'),
     ('Units sold per month', 1000, 'units'),
     ('Price increase', 10, '%')
   ) AS t(assumption, value, unit)

2. 02What the price increase buys you

   buckets values by condition · appends result sets (e.g. a TOTAL row) · sorts the output

   WITH p AS (
     SELECT
       MAX(CASE WHEN assumption = 'Current price' THEN CAST(value AS DOUBLE) END) AS price,
       MAX(CASE WHEN assumption = 'Cost per unit' THEN CAST(value AS DOUBLE) END) AS cost,
       MAX(CASE WHEN assumption = 'Units sold per month' THEN CAST(value AS DOUBLE) END) AS units,
       MAX(CASE WHEN assumption = 'Price increase' THEN CAST(value AS DOUBLE) END) / 100.0 AS inc
     FROM input_1
   ), c AS (
     SELECT *, price * (1 + inc) AS new_price, (price - cost) * units AS profit_now,
       -- the share of customers you can lose before profit falls below today's
       (price * inc) / (price * (1 + inc) - cost) AS max_loss
     FROM p
   )
   SELECT metric, amount, unit FROM (
     SELECT 1 AS o, 'Customers you can lose and still break even' AS metric, ROUND(100 * max_loss, 1) AS amount, '%' AS unit FROM c
     UNION ALL SELECT 2, 'That is, units per month', ROUND(units * max_loss), 'units' FROM c
     UNION ALL SELECT 3, 'New price', ROUND(new_price, 2), 'USD' FROM c
     UNION ALL SELECT 4, 'Monthly profit today', ROUND(profit_now, 2), 'USD' FROM c
     UNION ALL SELECT 5, 'Monthly profit if nobody leaves', ROUND((new_price - cost) * units, 2), 'USD' FROM c
     UNION ALL SELECT 6, 'Extra profit if nobody leaves', ROUND((new_price - cost) * units - profit_now, 2), 'USD' FROM c
   ) ORDER BY o

3. 03Monthly profit as customers leave

   combines data from multiple inputs · buckets values by condition · sorts the output

   WITH p AS (
     SELECT
       MAX(CASE WHEN assumption = 'Current price' THEN CAST(value AS DOUBLE) END) AS price,
       MAX(CASE WHEN assumption = 'Cost per unit' THEN CAST(value AS DOUBLE) END) AS cost,
       MAX(CASE WHEN assumption = 'Units sold per month' THEN CAST(value AS DOUBLE) END) AS units,
       MAX(CASE WHEN assumption = 'Price increase' THEN CAST(value AS DOUBLE) END) / 100.0 AS inc
     FROM input_1
   )
   SELECT
     -v AS volume_change_pct,
     ROUND((price * (1 + inc) - cost) * units * (1 - v / 100.0), 2) AS profit_with_increase,
     ROUND((price - cost) * units, 2) AS profit_today
   FROM p CROSS JOIN generate_series(0, 40, 5) AS g(v)
   ORDER BY v