== 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.
$$$