Portfolio Optimization - DeciDB Examples

THE PROBLEM

An investment manager builds a portfolio from a universe of stocks. The budget comes from a configuration table. Each asset has an expected return, a risk score, a minimum investment amount, and belongs to a sector. The portfolio must include at least 5 assets, stay within a total risk budget of 300 points, and have no more than 2 assets per sector (diversification). The objective maximizes expected dollar return, computed as return percentage × investment amount. This combines scalar subqueries, the PER keyword for per-sector limits, and complex objective coefficients.

SAMPLE DATA

CREATE TABLE Config (name VARCHAR, value INTEGER);
INSERT INTO Config VALUES ('budget', 25000);

CREATE TABLE Assets (
    ticker VARCHAR, name VARCHAR, sector VARCHAR,
    expected_return DOUBLE, risk_score INTEGER, min_investment INTEGER
);
INSERT INTO Assets VALUES
    ('MSFT', 'Microsoft', 'Technology', 11.2, 55, 5000),
    ('AAPL', 'Apple', 'Technology', 10.5, 60, 6000),
    ('PFE', 'Pfizer', 'Healthcare', 8.5, 35, 2000),
    ('JNJ', 'Johnson & Johnson', 'Healthcare', 7.2, 25, 3000),
    ('JPM', 'JP Morgan', 'Finance', 9.2, 50, 4000),
    ('BAC', 'Bank of America', 'Finance', 10.1, 55, 3000),
    ('XOM', 'Exxon', 'Energy', 8.0, 45, 3000),
    ('PG', 'Procter & Gamble', 'Consumer', 6.5, 20, 2000),
    ('KO', 'Coca-Cola', 'Consumer', 5.8, 15, 2000);

THE QUERY

SELECT ticker, name, sector, expected_return, risk_score, min_investment, x as selected
FROM Assets
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * min_investment) <= (SELECT value FROM Config WHERE name = 'budget') AND
    SUM(x * risk_score) <= 300 AND
    SUM(x) >= 5 AND
    SUM(x) <= 2 PER sector
MAXIMIZE SUM(x * expected_return * min_investment / 100);

QUERY BREAKDOWN

1

SUM(x * min_investment) <= (SELECT value FROM Config WHERE name = 'budget') — Total investment within external budget ($25,000).

2

SUM(x * risk_score) <= 300 — Portfolio risk within 300-point budget.

3

SUM(x) >= 5 — At least 5 assets for diversification.

4

SUM(x) <= 2 PER sector — At most 2 assets per sector. The PER keyword emits one constraint for each distinct value of sector in the data, so you don't have to list Technology, Healthcare, Finance, Energy, Consumer individually — adding a new sector to the table needs no query change.

5

MAXIMIZE SUM(x * expected_return * min_investment / 100) — Maximize expected dollar return (return% × investment / 100).

RESULT

ticker	name	sector	return	risk	investment	selected
MSFT	Microsoft	Technology	11.2%	55	$5,000	1
AAPL	Apple	Technology	10.5%	60	$6,000	1
PFE	Pfizer	Healthcare	8.5%	35	$2,000	1
JNJ	J&J	Healthcare	7.2%	25	$3,000	1
JPM	JP Morgan	Finance	9.2%	50	$4,000	1
BAC	Bank of America	Finance	10.1%	55	$3,000	1
XOM	Exxon	Energy	8.0%	45	$3,000	0
PG	P&G	Consumer	6.5%	20	$2,000	1
KO	Coca-Cola	Consumer	5.8%	15	$2,000	0

INTERPRETATION

The portfolio includes 7 assets spanning 4 of the 5 sectors: Microsoft ($5,000), Apple ($6,000), Pfizer ($2,000), J&J ($3,000), JP Morgan ($4,000), Bank of America ($3,000), and Procter & Gamble ($2,000). Total investment is exactly $25,000 (the full budget) and total risk is exactly 300 (the cap) — both constraints are binding. Expected dollar return is $2,377. The two-per-sector cap is binding for Technology (MSFT + AAPL), Healthcare (PFE + JNJ), and Finance (JPM + BAC); Consumer fills with PG only, and Energy is left out because Exxon's risk score (45) consumes too much of the risk budget. Coca-Cola is excluded over P&G because P&G has a higher expected return.