source: docs/P6-AdvancedReports/wiki/AdvancedReports.md@ ef1c1c7

= Advanced Reports =

This is a solo project (see [wiki:UseCaseModel]),
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` (`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
[wiki:ERModel] or [wiki:RelationalDesign]
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` (defined in `schema_creation.sql`) as it already exists: every buy, sell
and fee is one signed row there (see [wiki:UseCase0004] and
[wiki:UseCase0005] for how each row is produced), so no new
column or table is needed.

The `transactions` table, from `schema_creation.sql`:

{{{
CREATE TABLE project.transactions (
    id            uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       uuid           NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
    type          varchar(50)    NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
    amount        numeric(18,4)  NOT NULL,
    currency      char(3)        NOT NULL DEFAULT 'USD',
    related_order uuid           REFERENCES project.orders(id),
    created_at    timestamptz    NOT NULL DEFAULT now(),
    description   text
);
}}}

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
   [wiki:ERModel]).
 * '''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`:

{{{
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` 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
(shown in full in the Demonstration data section below)
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 ([wiki:PrototypeImplementation]),
and `orders` is the only place a specific user is tied to a specific market
([wiki:ERModel]) —
`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`:

{{{
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 in the Demonstration data section below). 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.

== Demonstration data ==

`reports_demo_data.sql`, the optional script both verified runs above were produced with:

{{{
-- reports_demo_data.sql
-- EduBerza - optional historical data for the P6 reports
-- Course: Databases 2025/2026 Winter, FINKI UKIM
--
-- data_load.sql only seeds ~10 minutes of trade history, which is enough to
-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or
-- report_market_performance() doing anything interesting: everything falls
-- into a single quarter, so "number of profitable periods" and "consistency"
-- are trivial and "market return" has almost no history to work with.
--
-- This script adds five quarters of synthetic transactions, market trades and
-- executed orders on top of an already-loaded data_load.sql, spanning
-- 2025-07 to 2026-07, so the two P6 reports have several periods and two
-- markets with opposite price trends to actually compare.
--
-- Deliberately NOT part of -init / -load-data: it only inserts into
-- transactions, market_trades and orders, and does not touch
-- users.available_balance/invested_balance or holdings, so it does not
-- disturb the balances the other use cases' documented "verified run"
-- sections depend on. Run it by hand, after data_load.sql, only to exercise
-- the two reports:
--
--   psql "$DATABASE_URL" -f server/db/schema_creation.sql
--   psql "$DATABASE_URL" -f server/db/data_load.sql
--   psql "$DATABASE_URL" -f server/db/reports_demo_data.sql
--
-- Idempotent: deletes its own previously-inserted rows (tagged via
-- description/source) before re-inserting.

SET search_path TO project, public;

DELETE FROM transactions  WHERE description = 'P6 demo data';
DELETE FROM orders        WHERE id IN (
    'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
    'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
    'e5555555-5555-5555-5555-555555555555'
);
DELETE FROM market_trades WHERE source = 'p6_demo';

-- ============================================================================
-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
-- ============================================================================
INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
    ('b1111111-1111-1111-1111-111111111111', 'buy',  -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'sell',  5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),

    ('b1111111-1111-1111-1111-111111111111', 'buy',  -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'sell',  3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),

    ('b1111111-1111-1111-1111-111111111111', 'buy',  -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'sell',  6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),

    ('b1111111-1111-1111-1111-111111111111', 'buy',  -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'sell',  2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),

    ('b1111111-1111-1111-1111-111111111111', 'buy',  -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'sell',  5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');

-- ============================================================================
-- Bob: three quarterly round trips, all profitable (100% consistency),
-- smaller total P/L than Alice but a higher ROI.
-- ============================================================================
INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
    ('b2222222-2222-2222-2222-222222222222', 'buy',  -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'sell',  2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),

    ('b2222222-2222-2222-2222-222222222222', 'buy',  -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'sell',  2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),

    ('b2222222-2222-2222-2222-222222222222', 'buy',  -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'sell',  2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');

-- ============================================================================
-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
-- source='p6_demo' keeps these separate from data_load.sql's own rows and
-- from live user/bot fills so this script can clean up after itself.
-- ============================================================================
INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
    ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy',  'p6_demo'),
    ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy',  'p6_demo'),
    ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy',  'p6_demo'),
    ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy',  'p6_demo'),

    ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00',  4000.000000, 3.000000, 'sell', 'p6_demo'),
    ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00',  3800.000000, 2.500000, 'sell', 'p6_demo'),
    ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00',  3600.000000, 2.000000, 'sell', 'p6_demo'),
    ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00',  3550.000000, 1.800000, 'sell', 'p6_demo');

-- ============================================================================
-- Executed orders: who participated in which market, across the same quarters.
-- ============================================================================
INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
    ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
     '2025-07-15 10:00', '2025-07-15 10:00'),
    ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
     '2025-10-15 10:00', '2025-10-15 10:00'),
    ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
     '2026-01-15 10:00', '2026-01-15 10:00'),
    ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
     '2026-04-15 10:00', '2026-04-15 10:00'),
    ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
     '2026-01-15 10:00', '2026-01-15 10:00');
}}}

== AI usage ==

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

 * '''Phase log:''' [wiki:AdvancedReportsAIUsage] — 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 the "Follow-up — 2026-09-17" section of
[wiki:AdvancedReportsAIUsage] for that change.