Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

09/17/26 00:24:27 (10 days ago)

Author:

231285

Comment:

--

AdvancedReports

@@ +1 @@
+== Advanced Reports
+
+Both reports 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`
+
+They are exposed directly in the prototype through menu options `[10]` and `[11]`
+in `server/reports.go`.
+
+== Report 1: Top traders by realized performance
+
+=== Description
+
+The purpose of this report is to determine which users achieved the strongest
+trading performance over a selected time period.
+
+For a given interval `[from, to)`, the report calculates:
+
+* **Realized P/L** — the sum of `buy`, `sell` and `fee` transaction amounts.
+  Deposits are excluded because they are not trading results.
+* **Total invested** — the absolute value of the user's buy amounts.
+* **ROI %** — realized P/L divided by total invested, multiplied by 100.
+* **Profitable periods** — number of calendar quarters with positive P/L.
+* **Losing periods** — number of calendar quarters with negative P/L.
+* **Total periods** — number of quarters containing any trading activity.
+* **Consistency %** — profitable quarters divided by total active quarters,
+  multiplied by 100.
+
+Quarterly grouping is performed internally even when the requested date range
+covers several years. This makes it possible to distinguish a user with one
+large profitable period from a user whose results are consistently positive
+across several periods.
+
+The report is ordered by realized P/L in descending order.
+
+=== SQL implementation
+
+Implemented as `project.report_top_traders(p_from, p_to)` in
+[`schema_creation.sql`](../../server/db/schema_creation.sql).
+
+## [source,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;
+```
+
+$$$;
+----
+
+=== SQL logic
+
+The query consists of one CTE and one outer `SELECT`.
+
+The `period_pl` CTE first filters the transaction history to the requested
+interval and keeps only `buy`, `sell` and `fee` transactions. Transactions are
+then grouped by user and calendar quarter.
+
+For each quarter it calculates:
+
+- the total P/L for that quarter;
+- the total value of buy transactions.
+
+The outer query combines those quarterly results into user-level totals and
+derives ROI and consistency.
+
+`NULLIF` prevents division by zero for users who have no invested amount, while
+the `FILTER` clauses allow profitable and losing quarters to be counted
+independently.
+
+== Report 2: Market performance leaderboard
+
+=== Description
+
+The purpose of this report is to compare the performance and activity of all
+markets over a selected time interval.
+
+For each market, the report calculates:
+
+- **Total volume** — sum of traded quantity.
+- **Trade count** — number of market trades.
+- **Average price** — average trade price.
+- **Market return %** — percentage change from the first trade price in the
+  period to the last trade price.
+- **Price volatility** — sample standard deviation of trade prices.
+- **Participating users** — number of distinct users with executed orders on the
+  market during the period.
+
+`market_trades` is used for volume, trade count, average price and price
+movement because it is the source of executed market activity.
+
+`orders` is used for participating users because `market_trades` intentionally
+does not contain a `user_id`; it records both user-generated fills and simulated
+market activity.
+
+The result is ordered by total traded volume in descending order.
+
+=== SQL implementation
+
+Implemented as `project.report_market_performance(p_from, p_to)` in
+[`schema_creation.sql`](../../server/db/schema_creation.sql).
+
+[source,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;
+$$;
+----
+
+=== SQL logic
+
+The `trades` CTE restricts `market_trades` to the requested time interval and
+uses window functions to identify the first and last trade price for each
+market.
+
+The `market_stats` CTE aggregates those trades into one row per market and
+calculates volume, trade count, average price and price volatility.
+
+The `participation` CTE independently counts distinct users with executed
+orders. This must be calculated from `orders` because simulated
+`market_trades` do not belong to individual users.
+
+The final query joins the calculated statistics with `markets` and `crypto` to
+obtain the market symbol and quote currency.
+
+A `LEFT JOIN` is used for participation so that a market with trades but no
+executed user orders still appears in the report with `participating_users = 0`.
+
+== Implementation summary
+
+Both reports are implemented as callable SQL functions and therefore execute
+as part of the actual database-backed prototype rather than existing only as
+documentation.
+
+The two functions are:
+
+- `project.report_top_traders(p_from, p_to)`
+- `project.report_market_performance(p_from, p_to)`
+
+They are exposed through:
+
+- `[10] Report: top traders`
+- `[11] Report: market performance`
+
+No additional database objects or schema changes were necessary because all
+required information already exists in the relational model.
+$$$