# Training Exercises — Asset Management Database

> Progressive hands-on exercises for DBeaver. Each exercise states a goal, gives a hint, and provides the solution query with an explanation. Try to write the query yourself before looking at the solution.

---

## How to Use This Document

1. Open DBeaver, connect to the `asset_mgmt` database (port 5433)
2. For each exercise, open a new SQL editor and try to write the query
3. If stuck, read the hint
4. Compare your query to the solution — there are usually multiple valid approaches
5. Read the explanation to understand what the query demonstrates

**Convention:** Solutions are marked with `--- SOLUTION ---`. Don't peek until you've tried!

**Useful data in the database:**
- Client: C-00001 (client_id = 5)
- Portfolios: P-00001 (portfolio_id = 5, discretionary), P-00002 (portfolio_id = 7, advisory)
- Strategy: USD_BALANCED (strategy_id = 8)
- Securities: 102 total — 50 equities, 50 ETFs, 2 commodities (AAPL = security_id = 8)

---

## Exercise 1: List All Securities

**Goal:** List all securities with their type, ticker, ISIN, name, domicile, and reporting currency.

**Hint:** This is a simple SELECT from the `security` table. No joins needed. There are 102 rows.

--- SOLUTION ---

```sql
SELECT security_type, ticker, isin, name,
       domicile_country_code, reporting_currency
FROM security
ORDER BY security_type, ticker;
```

**What this demonstrates:** The `security` master table. It's the simplest table — one row per security, no bitemporal columns. It holds stable identity attributes. Note the variety of `security_type` values (EQUITY, ETF, COMMODITY).

---

## Exercise 2: Apple's Price on a Specific Date

**Goal:** Find Apple's last price and market cap on August 18, 2025.

**Hint:** You need to JOIN `security` (to find Apple by ISIN or ticker) with `security_metric_history` (to get the price). The key is the SCD2 predicate: the row where `valid_from <= date AND valid_to >= date`.

--- SOLUTION ---

```sql
SELECT s.ticker, s.name,
       m.valid_from, m.valid_to,
       m.last_price, m.market_cap
FROM security s
JOIN security_metric_history m
    ON  m.security_id = s.security_id
    AND DATE '2025-08-18' BETWEEN m.valid_from AND m.valid_to
WHERE s.isin = 'US0378331005';
```

**What this demonstrates:** The SCD2 point-in-time query pattern. The predicate `DATE '2025-08-18' BETWEEN m.valid_from AND m.valid_to` finds the single interval that was active on that date. This returns exactly one row — the interval invariant guarantees it. This is the pattern you'll use for every bitemporal query.

---

## Exercise 3: Convert a USD Value to CHF

**Goal:** Find Apple's price and market cap on August 18, 2025, converted to CHF using the USD/CHF rate as-of that date.

**Hint:** You need a third JOIN — to `fx_rate_history` using the same SCD2 predicate. The rate is stored as "quote per base" (USD/CHF = how many CHF for 1 USD). Multiply USD values by the rate to get CHF.

--- SOLUTION ---

```sql
SELECT s.name,
       m.last_price AS price_usd,
       m.market_cap AS market_cap_usd,
       fx.rate AS usd_chf_rate,
       m.last_price * fx.rate AS price_chf,
       m.market_cap * fx.rate AS market_cap_chf
FROM security s
JOIN security_metric_history m
    ON  m.security_id = s.security_id
    AND DATE '2025-08-18' BETWEEN m.valid_from AND m.valid_to
JOIN fx_rate_history fx
    ON  fx.base_currency = 'USD'
    AND fx.quote_currency = 'CHF'
    AND DATE '2025-08-18' BETWEEN fx.valid_from AND fx.valid_to
WHERE s.isin = 'US0378331005';
```

**What this demonstrates:** The unified query model in action. Both the metric table and the FX table use the **same predicate shape** (`:as_of BETWEEN valid_from AND valid_to`). You can chain any number of bitemporal joins this way. The CHF conversion is just arithmetic — `amount_usd * rate`.

---

## Exercise 4: Client Portfolios and Current Mandate

**Goal:** Find all portfolios for client C-00001, showing the portfolio code, current mandate type, and current fee rate.

**Hint:** JOIN `client` to `portfolio` to get the portfolios. Then JOIN `portfolio_mandate_history` and `portfolio_fee_history` — but these are SCD2 tables, so you need the "current" row (`valid_to = '9999-12-31'`) or the `:as_of` predicate with today's date.

--- SOLUTION ---

```sql
SELECT c.client_code,
       p.portfolio_code,
       p.mandate_type AS portfolio_mandate_type,
       mh.mandate_type AS current_mandate_type,
       fh.management_fee_rate,
       fh.hard_floor_chf
FROM client c
JOIN portfolio p ON p.client_id = c.client_id
LEFT JOIN portfolio_mandate_history mh
    ON  mh.portfolio_id = p.portfolio_id
    AND mh.valid_to = '9999-12-31'
LEFT JOIN portfolio_fee_history fh
    ON  fh.portfolio_id = p.portfolio_id
    AND fh.valid_to = '9999-12-31'
WHERE c.client_code = 'C-00001'
ORDER BY p.portfolio_code;
```

**What this demonstrates:** "Current" queries use `valid_to = '9999-12-31'` — this finds the open interval (the latest value). The `LEFT JOIN` is used in case a portfolio doesn't have a mandate or fee record yet. Note that `portfolio.mandate_type` is the master table's copy (may be stale); `portfolio_mandate_history.mandate_type` is the SCD2-tracked version that's always point-in-time correct.

---

## Exercise 5: What Was the Fee Rate on a Specific Date?

**Goal:** Client C-00001, portfolio P-00001 — what was the management fee rate on March 1, 2025? And what is it today? Show both.

**Hint:** The fee changed mid-quarter (from 0.8% to 0.6% on March 15). Query `portfolio_fee_history` with two different dates using the SCD2 predicate. You'll need the portfolio_id (which is 5).

--- SOLUTION ---

```sql
SELECT 'March 1, 2025' AS as_of_date,
       fh.management_fee_rate AS fee_rate_on_mar_1
FROM portfolio_fee_history fh
WHERE fh.portfolio_id = 5
  AND DATE '2025-03-01' BETWEEN fh.valid_from AND fh.valid_to

UNION ALL

SELECT 'Today' AS as_of_date,
       fh.management_fee_rate AS fee_rate_on_mar_1
FROM portfolio_fee_history fh
WHERE fh.portfolio_id = 5
  AND fh.valid_to = '9999-12-31';
```

**What this demonstrates:** Bitemporal client data. The same portfolio has different fee rates at different points in time. On March 1, the rate was 0.80% (the interval valid Jan 1 – Mar 14). After March 15, it became 0.60%. This is why the fee engine needs SCD2 — to calculate fees pro-rata across rate changes.

---

## Exercise 6: Strategy Constraints

**Goal:** List all constraints for the USD Balanced strategy, grouped by constraint type. Show the constraint type, dimension, operator, and the JSONB value.

**Hint:** Query `portfolio_constraint` where `constraint_scope = 'STRATEGY'` and `strategy_id = 8`. Use the SCD2 predicate to get the current constraints. Group or order by constraint type.

--- SOLUTION ---

```sql
SELECT constraint_type,
       dimension,
       operator,
       value,
       valid_from
FROM portfolio_constraint
WHERE constraint_scope = 'STRATEGY'
  AND strategy_id = 8
  AND valid_to = '9999-12-31'
ORDER BY constraint_type, dimension;
```

**What this demonstrates:** The constraint table stores strategy-level and client-level constraints in one table, distinguished by `constraint_scope`. The `value` column is JSONB — it holds different shapes depending on the constraint type (allocation bands have `{"class": "EQUITY", "target": 0.50, ...}`, quality filters have `{"min_usd": 5000000000}`, etc.).

---

## Exercise 7: Screen the Universe by Metrics

**Goal:** Find all equities with market cap above $5 billion AND a technical score of 3 or higher, as of the latest data. Show the ticker, name, market cap, and technical score.

**Hint:** You need to JOIN `security` with `security_metric_history` (for market_cap) using the SCD2 predicate. The technical score is in the JSONB `extra_metrics` column — extract it with `(extra_metrics->>'technical_score')::int`. Use `valid_to = '9999-12-31'` for current values.

--- SOLUTION ---

```sql
SELECT s.ticker,
       s.name,
       m.market_cap,
       (m.extra_metrics->>'technical_score')::int AS technical_score
FROM security s
JOIN security_metric_history m
    ON  m.security_id = s.security_id
    AND m.valid_to = '9999-12-31'
WHERE s.security_type = 'EQUITY'
  AND m.market_cap > 5000000000
  AND (m.extra_metrics->>'technical_score')::int >= 3
ORDER BY (m.extra_metrics->>'technical_score')::int DESC,
         m.market_cap DESC;
```

**What this demonstrates:** JSONB queries on `extra_metrics`. The `->>` operator extracts a JSONB key as text, then `::int` casts it. The `extra_metrics` column holds evolving metrics that haven't been promoted to typed columns yet. This is the screening pattern the reconstruction engine uses to filter the universe.

---

## Exercise 8: Client Portfolio AuM

**Goal:** Find the total portfolio value (AuM) for client C-00001, broken down by portfolio. Show the portfolio code, the total market value in CHF, and the total in the position currency. Use current positions.

**Hint:** Query `portfolio_position_history` with `valid_to = '9999-12-31'`. Some positions are in USD, some in CHF. Sum `market_value_chf` (which is pre-converted) to get the total in CHF. JOIN to `portfolio` to get the portfolio code, then JOIN to `client` to filter by client.

--- SOLUTION ---

```sql
SELECT p.portfolio_code,
       count(*) AS position_count,
       SUM(ph.market_value_chf) AS total_value_chf,
       SUM(ph.market_value) AS total_value_local_ccy
FROM client c
JOIN portfolio p ON p.client_id = c.client_id
JOIN portfolio_position_history ph ON ph.portfolio_id = p.portfolio_id
    AND ph.valid_to = '9999-12-31'
WHERE c.client_code = 'C-00001'
GROUP BY p.portfolio_code
ORDER BY p.portfolio_code;
```

**What this demonstrates:** Portfolio positions use the same SCD2 pattern — `valid_to = '9999-12-31'` gives you current holdings. The `market_value_chf` column is pre-converted during ingestion (by the data pipeline), so you can sum across currencies directly. The `security_id IS NULL` rows represent cash positions (no underlying security).

---

## Exercise 9: Reconstruction Inputs as-of a Date

**Goal:** For portfolio P-00001 (portfolio_id = 5) on August 26, 2025, show all the SCD2 inputs that the reconstruction engine would use: the strategy assigned, the mandate type, and the fee rate.

**Hint:** You need four separate SCD2 queries (or a UNION): strategy assignment from `portfolio_strategy_history`, mandate type from `portfolio_mandate_history`, fee rate from `portfolio_fee_history`, and strategy name from `strategy`. All use the same predicate with date '2025-08-26'.

--- SOLUTION ---

```sql
SELECT 'strategy' AS input_type,
       s.strategy_code || ' — ' || s.name AS value
FROM portfolio_strategy_history psh
JOIN strategy s ON s.strategy_id = psh.strategy_id
    AND DATE '2025-08-26' BETWEEN s.valid_from AND s.valid_to
WHERE psh.portfolio_id = 5
    AND DATE '2025-08-26' BETWEEN psh.valid_from AND psh.valid_to

UNION ALL

SELECT 'mandate_type' AS input_type,
       mh.mandate_type AS value
FROM portfolio_mandate_history mh
WHERE mh.portfolio_id = 5
    AND DATE '2025-08-26' BETWEEN mh.valid_from AND mh.valid_to

UNION ALL

SELECT 'fee_rate' AS input_type,
       fh.management_fee_rate::text || ' (floor: ' || fh.hard_floor_chf::text || ' CHF)' AS value
FROM portfolio_fee_history fh
WHERE fh.portfolio_id = 5
    AND DATE '2025-08-26' BETWEEN fh.valid_from AND fh.valid_to;
```

**What this demonstrates:** The reconstruction engine fetches multiple SCD2 inputs as-of the target date. Each table uses the **same predicate** — this is what makes the system composable. The engine doesn't need special logic per table; it just applies `:as_of BETWEEN valid_from AND valid_to` everywhere.

---

## Exercise 10: Fee Pro-Rata Calculation

**Goal:** For portfolio P-00001 (portfolio_id = 5), show how the fee rate changed during Q1 2025. List each fee interval that overlaps with January 1 – March 31, showing the rate, the number of days at that rate, and the pro-rata weight.

**Hint:** The fee changed on March 15 (from 0.8% to 0.6%). Query `portfolio_fee_history` for portfolio_id = 5. Find intervals that overlap with Q1 (Jan 1 – Mar 31). The number of days at each rate is `LEAST(valid_to, '2025-03-31') - GREATEST(valid_from, '2025-01-01') + 1`.

--- SOLUTION ---

```sql
SELECT fh.management_fee_rate,
       fh.valid_from,
       fh.valid_to,
       -- Days in this interval that fall within Q1
       (LEAST(fh.valid_to, DATE '2025-03-31')
        - GREATEST(fh.valid_from, DATE '2025-01-01') + 1) AS days_in_q1,
       -- Total days in Q1 (90 for Jan-Mar)
       90 AS total_q1_days,
       -- Pro-rata weight
       (LEAST(fh.valid_to, DATE '2025-03-31')
        - GREATEST(fh.valid_from, DATE '2025-01-01') + 1)::numeric / 90 AS pro_rata_weight
FROM portfolio_fee_history fh
WHERE fh.portfolio_id = 5
  AND fh.valid_from <= DATE '2025-03-31'
  AND fh.valid_to >= DATE '2025-01-01'
ORDER BY fh.valid_from;
```

**Result:**

```
 management_fee_rate | valid_from |  valid_to  | days_in_q1 | total_q1_days | pro_rata_weight
---------------------+------------+------------+------------+---------------+----------------
              0.0080 | 2025-01-01 | 2025-03-14 |         73 |            90 |     0.8111
              0.0060 | 2025-03-15 | 9999-12-31 |         17 |            90 |     0.1889
```

**What this demonstrates:** Fee pro-rata across rate changes. The effective Q1 fee rate is `0.8111 * 0.008 + 0.1889 * 0.006 = 0.00762` (0.762%). The Python fee engine does this calculation automatically — it queries the SCD2 fee intervals overlapping the billing period and weights each by the number of days. The SCD2 intervals make this exact and auditable.

---

## Exercise 11: Weekend Point-in-Time (Bonus)

**Goal:** Show that the SCD2 interval model handles weekends correctly. Find Apple's price on Saturday, August 16, 2025 — a day with no trading. It should return Friday's price.

**Hint:** The same query as Exercise 2, but with date '2025-08-16'. The Friday interval (valid_from = Aug 15) should have `valid_to = Aug 17` (spanning the weekend). Check the `valid_from` and `valid_to` columns in the result.

--- SOLUTION ---

```sql
SELECT s.ticker,
       m.last_price,
       m.valid_from AS metric_valid_from,
       m.valid_to AS metric_valid_to
FROM security s
JOIN security_metric_history m
    ON  m.security_id = s.security_id
    AND DATE '2025-08-16' BETWEEN m.valid_from AND m.valid_to
WHERE s.isin = 'US0378331005';
```

**Expected result:** `last_price = 233.85`, `valid_from = 2025-08-15`, `valid_to = 2025-08-17`. The Friday interval spans Saturday and Sunday because no new observation arrived until Monday. The query on Saturday returns Friday's price — correct behavior.

**What this demonstrates:** The interval model automatically handles non-trading days. No special logic for weekends or holidays — the previous interval simply holds until the next observation closes it.

---

## Exercise 12: All Fee Changes in a Period (Bonus)

**Goal:** List all fee rate changes across all portfolios during 2025. Show the portfolio code, the old rate, the new rate, and the effective date of the change.

**Hint:** A "change" happens when a new interval opens (valid_from in 2025) and there was a previous interval for the same portfolio. You can use `LAG()` window function to compare each row's rate to the previous row's rate for the same portfolio.

--- SOLUTION ---

```sql
SELECT p.portfolio_code,
       fh.valid_from AS effective_date,
       LAG(fh.management_fee_rate) OVER (PARTITION BY fh.portfolio_id ORDER BY fh.valid_from) AS old_rate,
       fh.management_fee_rate AS new_rate,
       fh.changed_by_user
FROM portfolio_fee_history fh
JOIN portfolio p ON p.portfolio_id = fh.portfolio_id
WHERE fh.valid_from >= DATE '2025-01-01'
  AND fh.valid_from <= DATE '2025-12-31'
ORDER BY p.portfolio_code, fh.valid_from;
```

**What this demonstrates:** The `LAG()` window function to detect changes across SCD2 intervals. Each new interval (row) represents a change. By comparing to the previous row for the same portfolio, you can see what the old rate was. The `changed_by_user` column provides the audit trail — who made the change.

---

*After completing these exercises, try writing your own queries. The query cheatsheet at `docs/query-cheatsheet.md` has common patterns for quick reference. The architecture guide at `docs/architecture-guide.md` explains the design philosophy.*