Index: docs/P6-AdvancedReports/wiki/AdvancedReports.md =================================================================== --- docs/P6-AdvancedReports/wiki/AdvancedReports.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P6-AdvancedReports/wiki/AdvancedReports.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,477 @@ += 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.