Per-Group Limits - DeciDB Examples

THE PROBLEM

A company is selecting employees for promotion this cycle. Two rules apply: each department may have at most 2 promotions (to keep things fair across teams), and the company-wide total is capped at 4 promotions. The goal is to maximize the combined performance score of promoted employees. Without the PER keyword, enforcing the per-department rule would require writing one explicit constraint per department name — impractical when departments are determined by data. PER generates one constraint per distinct column value automatically.

SAMPLE DATA

CREATE TABLE employees (
    id INTEGER, name VARCHAR,
    department VARCHAR, performance_score REAL
);
INSERT INTO employees VALUES
    (1, 'Alice', 'Engineering', 9.5),
    (2, 'Bob',   'Engineering', 9.4),
    (3, 'Carol', 'Engineering', 9.3),
    (4, 'Dave',  'Marketing',   8.9),
    (5, 'Eve',   'Marketing',   8.7),
    (6, 'Frank', 'Marketing',   7.4),
    (7, 'Grace', 'Finance',     8.6),
    (8, 'Hank',  'Finance',     8.5);

THE QUERY

SELECT name, department, performance_score, promoted
FROM employees
DECIDE promoted IS BOOLEAN
SUCH THAT
    SUM(promoted) <= 2 PER department AND
    SUM(promoted) <= 4
MAXIMIZE SUM(promoted * performance_score);

QUERY BREAKDOWN

1

DECIDE promoted IS BOOLEAN — One binary variable per employee. promoted=1 means selected for promotion.

2

SUM(promoted) <= 2 PER department — DeciDB finds all distinct values of department in the data and emits one constraint for each: Engineering ≤2, Marketing ≤2, Finance ≤2. The number of departments is determined at query execution time, not at write time.

3

SUM(promoted) <= 4 — A global cap across all departments. Combined with the per-department cap, this forces real trade-offs.

4

MAXIMIZE SUM(promoted * performance_score) — Pick the highest-scoring combination that satisfies both the per-department and global caps.

RESULT

id	name	department	performance_score	promoted
1	Alice	Engineering	9.5	1
2	Bob	Engineering	9.4	1
3	Carol	Engineering	9.3	0
4	Dave	Marketing	8.9	1
5	Eve	Marketing	8.7	1
6	Frank	Marketing	7.4	0
7	Grace	Finance	8.6	0
8	Hank	Finance	8.5	0

INTERPRETATION

The solver promotes Alice (9.5), Bob (9.4), Dave (8.9), and Eve (8.7) for a combined score of 36.5. The PER constraint forces a real trade-off: without the department cap, the pure top-4 by score would be Alice, Bob, Carol, and Dave — three engineers and one marketer — scoring 37.1. But SUM(promoted) <= 2 PER department caps Engineering at two slots, so Carol (the third-best engineer) is excluded; Eve fills the freed slot as the next-best Marketing candidate. Finance is not represented at all because all four global slots were consumed by Engineering and Marketing. This illustrates how PER turns a single constraint template into data-driven per-group rules.