source: docs/P6-AdvancedReports/AdvancedReports.md@ 4438e45

# Advanced Reports

This is a solo project (see [UseCaseModel](../P3-UseCaseModel/UseCaseModel.md#realization-details-on-selection-of-the-most-important-use-cases)),
so the rubric's "2 per team member" is 2 reports total. Both are implemented as
single SQL statements, wrapped as callable SQL functions in
[`schema_creation.sql`](../../server/db/schema_creation.sql) (`report_top_traders`,
`report_market_performance`) so they are actual reports inside the prototype — menu
options `[10]` and `[11]` in `server/reports.go` — not just documentation. No change to
[ERModel](../P1-ConceptualModel/ERModel.md) or [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md)
was needed: both reports read `transactions`, `market_trades` and `orders`, all of which
already carry everything required.

### Notation used below

Both solutions need grouping, aggregation and computed attributes that plain relational
algebra has no notation for, so the relational-algebra sections use the standard *extended*
operators:

| Symbol | Meaning |
|---|---|
| `σ_cond(R)` | selection |
| `π_list(R)` | projection — a list entry `expr → name` is a **generalized projection**: a computed attribute, not just a column reference |
| `ρ_name(R)` | rename |
| `R ⋈_cond S` | inner join |
| `R ⟕_cond S` | left outer join (needed wherever a group can legitimately have zero matching rows on the other side, e.g. zero profitable periods, zero participating users) |
| `γ_{grouping; agg → name, …}(R)` | grouping/aggregation |
| `τ_attr(R)` | sort, for the presentation order only |

## Top traders by realized performance

### Data requirements description

*"Which users actually made money, how much, how efficiently, and how consistently — over
a quarter, a year, or several years?"* This is the natural crypto-exchange analogue of "which
customers bring the most profit" from the phase brief: a Trader's `available_balance` and
`invested_balance` (P1 `Users`) show a live snapshot, but they say nothing about performance
*over a chosen window*, and nothing at all about whether a user's results are one lucky
quarter or a repeatable pattern. All of it is derivable from
[`transactions`](../../server/db/schema_creation.sql) as it already exists: every buy, sell
and fee is one signed row there (see [UseCase0004](../P3-UseCaseModel/UseCase0004.md) and
[UseCase0005](../P3-UseCaseModel/UseCase0005.md) for how each row is produced), so no new
column or table is needed.

Given a period `[from, to)`:

- **Realized P/L** = `SUM(amount)` over that user's `buy`, `sell` and `fee` transactions in
  the period (deposits excluded — they are not trading results).
- **Total invested** = absolute value of the sum of that user's `buy` transactions in the
  period (buy amounts are stored negative, per
  [ERModel](../P1-ConceptualModel/ERModel.md#transactions)).
- **ROI %** = realized P/L ÷ total invested × 100.
- The period is additionally bucketed into **quarters** internally, regardless of how wide
  `[from, to)` is, to measure:
  - **Profitable / losing periods** — how many quarters inside the window had positive vs.
    negative P/L.
  - **Consistency %** = profitable periods ÷ total periods with any activity × 100 — two
    users can have the same total P/L with very different risk profiles, and this is the
    number that tells them apart.

### Solution SQL

Implemented as `project.report_top_traders(p_from, p_to)` in
[`schema_creation.sql`](../../server/db/schema_creation.sql):

```sql
CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
RETURNS TABLE (
    username            varchar,
    realized_pl         numeric,
    total_invested      numeric,
    roi_pct             numeric,
    profitable_periods  bigint,
    losing_periods      bigint,
    total_periods       bigint,
    consistency_pct     numeric
)
LANGUAGE sql STABLE AS $$
    WITH period_pl AS (
        SELECT
            t.user_id,
            date_trunc('quarter', t.created_at)          AS period,
            SUM(t.amount)                                AS period_pl,
            SUM(t.amount) FILTER (WHERE t.type = 'buy')  AS period_buy
        FROM project.transactions t
        WHERE t.type IN ('buy', 'sell', 'fee')
          AND t.created_at >= p_from
          AND t.created_at <  p_to
        GROUP BY t.user_id, date_trunc('quarter', t.created_at)
    )
    SELECT
        u.username,
        SUM(pp.period_pl)                                                       AS realized_pl,
        ABS(SUM(pp.period_buy))                                                 AS total_invested,
        ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2)  AS roi_pct,
        COUNT(*) FILTER (WHERE pp.period_pl > 0)                                AS profitable_periods,
        COUNT(*) FILTER (WHERE pp.period_pl < 0)                                AS losing_periods,
        COUNT(*)                                                                AS total_periods,
        ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
              / NULLIF(COUNT(*), 0) * 100, 2)                                   AS consistency_pct
    FROM period_pl pp
    JOIN project.users u ON u.id = pp.user_id
    GROUP BY u.id, u.username
    ORDER BY realized_pl DESC;
$$;
```

One `SELECT`, one `WITH` CTE — the CTE does the quarter bucketing per user, the outer query
rolls those buckets up into the totals, the ROI/consistency percentages and the ranking.

**Verified run.** [`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) adds five
quarters of round-trip trades (2025-07 through 2026-07) on top of the normal seed data
specifically so this report has more than one period to work with — see that file's header
for exactly what it inserts and why it is optional rather than part of `-init`. Run against
PostgreSQL 16 with `data_load.sql` + `reports_demo_data.sql` loaded, through the actual CLI
(`[10] Report: top traders`, range `2025-01-01` to `2026-09-17`):

```
  Username      Realized P/L        Invested       ROI %   Prof.    Loss   Total  Consist. %
  ------------------------------------------------------------------------------------------
  bob              +991.0000       6300.0000       15.73       3       0       3      100.00
  alice            -475.0000      24250.0000       -1.96       2       3       5       40.00
```

Sorting by raw P/L alone would rank alice above bob if alice's numbers were all positive; here
it does the opposite, and that is the point of the report — alice traded a much larger total
(and one of her seeded round trips landed in the same quarter as the ETH buy already in
`data_load.sql`, tipping that quarter into a loss), while bob's three quarters were smaller
but every one of them profitable, giving him both the better ROI and a perfect consistency
score. A single "total profit" column would have hidden that difference completely.

### Solution Relational Algebra

```
T_period  = σ_{type ∈ {buy,sell,fee} ∧ created_at ≥ from ∧ created_at < to} (Transactions)

T_tagged  = π_{user_id, created_at, amount,
               (type = 'buy' ? amount : 0) → buy_amt} (T_period)

Periods   = γ_{user_id, quarter(created_at) → period ;
               SUM(amount) → period_pl, SUM(buy_amt) → period_buy} (T_tagged)

Totals      = γ_{user_id ; SUM(period_pl) → realized_pl,
                 ABS(SUM(period_buy)) → total_invested,
                 COUNT(*) → total_periods} (Periods)
Profitable  = γ_{user_id ; COUNT(*) → profitable_periods} (σ_{period_pl > 0} (Periods))
Losing      = γ_{user_id ; COUNT(*) → losing_periods}     (σ_{period_pl < 0} (Periods))

Combined  = (Totals ⟕_{user_id} Profitable) ⟕_{user_id} Losing

Ranked    = π_{user_id, realized_pl, total_invested,
               (realized_pl / total_invested × 100) → roi_pct,
               COALESCE(profitable_periods, 0) → profitable_periods,
               COALESCE(losing_periods, 0) → losing_periods,
               total_periods,
               (COALESCE(profitable_periods, 0) / total_periods × 100) → consistency_pct}
             (Combined)

Result    = τ_{realized_pl ↓} (π_{username, realized_pl, total_invested, roi_pct,
               profitable_periods, losing_periods, total_periods, consistency_pct}
               (Ranked ⋈_{user_id = id} Users))
```

`Totals`/`Profitable`/`Losing` are three separate groupings of the same `Periods` relation
because plain aggregation has no built-in "count only where X" operator; the two outer joins
recombine them (`⟕`, not `⋈`, because a user with zero losing quarters must still appear with
`losing_periods = 0`, not disappear from the result).

## Market performance leaderboard

### Data requirements description

*"Which markets were actually worth making — high volume, real price movement, real user
interest — over a chosen period?"* This is the "products that bring the most profit" /
"good locations" family of question from the phase brief, translated to markets instead of
physical products: a market with heavy volume but a dead price, or a big price swing nobody
actually traded, are both misleading on their own; this report puts volume, trade count,
price return and user participation side by side so a market's performance over a
quarter/year/multi-year window can be judged as a whole, not from one number in isolation.
Everything needed already exists: `market_trades` is the single source of truth for price and
volume for every market ([PrototypeImplementation](../P4-Prototype/PrototypeImplementation.md#what-the-prototype-demonstrates-about-the-database-design)),
and `orders` is the only place a specific user is tied to a specific market
([ERModel](../P1-ConceptualModel/ERModel.md#placedon--markets-1--orders-n-total-on-orders)) —
`market_trades` deliberately has no `user_id` column, since it also records the market
simulator's own fills.

Given a period `[from, to)`, per market:

- **Total volume** = `SUM(quantity)` over its trades in the period.
- **Trade count** = `COUNT(*)` over the same trades (real fills and simulated fills alike —
  this is activity, not just user activity).
- **Average trading price** = `AVG(price)` over the same trades.
- **Market return %** = `(last trade price − first trade price) ÷ first trade price × 100`,
  ordering trades by `executed_at` inside the period.
- **Participating users** = `COUNT(DISTINCT user_id)` from that market's **executed orders**
  in the period — the only correct source, since `market_trades` cannot answer this question
  at all.

### Solution SQL

Implemented as `project.report_market_performance(p_from, p_to)` in
[`schema_creation.sql`](../../server/db/schema_creation.sql):

```sql
CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
RETURNS TABLE (
    symbol               varchar,
    quote_currency       char(3),
    total_volume         numeric,
    trade_count          bigint,
    avg_price            numeric,
    market_return_pct    numeric,
    participating_users  bigint
)
LANGUAGE sql STABLE AS $$
    WITH trades AS (
        SELECT
            market_id, price, quantity, executed_at,
            FIRST_VALUE(price) OVER w AS first_price,
            LAST_VALUE(price)  OVER (PARTITION BY market_id ORDER BY executed_at
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
        FROM project.market_trades
        WHERE executed_at >= p_from AND executed_at < p_to
        WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
    ),
    market_stats AS (
        SELECT
            market_id,
            SUM(quantity)    AS total_volume,
            COUNT(*)         AS trade_count,
            AVG(price)       AS avg_price,
            MAX(first_price) AS first_price,
            MAX(last_price)  AS last_price
        FROM trades
        GROUP BY market_id
    ),
    participation AS (
        SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
        FROM project.orders
        WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
        GROUP BY market_id
    )
    SELECT
        c.symbol,
        m.quote_currency,
        ms.total_volume,
        ms.trade_count,
        ROUND(ms.avg_price, 6)                                                       AS avg_price,
        ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
        COALESCE(p.participating_users, 0)                                          AS participating_users
    FROM market_stats ms
    JOIN project.markets m ON m.id = ms.market_id
    JOIN project.crypto  c ON c.id = m.crypto_id
    LEFT JOIN participation p ON p.market_id = ms.market_id
    ORDER BY ms.total_volume DESC;
$$;
```

`FIRST_VALUE`/`LAST_VALUE` pick the period's opening and closing price per market without a
self-join; `LEFT JOIN participation` is required, not optional — a market can have trades
from the simulator alone and legitimately zero participating users, and it must still show
`0`, not disappear from the report.

**Verified run.** Same seed as above (`data_load.sql` + `reports_demo_data.sql`, which also
adds a BTC/USD uptrend and an ETH/USD downtrend across the same five quarters — see that
file). Run through the CLI (`[11] Report: market performance`, `2025-01-01` to `2026-09-17`):

```
  Symbol  Quote        Volume    Trades       Avg Price      Return %     Users
  -----------------------------------------------------------------------------
  DOGE    USD      29500.0000         3        0.120583         +2.95         0
  ADA     USD       2500.0000         3        0.450750         +1.62         0
  SOL     USD         23.5000         3      165.283333         +1.13         0
  ETH     USD         14.3500         8     3622.312500        -12.00         2
  BTC     USD          3.9750         9    59447.400000        +67.85         2
```

BTC/USD and ETH/USD are the only two markets with historical (multi-quarter) data seeded, and
they show it: BTC's price nearly tripled over the period (`+67.85%`), while ETH quietly lost
`12%`. ADA/SOL/DOGE only have the few minutes of `data_load.sql`'s own recent seed trades, so
their return numbers reflect that narrow window, and their `0` participating users is correct
— `data_load.sql` seeds trade history for every market but only ever places an *order* on ETH.

A price-volatility column (standard deviation of trade price) was dropped from this report
after review — with only a handful of trades per market in most periods it read as noise
rather than signal, and total volume plus return already carry the useful information.

### Solution Relational Algebra

```
MT_period  = σ_{executed_at ≥ from ∧ executed_at < to} (MarketTrades)

Bounds     = γ_{market_id ; MIN(executed_at) → t_first, MAX(executed_at) → t_last} (MT_period)

FirstPx    = π_{market_id, price → first_price}
               (MT_period ⋈_{MT_period.market_id = Bounds.market_id
                              ∧ executed_at = t_first} Bounds)
LastPx     = π_{market_id, price → last_price}
               (MT_period ⋈_{MT_period.market_id = Bounds.market_id
                              ∧ executed_at = t_last} Bounds)

Stats      = γ_{market_id ; SUM(quantity) → total_volume, COUNT(*) → trade_count,
                AVG(price) → avg_price} (MT_period)

MarketStats = (Stats ⋈_{market_id} FirstPx) ⋈_{market_id} LastPx

O_period      = σ_{status = 'executed' ∧ executed_at ≥ from ∧ executed_at < to} (Orders)
Participation = γ_{market_id ; COUNT_DISTINCT(user_id) → participating_users} (O_period)

Joined = ((MarketStats ⟕_{market_id} Participation)
            ⋈_{market_id = id} Markets) ⋈_{crypto_id = id} Crypto

Result = τ_{total_volume ↓} (
           π_{symbol, quote_currency, total_volume, trade_count, avg_price,
              (last_price − first_price) / first_price × 100 → market_return_pct,
              COALESCE(participating_users, 0) → participating_users}
             (Joined) )
```

`FirstPx`/`LastPx` express `FIRST_VALUE`/`LAST_VALUE` — which have no classical relational-
algebra equivalent — as an aggregation for the boundary timestamp per market followed by a
self-join back to `MarketTrades` to recover the price at that timestamp; this is the standard
way to express "value at the extreme of a group" in extended relational algebra.

## AI usage

AI was used in this phase and is logged in full, per the course rule for P1 onward.

- **Phase log:** [AdvancedReportsAIUsage.md](AdvancedReportsAIUsage.md) — service used, what
  the AI produced, and what I decided myself.

**Service:** Claude Code (Anthropic), https://claude.com/claude-code — Claude subscription,
model Claude Sonnet 5.

**In short:** I specified both report questions in full — including the exact formulas for
P/L, ROI, consistency, market return, volatility and user participation — and asked the AI to
turn them into working SQL, wire them into the prototype as real reports, build the
relational-algebra equivalents, and produce demonstration data rich enough to show the
reports doing something non-trivial. In a follow-up, I asked for the price-volatility column
to be dropped from the market performance report — see
[AdvancedReportsAIUsage](AdvancedReportsAIUsage.md#follow-up--2026-09-17) for that change.