Changes between Version 1 and Version 2 of AdvancedReports


Ignore:
Timestamp:
09/24/26 15:21:25 (2 days ago)
Author:
231285
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v1 v2  
    1 == Advanced Reports
    2 
    3 Both reports are implemented as single SQL statements wrapped as callable SQL
    4 functions in [`schema_creation.sql`](../../server/db/schema_creation.sql):
    5 
    6 * `report_top_traders`
    7 * `report_market_performance`
    8 
    9 They are exposed directly in the prototype through menu options `[10]` and `[11]`
    10 in `server/reports.go`.
    11 
    12 == Report 1: Top traders by realized performance
    13 
    14 === Description
    15 
    16 The purpose of this report is to determine which users achieved the strongest
    17 trading performance over a selected time period.
    18 
    19 For a given interval `[from, to)`, the report calculates:
    20 
    21 * **Realized P/L** — the sum of `buy`, `sell` and `fee` transaction amounts.
    22   Deposits are excluded because they are not trading results.
    23 * **Total invested** — the absolute value of the user's buy amounts.
    24 * **ROI %** — realized P/L divided by total invested, multiplied by 100.
    25 * **Profitable periods** — number of calendar quarters with positive P/L.
    26 * **Losing periods** — number of calendar quarters with negative P/L.
    27 * **Total periods** — number of quarters containing any trading activity.
    28 * **Consistency %** — profitable quarters divided by total active quarters,
    29   multiplied by 100.
    30 
    31 Quarterly grouping is performed internally even when the requested date range
    32 covers several years. This makes it possible to distinguish a user with one
    33 large profitable period from a user whose results are consistently positive
    34 across several periods.
    35 
    36 The report is ordered by realized P/L in descending order.
    37 
    38 === SQL implementation
     1= Advanced Reports =
     2
     3This is a solo project (see [wiki:UseCaseModel]),
     4so the rubric's "2 per team member" is 2 reports total. Both are implemented as
     5single SQL statements, wrapped as callable SQL functions in
     6`schema_creation.sql` (`report_top_traders`,
     7`report_market_performance`) so they are actual reports inside the prototype — menu
     8options `[10]` and `[11]` in `server/reports.go` — not just documentation. No change to
     9[wiki:ERModel] or [wiki:RelationalDesign]
     10was needed: both reports read `transactions`, `market_trades` and `orders`, all of which
     11already carry everything required.
     12
     13=== Notation used below ===
     14
     15Both solutions need grouping, aggregation and computed attributes that plain relational
     16algebra has no notation for, so the relational-algebra sections use the standard ''extended''
     17operators:
     18
     19||= Symbol =||= Meaning =||
     20|| `σ_cond(R)` || selection ||
     21|| `π_list(R)` || projection — a list entry `expr → name` is a '''generalized projection''': a computed attribute, not just a column reference ||
     22|| `ρ_name(R)` || rename ||
     23|| `R ⋈_cond S` || inner join ||
     24|| `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) ||
     25|| `γ_{grouping; agg → name, …}(R)` || grouping/aggregation ||
     26|| `τ_attr(R)` || sort, for the presentation order only ||
     27
     28== Top traders by realized performance ==
     29
     30=== Data requirements description ===
     31
     32''"Which users actually made money, how much, how efficiently, and how consistently — over
     33a quarter, a year, or several years?"'' This is the natural crypto-exchange analogue of "which
     34customers bring the most profit" from the phase brief: a Trader's `available_balance` and
     35`invested_balance` (P1 `Users`) show a live snapshot, but they say nothing about performance
     36''over a chosen window'', and nothing at all about whether a user's results are one lucky
     37quarter or a repeatable pattern. All of it is derivable from
     38`transactions` (defined in `schema_creation.sql`) as it already exists: every buy, sell
     39and fee is one signed row there (see [wiki:UseCase0004] and
     40[wiki:UseCase0005] for how each row is produced), so no new
     41column or table is needed.
     42
     43The `transactions` table, from `schema_creation.sql`:
     44
     45{{{
     46CREATE TABLE project.transactions (
     47    id            uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     48    user_id       uuid           NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
     49    type          varchar(50)    NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
     50    amount        numeric(18,4)  NOT NULL,
     51    currency      char(3)        NOT NULL DEFAULT 'USD',
     52    related_order uuid           REFERENCES project.orders(id),
     53    created_at    timestamptz    NOT NULL DEFAULT now(),
     54    description   text
     55);
     56}}}
     57
     58Given a period `[from, to)`:
     59
     60 * '''Realized P/L''' = `SUM(amount)` over that user's `buy`, `sell` and `fee` transactions in
     61   the period (deposits excluded — they are not trading results).
     62 * '''Total invested''' = absolute value of the sum of that user's `buy` transactions in the
     63   period (buy amounts are stored negative, per
     64   [wiki:ERModel]).
     65 * '''ROI %''' = realized P/L ÷ total invested × 100.
     66 * The period is additionally bucketed into '''quarters''' internally, regardless of how wide
     67   `[from, to)` is, to measure:
     68   * '''Profitable / losing periods''' — how many quarters inside the window had positive vs.
     69     negative P/L.
     70   * '''Consistency %''' = profitable periods ÷ total periods with any activity × 100 — two
     71     users can have the same total P/L with very different risk profiles, and this is the
     72     number that tells them apart.
     73
     74=== Solution SQL ===
    3975
    4076Implemented as `project.report_top_traders(p_from, p_to)` in
    41 [`schema_creation.sql`](../../server/db/schema_creation.sql).
    42 
    43 ## [source,sql]
    44 
    45 CREATE OR REPLACE FUNCTION project.report_top_traders(
    46 p_from timestamptz,
    47 p_to   timestamptz
    48 )
     77`schema_creation.sql`:
     78
     79{{{
     80CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
    4981RETURNS TABLE (
    50 username            varchar,
    51 realized_pl         numeric,
    52 total_invested      numeric,
    53 roi_pct             numeric,
    54 profitable_periods  bigint,
    55 losing_periods      bigint,
    56 total_periods       bigint,
    57 consistency_pct     numeric
     82    username            varchar,
     83    realized_pl         numeric,
     84    total_invested      numeric,
     85    roi_pct             numeric,
     86    profitable_periods  bigint,
     87    losing_periods      bigint,
     88    total_periods       bigint,
     89    consistency_pct     numeric
    5890)
    5991LANGUAGE sql STABLE AS $$
    60 WITH period_pl AS (
    61 SELECT
    62 t.user_id,
    63 date_trunc('quarter', t.created_at) AS period,
    64 SUM(t.amount) AS period_pl,
    65 SUM(t.amount) FILTER (
    66 WHERE t.type = 'buy'
    67 ) AS period_buy
    68 FROM project.transactions t
    69 WHERE t.type IN ('buy', 'sell', 'fee')
    70 AND t.created_at >= p_from
    71 AND t.created_at <  p_to
    72 GROUP BY
    73 t.user_id,
    74 date_trunc('quarter', t.created_at)
    75 )
    76 SELECT
    77 u.username,
    78 
    79 ```
    80     SUM(pp.period_pl) AS realized_pl,
    81 
    82     ABS(SUM(pp.period_buy)) AS total_invested,
    83 
    84     ROUND(
    85         SUM(pp.period_pl)
    86         / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100,
    87         2
    88     ) AS roi_pct,
    89 
    90     COUNT(*) FILTER (
    91         WHERE pp.period_pl > 0
    92     ) AS profitable_periods,
    93 
    94     COUNT(*) FILTER (
    95         WHERE pp.period_pl < 0
    96     ) AS losing_periods,
    97 
    98     COUNT(*) AS total_periods,
    99 
    100     ROUND(
    101         COUNT(*) FILTER (
    102             WHERE pp.period_pl > 0
    103         )::numeric
    104         / NULLIF(COUNT(*), 0) * 100,
    105         2
    106     ) AS consistency_pct
    107 
    108 FROM period_pl pp
    109 JOIN project.users u
    110     ON u.id = pp.user_id
    111 
    112 GROUP BY
    113     u.id,
    114     u.username
    115 
    116 ORDER BY
    117     realized_pl DESC;
    118 ```
    119 
    120 $$$;
    121 ----
    122 
    123 === SQL logic
    124 
    125 The query consists of one CTE and one outer `SELECT`.
    126 
    127 The `period_pl` CTE first filters the transaction history to the requested
    128 interval and keeps only `buy`, `sell` and `fee` transactions. Transactions are
    129 then grouped by user and calendar quarter.
    130 
    131 For each quarter it calculates:
    132 
    133 - the total P/L for that quarter;
    134 - the total value of buy transactions.
    135 
    136 The outer query combines those quarterly results into user-level totals and
    137 derives ROI and consistency.
    138 
    139 `NULLIF` prevents division by zero for users who have no invested amount, while
    140 the `FILTER` clauses allow profitable and losing quarters to be counted
    141 independently.
    142 
    143 == Report 2: Market performance leaderboard
    144 
    145 === Description
    146 
    147 The purpose of this report is to compare the performance and activity of all
    148 markets over a selected time interval.
    149 
    150 For each market, the report calculates:
    151 
    152 - **Total volume** — sum of traded quantity.
    153 - **Trade count** — number of market trades.
    154 - **Average price** — average trade price.
    155 - **Market return %** — percentage change from the first trade price in the
    156   period to the last trade price.
    157 - **Price volatility** — sample standard deviation of trade prices.
    158 - **Participating users** — number of distinct users with executed orders on the
    159   market during the period.
    160 
    161 `market_trades` is used for volume, trade count, average price and price
    162 movement because it is the source of executed market activity.
    163 
    164 `orders` is used for participating users because `market_trades` intentionally
    165 does not contain a `user_id`; it records both user-generated fills and simulated
    166 market activity.
    167 
    168 The result is ordered by total traded volume in descending order.
    169 
    170 === SQL implementation
     92    WITH period_pl AS (
     93        SELECT
     94            t.user_id,
     95            date_trunc('quarter', t.created_at)          AS period,
     96            SUM(t.amount)                                AS period_pl,
     97            SUM(t.amount) FILTER (WHERE t.type = 'buy')  AS period_buy
     98        FROM project.transactions t
     99        WHERE t.type IN ('buy', 'sell', 'fee')
     100          AND t.created_at >= p_from
     101          AND t.created_at <  p_to
     102        GROUP BY t.user_id, date_trunc('quarter', t.created_at)
     103    )
     104    SELECT
     105        u.username,
     106        SUM(pp.period_pl)                                                       AS realized_pl,
     107        ABS(SUM(pp.period_buy))                                                 AS total_invested,
     108        ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2)  AS roi_pct,
     109        COUNT(*) FILTER (WHERE pp.period_pl > 0)                                AS profitable_periods,
     110        COUNT(*) FILTER (WHERE pp.period_pl < 0)                                AS losing_periods,
     111        COUNT(*)                                                                AS total_periods,
     112        ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
     113              / NULLIF(COUNT(*), 0) * 100, 2)                                   AS consistency_pct
     114    FROM period_pl pp
     115    JOIN project.users u ON u.id = pp.user_id
     116    GROUP BY u.id, u.username
     117    ORDER BY realized_pl DESC;
     118$$;
     119}}}
     120
     121One `SELECT`, one `WITH` CTE — the CTE does the quarter bucketing per user, the outer query
     122rolls those buckets up into the totals, the ROI/consistency percentages and the ranking.
     123
     124'''Verified run.''' `reports_demo_data.sql` adds five
     125quarters of round-trip trades (2025-07 through 2026-07) on top of the normal seed data
     126specifically so this report has more than one period to work with — see that file's header
     127(shown in full in the Demonstration data section below)
     128for exactly what it inserts and why it is optional rather than part of `-init`. Run against
     129PostgreSQL 16 with `data_load.sql` + `reports_demo_data.sql` loaded, through the actual CLI
     130(`[10] Report: top traders`, range `2025-01-01` to `2026-09-17`):
     131
     132{{{
     133  Username      Realized P/L        Invested       ROI %   Prof.    Loss   Total  Consist. %
     134  ------------------------------------------------------------------------------------------
     135  bob              +991.0000       6300.0000       15.73       3       0       3      100.00
     136  alice            -475.0000      24250.0000       -1.96       2       3       5       40.00
     137}}}
     138
     139Sorting by raw P/L alone would rank alice above bob if alice's numbers were all positive; here
     140it does the opposite, and that is the point of the report — alice traded a much larger total
     141(and one of her seeded round trips landed in the same quarter as the ETH buy already in
     142`data_load.sql`, tipping that quarter into a loss), while bob's three quarters were smaller
     143but every one of them profitable, giving him both the better ROI and a perfect consistency
     144score. A single "total profit" column would have hidden that difference completely.
     145
     146=== Solution Relational Algebra ===
     147
     148{{{
     149T_period  = σ_{type ∈ {buy,sell,fee} ∧ created_at ≥ from ∧ created_at < to} (Transactions)
     150
     151T_tagged  = π_{user_id, created_at, amount,
     152               (type = 'buy' ? amount : 0) → buy_amt} (T_period)
     153
     154Periods   = γ_{user_id, quarter(created_at) → period ;
     155               SUM(amount) → period_pl, SUM(buy_amt) → period_buy} (T_tagged)
     156
     157Totals      = γ_{user_id ; SUM(period_pl) → realized_pl,
     158                 ABS(SUM(period_buy)) → total_invested,
     159                 COUNT(*) → total_periods} (Periods)
     160Profitable  = γ_{user_id ; COUNT(*) → profitable_periods} (σ_{period_pl > 0} (Periods))
     161Losing      = γ_{user_id ; COUNT(*) → losing_periods}     (σ_{period_pl < 0} (Periods))
     162
     163Combined  = (Totals ⟕_{user_id} Profitable) ⟕_{user_id} Losing
     164
     165Ranked    = π_{user_id, realized_pl, total_invested,
     166               (realized_pl / total_invested × 100) → roi_pct,
     167               COALESCE(profitable_periods, 0) → profitable_periods,
     168               COALESCE(losing_periods, 0) → losing_periods,
     169               total_periods,
     170               (COALESCE(profitable_periods, 0) / total_periods × 100) → consistency_pct}
     171             (Combined)
     172
     173Result    = τ_{realized_pl ↓} (π_{username, realized_pl, total_invested, roi_pct,
     174               profitable_periods, losing_periods, total_periods, consistency_pct}
     175               (Ranked ⋈_{user_id = id} Users))
     176}}}
     177
     178`Totals`/`Profitable`/`Losing` are three separate groupings of the same `Periods` relation
     179because plain aggregation has no built-in "count only where X" operator; the two outer joins
     180recombine them (`⟕`, not `⋈`, because a user with zero losing quarters must still appear with
     181`losing_periods = 0`, not disappear from the result).
     182
     183== Market performance leaderboard ==
     184
     185=== Data requirements description ===
     186
     187''"Which markets were actually worth making — high volume, real price movement, real user
     188interest — over a chosen period?"'' This is the "products that bring the most profit" /
     189"good locations" family of question from the phase brief, translated to markets instead of
     190physical products: a market with heavy volume but a dead price, or a big price swing nobody
     191actually traded, are both misleading on their own; this report puts volume, trade count,
     192price return and user participation side by side so a market's performance over a
     193quarter/year/multi-year window can be judged as a whole, not from one number in isolation.
     194Everything needed already exists: `market_trades` is the single source of truth for price and
     195volume for every market ([wiki:PrototypeImplementation]),
     196and `orders` is the only place a specific user is tied to a specific market
     197([wiki:ERModel]) —
     198`market_trades` deliberately has no `user_id` column, since it also records the market
     199simulator's own fills.
     200
     201Given a period `[from, to)`, per market:
     202
     203 * '''Total volume''' = `SUM(quantity)` over its trades in the period.
     204 * '''Trade count''' = `COUNT(*)` over the same trades (real fills and simulated fills alike —
     205   this is activity, not just user activity).
     206 * '''Average trading price''' = `AVG(price)` over the same trades.
     207 * '''Market return %''' = `(last trade price − first trade price) ÷ first trade price × 100`,
     208   ordering trades by `executed_at` inside the period.
     209 * '''Participating users''' = `COUNT(DISTINCT user_id)` from that market's '''executed orders'''
     210   in the period — the only correct source, since `market_trades` cannot answer this question
     211   at all.
     212
     213=== Solution SQL ===
    171214
    172215Implemented as `project.report_market_performance(p_from, p_to)` in
    173 [`schema_creation.sql`](../../server/db/schema_creation.sql).
    174 
    175 [source,sql]
    176 ----
    177 CREATE OR REPLACE FUNCTION project.report_market_performance(
    178     p_from timestamptz,
    179     p_to   timestamptz
    180 )
     216`schema_creation.sql`:
     217
     218{{{
     219CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
    181220RETURNS TABLE (
    182221    symbol               varchar,
    … …  
    186225    avg_price            numeric,
    187226    market_return_pct    numeric,
    188     price_volatility     numeric,
    189227    participating_users  bigint
    190228)
    … …  
    192230    WITH trades AS (
    193231        SELECT
    194             market_id,
    195             price,
    196             quantity,
    197             executed_at,
    198 
     232            market_id, price, quantity, executed_at,
    199233            FIRST_VALUE(price) OVER w AS first_price,
    200 
    201             LAST_VALUE(price) OVER (
    202                 PARTITION BY market_id
    203                 ORDER BY executed_at
    204                 ROWS BETWEEN UNBOUNDED PRECEDING
    205                          AND UNBOUNDED FOLLOWING
    206             ) AS last_price
    207 
     234            LAST_VALUE(price)  OVER (PARTITION BY market_id ORDER BY executed_at
     235                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
    208236        FROM project.market_trades
    209 
    210         WHERE executed_at >= p_from
    211           AND executed_at <  p_to
    212 
    213         WINDOW w AS (
    214             PARTITION BY market_id
    215             ORDER BY executed_at
    216         )
     237        WHERE executed_at >= p_from AND executed_at < p_to
     238        WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
    217239    ),
    218 
    219240    market_stats AS (
    220241        SELECT
    221242            market_id,
    222             SUM(quantity) AS total_volume,
    223             COUNT(*) AS trade_count,
    224             AVG(price) AS avg_price,
    225             STDDEV(price) AS price_volatility,
     243            SUM(quantity)    AS total_volume,
     244            COUNT(*)         AS trade_count,
     245            AVG(price)       AS avg_price,
    226246            MAX(first_price) AS first_price,
    227             MAX(last_price) AS last_price
     247            MAX(last_price)  AS last_price
    228248        FROM trades
    229249        GROUP BY market_id
    230250    ),
    231 
    232251    participation AS (
    233         SELECT
    234             market_id,
    235             COUNT(DISTINCT user_id) AS participating_users
     252        SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
    236253        FROM project.orders
    237         WHERE status = 'executed'
    238           AND executed_at >= p_from
    239           AND executed_at <  p_to
     254        WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
    240255        GROUP BY market_id
    241256    )
    242 
    243257    SELECT
    244258        c.symbol,
    245259        m.quote_currency,
    246 
    247260        ms.total_volume,
    248261        ms.trade_count,
    249 
    250         ROUND(ms.avg_price, 6) AS avg_price,
    251 
    252         ROUND(
    253             (ms.last_price - ms.first_price)
    254             / NULLIF(ms.first_price, 0) * 100,
    255             2
    256         ) AS market_return_pct,
    257 
    258         ROUND(
    259             COALESCE(ms.price_volatility, 0),
    260             6
    261         ) AS price_volatility,
    262 
    263         COALESCE(
    264             p.participating_users,
    265             0
    266         ) AS participating_users
    267 
     262        ROUND(ms.avg_price, 6)                                                       AS avg_price,
     263        ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
     264        COALESCE(p.participating_users, 0)                                          AS participating_users
    268265    FROM market_stats ms
    269 
    270     JOIN project.markets m
    271         ON m.id = ms.market_id
    272 
    273     JOIN project.crypto c
    274         ON c.id = m.crypto_id
    275 
    276     LEFT JOIN participation p
    277         ON p.market_id = ms.market_id
    278 
    279     ORDER BY
    280         ms.total_volume DESC;
     266    JOIN project.markets m ON m.id = ms.market_id
     267    JOIN project.crypto  c ON c.id = m.crypto_id
     268    LEFT JOIN participation p ON p.market_id = ms.market_id
     269    ORDER BY ms.total_volume DESC;
    281270$$;
    282 ----
    283 
    284 === SQL logic
    285 
    286 The `trades` CTE restricts `market_trades` to the requested time interval and
    287 uses window functions to identify the first and last trade price for each
    288 market.
    289 
    290 The `market_stats` CTE aggregates those trades into one row per market and
    291 calculates volume, trade count, average price and price volatility.
    292 
    293 The `participation` CTE independently counts distinct users with executed
    294 orders. This must be calculated from `orders` because simulated
    295 `market_trades` do not belong to individual users.
    296 
    297 The final query joins the calculated statistics with `markets` and `crypto` to
    298 obtain the market symbol and quote currency.
    299 
    300 A `LEFT JOIN` is used for participation so that a market with trades but no
    301 executed user orders still appears in the report with `participating_users = 0`.
    302 
    303 == Implementation summary
    304 
    305 Both reports are implemented as callable SQL functions and therefore execute
    306 as part of the actual database-backed prototype rather than existing only as
    307 documentation.
    308 
    309 The two functions are:
    310 
    311 - `project.report_top_traders(p_from, p_to)`
    312 - `project.report_market_performance(p_from, p_to)`
    313 
    314 They are exposed through:
    315 
    316 - `[10] Report: top traders`
    317 - `[11] Report: market performance`
    318 
    319 No additional database objects or schema changes were necessary because all
    320 required information already exists in the relational model.
    321 $$$
     271}}}
     272
     273`FIRST_VALUE`/`LAST_VALUE` pick the period's opening and closing price per market without a
     274self-join; `LEFT JOIN participation` is required, not optional — a market can have trades
     275from the simulator alone and legitimately zero participating users, and it must still show
     276`0`, not disappear from the report.
     277
     278'''Verified run.''' Same seed as above (`data_load.sql` + `reports_demo_data.sql`, which also
     279adds a BTC/USD uptrend and an ETH/USD downtrend across the same five quarters — see that
     280file in the Demonstration data section below). Run through the CLI (`[11] Report: market performance`, `2025-01-01` to `2026-09-17`):
     281
     282{{{
     283  Symbol  Quote        Volume    Trades       Avg Price      Return %     Users
     284  -----------------------------------------------------------------------------
     285  DOGE    USD      29500.0000         3        0.120583         +2.95         0
     286  ADA     USD       2500.0000         3        0.450750         +1.62         0
     287  SOL     USD         23.5000         3      165.283333         +1.13         0
     288  ETH     USD         14.3500         8     3622.312500        -12.00         2
     289  BTC     USD          3.9750         9    59447.400000        +67.85         2
     290}}}
     291
     292BTC/USD and ETH/USD are the only two markets with historical (multi-quarter) data seeded, and
     293they show it: BTC's price nearly tripled over the period (`+67.85%`), while ETH quietly lost
     294`12%`. ADA/SOL/DOGE only have the few minutes of `data_load.sql`'s own recent seed trades, so
     295their return numbers reflect that narrow window, and their `0` participating users is correct
     296— `data_load.sql` seeds trade history for every market but only ever places an ''order'' on ETH.
     297
     298A price-volatility column (standard deviation of trade price) was dropped from this report
     299after review — with only a handful of trades per market in most periods it read as noise
     300rather than signal, and total volume plus return already carry the useful information.
     301
     302=== Solution Relational Algebra ===
     303
     304{{{
     305MT_period  = σ_{executed_at ≥ from ∧ executed_at < to} (MarketTrades)
     306
     307Bounds     = γ_{market_id ; MIN(executed_at) → t_first, MAX(executed_at) → t_last} (MT_period)
     308
     309FirstPx    = π_{market_id, price → first_price}
     310               (MT_period ⋈_{MT_period.market_id = Bounds.market_id
     311                              ∧ executed_at = t_first} Bounds)
     312LastPx     = π_{market_id, price → last_price}
     313               (MT_period ⋈_{MT_period.market_id = Bounds.market_id
     314                              ∧ executed_at = t_last} Bounds)
     315
     316Stats      = γ_{market_id ; SUM(quantity) → total_volume, COUNT(*) → trade_count,
     317                AVG(price) → avg_price} (MT_period)
     318
     319MarketStats = (Stats ⋈_{market_id} FirstPx) ⋈_{market_id} LastPx
     320
     321O_period      = σ_{status = 'executed' ∧ executed_at ≥ from ∧ executed_at < to} (Orders)
     322Participation = γ_{market_id ; COUNT_DISTINCT(user_id) → participating_users} (O_period)
     323
     324Joined = ((MarketStats ⟕_{market_id} Participation)
     325            ⋈_{market_id = id} Markets) ⋈_{crypto_id = id} Crypto
     326
     327Result = τ_{total_volume ↓} (
     328           π_{symbol, quote_currency, total_volume, trade_count, avg_price,
     329              (last_price − first_price) / first_price × 100 → market_return_pct,
     330              COALESCE(participating_users, 0) → participating_users}
     331             (Joined) )
     332}}}
     333
     334`FirstPx`/`LastPx` express `FIRST_VALUE`/`LAST_VALUE` — which have no classical relational-
     335algebra equivalent — as an aggregation for the boundary timestamp per market followed by a
     336self-join back to `MarketTrades` to recover the price at that timestamp; this is the standard
     337way to express "value at the extreme of a group" in extended relational algebra.
     338
     339== Demonstration data ==
     340
     341`reports_demo_data.sql`, the optional script both verified runs above were produced with:
     342
     343{{{
     344-- reports_demo_data.sql
     345-- EduBerza - optional historical data for the P6 reports
     346-- Course: Databases 2025/2026 Winter, FINKI UKIM
     347--
     348-- data_load.sql only seeds ~10 minutes of trade history, which is enough to
     349-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or
     350-- report_market_performance() doing anything interesting: everything falls
     351-- into a single quarter, so "number of profitable periods" and "consistency"
     352-- are trivial and "market return" has almost no history to work with.
     353--
     354-- This script adds five quarters of synthetic transactions, market trades and
     355-- executed orders on top of an already-loaded data_load.sql, spanning
     356-- 2025-07 to 2026-07, so the two P6 reports have several periods and two
     357-- markets with opposite price trends to actually compare.
     358--
     359-- Deliberately NOT part of -init / -load-data: it only inserts into
     360-- transactions, market_trades and orders, and does not touch
     361-- users.available_balance/invested_balance or holdings, so it does not
     362-- disturb the balances the other use cases' documented "verified run"
     363-- sections depend on. Run it by hand, after data_load.sql, only to exercise
     364-- the two reports:
     365--
     366--   psql "$DATABASE_URL" -f server/db/schema_creation.sql
     367--   psql "$DATABASE_URL" -f server/db/data_load.sql
     368--   psql "$DATABASE_URL" -f server/db/reports_demo_data.sql
     369--
     370-- Idempotent: deletes its own previously-inserted rows (tagged via
     371-- description/source) before re-inserting.
     372
     373SET search_path TO project, public;
     374
     375DELETE FROM transactions  WHERE description = 'P6 demo data';
     376DELETE FROM orders        WHERE id IN (
     377    'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
     378    'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
     379    'e5555555-5555-5555-5555-555555555555'
     380);
     381DELETE FROM market_trades WHERE source = 'p6_demo';
     382
     383-- ============================================================================
     384-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
     385-- ============================================================================
     386INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
     387    ('b1111111-1111-1111-1111-111111111111', 'buy',  -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
     388    ('b1111111-1111-1111-1111-111111111111', 'sell',  5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
     389    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
     390
     391    ('b1111111-1111-1111-1111-111111111111', 'buy',  -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
     392    ('b1111111-1111-1111-1111-111111111111', 'sell',  3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
     393    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
     394
     395    ('b1111111-1111-1111-1111-111111111111', 'buy',  -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
     396    ('b1111111-1111-1111-1111-111111111111', 'sell',  6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
     397    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
     398
     399    ('b1111111-1111-1111-1111-111111111111', 'buy',  -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
     400    ('b1111111-1111-1111-1111-111111111111', 'sell',  2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
     401    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
     402
     403    ('b1111111-1111-1111-1111-111111111111', 'buy',  -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
     404    ('b1111111-1111-1111-1111-111111111111', 'sell',  5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
     405    ('b1111111-1111-1111-1111-111111111111', 'fee',      -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');
     406
     407-- ============================================================================
     408-- Bob: three quarterly round trips, all profitable (100% consistency),
     409-- smaller total P/L than Alice but a higher ROI.
     410-- ============================================================================
     411INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
     412    ('b2222222-2222-2222-2222-222222222222', 'buy',  -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
     413    ('b2222222-2222-2222-2222-222222222222', 'sell',  2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
     414    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
     415
     416    ('b2222222-2222-2222-2222-222222222222', 'buy',  -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
     417    ('b2222222-2222-2222-2222-222222222222', 'sell',  2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
     418    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
     419
     420    ('b2222222-2222-2222-2222-222222222222', 'buy',  -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
     421    ('b2222222-2222-2222-2222-222222222222', 'sell',  2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
     422    ('b2222222-2222-2222-2222-222222222222', 'fee',      -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');
     423
     424-- ============================================================================
     425-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
     426-- source='p6_demo' keeps these separate from data_load.sql's own rows and
     427-- from live user/bot fills so this script can clean up after itself.
     428-- ============================================================================
     429INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
     430    ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy',  'p6_demo'),
     431    ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy',  'p6_demo'),
     432    ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy',  'p6_demo'),
     433    ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy',  'p6_demo'),
     434
     435    ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00',  4000.000000, 3.000000, 'sell', 'p6_demo'),
     436    ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00',  3800.000000, 2.500000, 'sell', 'p6_demo'),
     437    ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00',  3600.000000, 2.000000, 'sell', 'p6_demo'),
     438    ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00',  3550.000000, 1.800000, 'sell', 'p6_demo');
     439
     440-- ============================================================================
     441-- Executed orders: who participated in which market, across the same quarters.
     442-- ============================================================================
     443INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
     444    ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
     445     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
     446     '2025-07-15 10:00', '2025-07-15 10:00'),
     447    ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
     448     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
     449     '2025-10-15 10:00', '2025-10-15 10:00'),
     450    ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
     451     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
     452     '2026-01-15 10:00', '2026-01-15 10:00'),
     453    ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
     454     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
     455     '2026-04-15 10:00', '2026-04-15 10:00'),
     456    ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
     457     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
     458     '2026-01-15 10:00', '2026-01-15 10:00');
     459}}}
     460
     461== AI usage ==
     462
     463AI was used in this phase and is logged in full, per the course rule for P1 onward.
     464
     465 * '''Phase log:''' [wiki:AdvancedReportsAIUsage] — service used, what
     466   the AI produced, and what I decided myself.
     467
     468'''Service:''' Claude Code (Anthropic), `https://claude.com/claude-code` — Claude subscription,
     469model Claude Sonnet 5.
     470
     471'''In short:''' I specified both report questions in full — including the exact formulas for
     472P/L, ROI, consistency, market return, volatility and user participation — and asked the AI to
     473turn them into working SQL, wire them into the prototype as real reports, build the
     474relational-algebra equivalents, and produce demonstration data rich enough to show the
     475reports doing something non-trivial. In a follow-up, I asked for the price-volatility column
     476to be dropped from the market performance report — see the "Follow-up — 2026-09-17" section of
     477[wiki:AdvancedReportsAIUsage] for that change.