Index: docs/P6-AdvancedReports/AdvancedReports.md =================================================================== --- docs/P6-AdvancedReports/AdvancedReports.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ docs/P6-AdvancedReports/AdvancedReports.md (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) @@ -175,5 +175,5 @@ 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 +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 @@ -192,5 +192,4 @@ - **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 @@ -211,5 +210,4 @@ avg_price numeric, market_return_pct numeric, - price_volatility numeric, participating_users bigint ) @@ -231,5 +229,4 @@ 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 @@ -250,5 +247,4 @@ 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 @@ -270,19 +266,22 @@ ``` - 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 + 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%`) 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. +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 @@ -301,5 +300,5 @@ Stats = γ_{market_id ; SUM(quantity) → total_volume, COUNT(*) → trade_count, - AVG(price) → avg_price, STDDEV(price) → price_volatility} (MT_period) + AVG(price) → avg_price} (MT_period) MarketStats = (Stats ⋈_{market_id} FirstPx) ⋈_{market_id} LastPx @@ -314,5 +313,4 @@ π_{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) ) @@ -338,3 +336,5 @@ 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. +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. Index: docs/P6-AdvancedReports/AdvancedReportsAIUsage.md =================================================================== --- docs/P6-AdvancedReports/AdvancedReportsAIUsage.md (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ docs/P6-AdvancedReports/AdvancedReportsAIUsage.md (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) @@ -126,2 +126,32 @@ > [`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) directly. Append any further > prompts here if you ask for revisions. + +### Follow-up — 2026-09-17 + +**Intent:** simplify the market performance report after seeing it run — with only a handful +of trades per market in most periods, price volatility read as noise rather than a useful +signal. + +**Prompt (student, verbatim):** +> Can you tell me the exact database_url so I can execute the schema creating and the data_load? +> +> From the Market Performance can we remove Volatility, I don't think that is that useful, also +> can you tell me what is users column? + +**Response (AI, summarised):** +- Gave the local dev connection string (`postgresql://bp_project:1234@localhost:5433/bp_database`, + from `.env`) and explained `participating_users` is `COUNT(DISTINCT user_id)` from that + market's executed orders, not from `market_trades` (which has no `user_id`). +- Removed `price_volatility`/`STDDEV(price)` from `report_market_performance()` in + `schema_creation.sql`, from its `RETURNS TABLE` and its `SELECT`, and from the corresponding + column in `server/reports.go`'s `ShowMarketPerformance`. +- Updated the relational algebra (`Stats`, `Result`) and the SQL listing, verified run output, + and prose in [AdvancedReports.md](AdvancedReports.md) to match, and added a short note + explaining why the column was dropped. +- Rebuilt, re-ran `-init` and `reports_demo_data.sql` against the live database, and + re-verified the market performance report through the actual CLI before and after the + change to confirm only the one column disappeared and every other number is unchanged. + +**What I decided:** to drop the column entirely rather than keep it computed-but-hidden, +since an unused computation left in the query is exactly the kind of thing that should not +survive a review. Index: server/db/schema_creation.sql =================================================================== --- server/db/schema_creation.sql (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ server/db/schema_creation.sql (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) @@ -261,5 +261,4 @@ avg_price numeric, market_return_pct numeric, - price_volatility numeric, participating_users bigint ) @@ -281,5 +280,4 @@ 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 @@ -300,5 +298,4 @@ 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 Index: server/reports.go =================================================================== --- server/reports.go (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ server/reports.go (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) @@ -81,6 +81,6 @@ defer rows.Close() - header := fmt.Sprintf(" %-6s %-5s %12s %8s %14s %12s %14s %8s", - "Symbol", "Quote", "Volume", "Trades", "Avg Price", "Return %", "Volatility", "Users") + header := fmt.Sprintf(" %-6s %-5s %12s %8s %14s %12s %8s", + "Symbol", "Quote", "Volume", "Trades", "Avg Price", "Return %", "Users") fmt.Println() fmt.Println(header) @@ -90,12 +90,12 @@ for rows.Next() { var symbol, quote string - var volume, avgPrice, returnPct, volatility float64 + var volume, avgPrice, returnPct float64 var tradeCount, users int64 - if err := rows.Scan(&symbol, "e, &volume, &tradeCount, &avgPrice, &returnPct, &volatility, &users); err != nil { + if err := rows.Scan(&symbol, "e, &volume, &tradeCount, &avgPrice, &returnPct, &users); err != nil { fmt.Println("scan error:", err) return } - fmt.Printf(" %-6s %-5s %12.4f %8d %14.6f %+12.2f %14.6f %8d\n", - symbol, quote, volume, tradeCount, avgPrice, returnPct, volatility, users) + fmt.Printf(" %-6s %-5s %12.4f %8d %14.6f %+12.2f %8d\n", + symbol, quote, volume, tradeCount, avgPrice, returnPct, users) empty = false }