Changeset 9e6d8a2


Ignore:
Timestamp:
09/17/26 00:36:39 (13 days ago)
Author:
Stefan <trsunovstefan@…>
Branches:
main
Children:
4438e45
Parents:
35bcb41
Message:

Remove volatility from Phase 6

Files:
5 edited

Legend:

Unmodified
Added
Removed

  • docs/P6-AdvancedReports/AdvancedReports.md

    r35bcb41 r9e6d8a2  
    175175physical products: a market with heavy volume but a dead price, or a big price swing nobody
    176176actually traded, are both misleading on their own; this report puts volume, trade count,
    177 price return, volatility and user participation side by side so a market's performance over a
     177price return and user participation side by side so a market's performance over a
    178178quarter/year/multi-year window can be judged as a whole, not from one number in isolation.
    179179Everything needed already exists: `market_trades` is the single source of truth for price and
    … …  
    192192- **Market return %** = `(last trade price − first trade price) ÷ first trade price × 100`,
    193193  ordering trades by `executed_at` inside the period.
    194 - **Price volatility** = the (sample) standard deviation of trade prices in the period.
    195194- **Participating users** = `COUNT(DISTINCT user_id)` from that market's **executed orders**
    196195  in the period — the only correct source, since `market_trades` cannot answer this question
    … …  
    211210    avg_price            numeric,
    212211    market_return_pct    numeric,
    213     price_volatility     numeric,
    214212    participating_users  bigint
    215213)
    … …  
    231229            COUNT(*)         AS trade_count,
    232230            AVG(price)       AS avg_price,
    233             STDDEV(price)    AS price_volatility,
    234231            MAX(first_price) AS first_price,
    235232            MAX(last_price)  AS last_price
    … …  
    250247        ROUND(ms.avg_price, 6)                                                       AS avg_price,
    251248        ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
    252         ROUND(COALESCE(ms.price_volatility, 0), 6)                                   AS price_volatility,
    253249        COALESCE(p.participating_users, 0)                                          AS participating_users
    254250    FROM market_stats ms
    … …  
    270266
    271267```
    272   Symbol  Quote        Volume    Trades       Avg Price      Return %      Volatility     Users
    273   ---------------------------------------------------------------------------------------------
    274   DOGE    USD      29500.0000         3        0.120583         +2.95        0.001843        0
    275   ADA     USD       2500.0000         3        0.450750         +1.62        0.003783        0
    276   SOL     USD         23.5000         3      165.283333         +1.13        0.943840        0
    277   ETH     USD         14.3500         8     3622.312500        -12.00      182.657992         2
    278   BTC     USD          3.9750         9    59447.400000        +67.85    10563.413339         2
     268  Symbol  Quote        Volume    Trades       Avg Price      Return %     Users
     269  -----------------------------------------------------------------------------
     270  DOGE    USD      29500.0000         3        0.120583         +2.95        0
     271  ADA     USD       2500.0000         3        0.450750         +1.62        0
     272  SOL     USD         23.5000         3      165.283333         +1.13        0
     273  ETH     USD         14.3500         8     3622.312500        -12.00         2
     274  BTC     USD          3.9750         9    59447.400000        +67.85         2
    279275```
    280276
    281277BTC/USD and ETH/USD are the only two markets with historical (multi-quarter) data seeded, and
    282 they show it: BTC's price nearly tripled over the period (`+67.85%`) with by far the highest
    283 volatility, while ETH quietly lost `12%`. ADA/SOL/DOGE only have the few minutes of
    284 `data_load.sql`'s own recent seed trades, so their return/volatility numbers reflect that
    285 narrow window, and their `0` participating users is correct — `data_load.sql` seeds trade
    286 history for every market but only ever places an *order* on ETH.
     278they show it: BTC's price nearly tripled over the period (`+67.85%`), while ETH quietly lost
     279`12%`. ADA/SOL/DOGE only have the few minutes of `data_load.sql`'s own recent seed trades, so
     280their return numbers reflect that narrow window, and their `0` participating users is correct
     281— `data_load.sql` seeds trade history for every market but only ever places an *order* on ETH.
     282
     283A price-volatility column (standard deviation of trade price) was dropped from this report
     284after review — with only a handful of trades per market in most periods it read as noise
     285rather than signal, and total volume plus return already carry the useful information.
    287286
    288287### Solution Relational Algebra
    … …  
    301300
    302301Stats      = γ_{market_id ; SUM(quantity) → total_volume, COUNT(*) → trade_count,
    303                 AVG(price) → avg_price, STDDEV(price) → price_volatility} (MT_period)
     302                AVG(price) → avg_price} (MT_period)
    304303
    305304MarketStats = (Stats ⋈_{market_id} FirstPx) ⋈_{market_id} LastPx
    … …  
    314313           π_{symbol, quote_currency, total_volume, trade_count, avg_price,
    315314              (last_price − first_price) / first_price × 100 → market_return_pct,
    316               COALESCE(price_volatility, 0) → price_volatility,
    317315              COALESCE(participating_users, 0) → participating_users}
    318316             (Joined) )
    … …  
    338336turn them into working SQL, wire them into the prototype as real reports, build the
    339337relational-algebra equivalents, and produce demonstration data rich enough to show the
    340 reports doing something non-trivial.
     338reports doing something non-trivial. In a follow-up, I asked for the price-volatility column
     339to be dropped from the market performance report — see
     340[AdvancedReportsAIUsage](AdvancedReportsAIUsage.md#follow-up--2026-09-17) for that change.

  • docs/P6-AdvancedReports/AdvancedReportsAIUsage.md

    r35bcb41 r9e6d8a2  
    126126> [`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) directly. Append any further
    127127> prompts here if you ask for revisions.
     128
     129### Follow-up — 2026-09-17
     130
     131**Intent:** simplify the market performance report after seeing it run — with only a handful
     132of trades per market in most periods, price volatility read as noise rather than a useful
     133signal.
     134
     135**Prompt (student, verbatim):**
     136> Can you tell me the exact database_url so I can execute the schema creating and the data_load?
     137>
     138> From the Market Performance can we remove Volatility, I don't think that is that useful, also
     139> can you tell me what is users column?
     140
     141**Response (AI, summarised):**
     142- Gave the local dev connection string (`postgresql://bp_project:1234@localhost:5433/bp_database`,
     143  from `.env`) and explained `participating_users` is `COUNT(DISTINCT user_id)` from that
     144  market's executed orders, not from `market_trades` (which has no `user_id`).
     145- Removed `price_volatility`/`STDDEV(price)` from `report_market_performance()` in
     146  `schema_creation.sql`, from its `RETURNS TABLE` and its `SELECT`, and from the corresponding
     147  column in `server/reports.go`'s `ShowMarketPerformance`.
     148- Updated the relational algebra (`Stats`, `Result`) and the SQL listing, verified run output,
     149  and prose in [AdvancedReports.md](AdvancedReports.md) to match, and added a short note
     150  explaining why the column was dropped.
     151- Rebuilt, re-ran `-init` and `reports_demo_data.sql` against the live database, and
     152  re-verified the market performance report through the actual CLI before and after the
     153  change to confirm only the one column disappeared and every other number is unchanged.
     154
     155**What I decided:** to drop the column entirely rather than keep it computed-but-hidden,
     156since an unused computation left in the query is exactly the kind of thing that should not
     157survive a review.

  • server/db/schema_creation.sql

    r35bcb41 r9e6d8a2  
    261261    avg_price            numeric,
    262262    market_return_pct    numeric,
    263     price_volatility     numeric,
    264263    participating_users  bigint
    265264)
    … …  
    281280            COUNT(*)         AS trade_count,
    282281            AVG(price)       AS avg_price,
    283             STDDEV(price)    AS price_volatility,
    284282            MAX(first_price) AS first_price,
    285283            MAX(last_price)  AS last_price
    … …  
    300298        ROUND(ms.avg_price, 6)                                                            AS avg_price,
    301299        ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2)      AS market_return_pct,
    302         ROUND(COALESCE(ms.price_volatility, 0), 6)                                        AS price_volatility,
    303300        COALESCE(p.participating_users, 0)                                                AS participating_users
    304301    FROM market_stats ms

  • server/reports.go

    r35bcb41 r9e6d8a2  
    8181        defer rows.Close()
    8282
    83         header := fmt.Sprintf("  %-6s  %-5s  %12s  %8s  %14s  %12s  %14s  %8s",
    84                 "Symbol", "Quote", "Volume", "Trades", "Avg Price", "Return %", "Volatility", "Users")
     83        header := fmt.Sprintf("  %-6s  %-5s  %12s  %8s  %14s  %12s  %8s",
     84                "Symbol", "Quote", "Volume", "Trades", "Avg Price", "Return %", "Users")
    8585        fmt.Println()
    8686        fmt.Println(header)
    … …  
    9090        for rows.Next() {
    9191                var symbol, quote string
    92                 var volume, avgPrice, returnPct, volatility float64
     92                var volume, avgPrice, returnPct float64
    9393                var tradeCount, users int64
    94                 if err := rows.Scan(&symbol, &quote, &volume, &tradeCount, &avgPrice, &returnPct, &volatility, &users); err != nil {
     94                if err := rows.Scan(&symbol, &quote, &volume, &tradeCount, &avgPrice, &returnPct, &users); err != nil {
    9595                        fmt.Println("scan error:", err)
    9696                        return
    9797                }
    98                 fmt.Printf("  %-6s  %-5s  %12.4f  %8d  %14.6f  %+12.2f  %14.6f  %8d\n",
    99                         symbol, quote, volume, tradeCount, avgPrice, returnPct, volatility, users)
     98                fmt.Printf("  %-6s  %-5s  %12.4f  %8d  %14.6f  %+12.2f  %8d\n",
     99                        symbol, quote, volume, tradeCount, avgPrice, returnPct, users)
    100100                empty = false
    101101        }
Note: See TracChangeset for help on using the changeset viewer.