Postgres 17

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

CREATE TABLE ACCOUNT_QUOTA (
  ACCOUNT_ID   INT,
  SEGMENT      INT,
  QUOTA        INT
)

CREATE TABLE


INSERT INTO
  ACCOUNT_QUOTA
VALUES
  (1, 1, 2),
  (2, 1, 2),
  (3, 1, 2),
  (4, 1, 2),
  (1, 2, 2),
  (2, 2, 2),
  (3, 2, 2),
  (4, 2, 2)

INSERT 0 8


WITH
  FILL_SEGMENT AS
(
SELECT
    ACCOUNT_ID
  , SEGMENT
  , QUOTA
  , ROW_NUMBER() OVER (PARTITION BY SEGMENT ORDER BY ACCOUNT_ID) ROW_N
FROM
  ACCOUNT_QUOTA
),
  RANK_SEGMENT AS
(
SELECT 
    ACCOUNT_ID
  , SEGMENT
  , QUOTA
  , ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY segment) AS SEGMENT_RANK
FROM
  FILL_SEGMENT
WHERE
  ROW_N <= QUOTA
)
SELECT 
  *
FROM
  RANK_SEGMENT
WHERE
  SEGMENT_RANK = 1
ORDER BY
  SEGMENT,
  ACCOUNT_ID
;


account_id
segment
quota
segment_rank


1
1
2
1

2
1
2
1



SELECT 2