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