Index: docs/P6-AdvancedReports/AdvancedReports.md =================================================================== --- docs/P6-AdvancedReports/AdvancedReports.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ docs/P6-AdvancedReports/AdvancedReports.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) @@ -0,0 +1,340 @@ +# 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, volatility 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. +- **Price volatility** = the (sample) standard deviation of trade prices in 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, + price_volatility 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, + STDDEV(price) AS price_volatility, + 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, + ROUND(COALESCE(ms.price_volatility, 0), 6) AS price_volatility, + 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 % Volatility Users + --------------------------------------------------------------------------------------------- + DOGE USD 29500.0000 3 0.120583 +2.95 0.001843 0 + ADA USD 2500.0000 3 0.450750 +1.62 0.003783 0 + SOL USD 23.5000 3 165.283333 +1.13 0.943840 0 + ETH USD 14.3500 8 3622.312500 -12.00 182.657992 2 + BTC USD 3.9750 9 59447.400000 +67.85 10563.413339 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%`) with by far the highest +volatility, 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/volatility 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. + +### 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, STDDEV(price) → price_volatility} (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(price_volatility, 0) → price_volatility, + 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. Index: docs/P6-AdvancedReports/AdvancedReportsAIUsage.md =================================================================== --- docs/P6-AdvancedReports/AdvancedReportsAIUsage.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ docs/P6-AdvancedReports/AdvancedReportsAIUsage.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) @@ -0,0 +1,127 @@ +# Advanced Reports AI Usage + +## Name of AI service/solution that was used + +**Claude Code** (Anthropic) + +- **URL:** https://claude.com/claude-code +- **Type of service/subscription:** Claude subscription, model Claude Sonnet 5. + +## Final result + +### Diagram + +None. Both reports read `transactions`, `market_trades`, `orders`, `markets`, `crypto` and +`users` exactly as they already existed after +[Normalization](../P5-Normalization/Normalization.md) — no attribute or relation was missing, +so [ERModel](../P1-ConceptualModel/ERModel.md) and +[RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) needed no changes and there is +no new diagram for this phase. This is stated explicitly rather than left implicit because the +phase rubric specifically calls out modifying the design as the fallback when a good report +idea can't be answered by the data on hand — it wasn't needed here. + +### Results in details / description + +The AI: + +- Turned my two fully-specified report questions (the exact P/L, ROI, consistency, volume, + return, volatility and participation formulas were mine) into two single-statement SQL + queries, each wrapped as a `LANGUAGE sql STABLE` function + (`project.report_top_traders`, `project.report_market_performance`) in + [`schema_creation.sql`](../../server/db/schema_creation.sql), so the phase's "just one SQL + query" requirement is met by the query text itself, while still giving the prototype a + clean, parameterised, named thing to call. +- Wired both into the running CLI as real menu options — `server/reports.go`, options + `[10]`/`[11]` in `server/cli.go` — rather than leaving them as documentation-only SQL, per + the phase's own framing ("used as reports within your application"). +- Wrote the relational-algebra equivalent of each query, including how to express + `FIRST_VALUE`/`LAST_VALUE` (which have no classical RA equivalent) as an aggregation for the + boundary timestamp followed by a self-join, and how to express `FILTER (WHERE …)`-style + conditional counts as separate groupings recombined with left outer joins. +- Noticed that the existing `data_load.sql` seed data (a few minutes of trade history) cannot + demonstrate either report meaningfully — everything falls into one quarter, so "consistency" + and "market return over time" have nothing to show — and wrote + [`reports_demo_data.sql`](../../server/db/reports_demo_data.sql), an optional, separate, + idempotent script adding five quarters of synthetic transactions, market trades and executed + orders, deliberately excluded from `-init`/`-load-data` so it cannot disturb the balances the + other use cases' documented "verified run" sections depend on. +- Ran both reports against a live PostgreSQL 16 database with that demo data loaded, through + the actual CLI, and used the real output (including a run where alice's seeded quarter + interacted with a pre-existing `data_load.sql` transaction and flipped a profitable quarter + into a loss) as the verified evidence in [AdvancedReports.md](AdvancedReports.md), rather + than inventing example numbers. + +## Summary of AI involvement + +| | This session — 2026-09-16 | +|---|---| +| **What I brought** | The phase rubric, plus both report questions fully specified down to the exact aggregate formulas | +| **What the AI did** | Wrote the SQL, wrote the relational algebra, wired the reports into the CLI, designed and ran the demonstration data, verified everything against a live database | +| **What I decided** | To keep both reports as SQL functions rather than plain ad-hoc queries so they are actually usable from the application; to accept the AI's synthetic multi-quarter demo dataset rather than wait for enough real usage history to accumulate | + +The two ideas and their formulas were mine, specified in enough detail (P/L as the sum of +buy+sell+fee transactions, ROI relative to total buys, consistency as a share of profitable +quarters, market return as first-vs-last trade price, volatility as price standard deviation, +participation from orders rather than trades) that there was no separate "AI alternative +idea" to borrow from and document a change against, unlike the more open-ended P1–P3 phases — +the AI's job here was implementation and verification of a fully-specified design, which is +what is logged above and in the prompt below. + +## Entire AI usage log + +### 2026-09-16 + +**Intent:** hand over the P6 rubric together with both report ideas, fully specified, and +have the whole phase — SQL, relational algebra, prototype integration, and demonstration data +— produced and verified in one pass. + +**Prompt (student, verbatim):** +> Phase P6: Complex DB Reports (SQL, Stored Procedures, Relational Algebra) +> [the full phase rubric was pasted: 2 complex analytical reports solvable each with one SQL +> query, usable as reports within the application, with a note that helper views/functions/ +> procedures are acceptable when pure SQL isn't enough, that the design should be extended if +> a good idea needs data that doesn't exist yet, a requirement for the corresponding relational +> algebra, the exact `AdvancedReports`/`AdvancedReportsAIUsage` wiki templates, and the phase's +> AI-use rules] +> +> I will describe for you the 2 more complex sql queries: +> 1. Find the users who performed the best in a selected quarter, year, or multi-year period +> based on their realized trading performance, and show their total profit/loss, total +> amount invested, ROI, number of profitable and losing periods, and consistency of +> returns. Calculate realized P/L as SUM(sell transactions + buy transactions + fee +> transactions), calculate ROI as realized P/L / absolute value of total buy transactions × +> 100, and calculate consistency as profitable periods / total periods × 100. +> 2. Find which cryptocurrency markets performed best during a selected quarter, year, or +> multi-year period based on trading activity and price behavior, and report the markets +> with the highest trading volume, number of trades, number of participating users, and +> price return. Calculate total volume as SUM(trade quantity), average trading price as +> AVG(price), market return as (last trade price − first trade price) / first trade price × +> 100, price volatility as standard deviation of trade prices, and user participation as +> COUNT(DISTINCT user_id) from the related executed orders. + +**Response (AI, summarised):** +- Confirmed both report questions were answerable from the existing schema with no new + columns or tables, and said so explicitly rather than silently skipping that check. +- Wrote `report_top_traders` and `report_market_performance` as SQL functions, added to + `schema_creation.sql` under a new `REPORTS (P6)` section. +- Wrote `server/reports.go` (`ShowTopTraders`, `ShowMarketPerformance`, a shared + `promptPeriod` helper) and added menu options `[10]`/`[11]` to `server/cli.go`. +- Wrote `server/db/reports_demo_data.sql` for a richer, multi-quarter demonstration, and + documented in its own header exactly why it is optional and what it deliberately does not + touch (user balances, holdings). +- Built the prototype, ran both reports against the live database with the demo data loaded, + cross-checked the SQL output against hand-computed expected values for both seeded users, + and used that real output as the verified evidence. +- Wrote out the relational-algebra derivation for each query and [AdvancedReports.md](AdvancedReports.md). + +**What I decided:** to keep the demo dataset out of `-init`/`-load-data` rather than merge it +into `data_load.sql`, since the other phases' documented expected values (specific balances in +[BuildInstructions](../P4-Prototype/BuildInstructions.md)) depend on the seed data staying +exactly as it is. + +> **Student action required.** Read [AdvancedReports.md](AdvancedReports.md) end to end +> before the defense, and be ready to compute one period's realized P/L or one market's return +> by hand from the raw `transactions`/`market_trades` rows — the numbers in the verified run +> are real output, not invented, so they can be checked against +> [`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) directly. Append any further +> prompts here if you ask for revisions.