Advanced Reports
This is a solo project (see UseCaseModel),
so the rubric's "2 per team member" is 2 reports total. Both are implemented as
single SQL statements, wrapped as callable SQL functions in
schema_creation.sql (report_top_traders,
report_market_performance) so they are actual reports inside the prototype — menu
options [10] and [11] in server/reports.go — not just documentation. No change to
ERModel or RelationalDesign
was needed: both reports read transactions, market_trades and orders, all of which
already carry everything required.
Notation used below
Both solutions need grouping, aggregation and computed attributes that plain relational algebra has no notation for, so the relational-algebra sections use the standard extended operators:
| Symbol | Meaning |
|---|---|
σ_cond(R) | selection |
π_list(R) | projection — a list entry expr → name is a generalized projection: a computed attribute, not just a column reference
|
ρ_name(R) | rename |
R ⋈_cond S | inner join |
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) |
γ_{grouping; agg → name, …}(R) | grouping/aggregation |
τ_attr(R) | sort, for the presentation order only |
Top traders by realized performance
Data requirements description
"Which users actually made money, how much, how efficiently, and how consistently — over
a quarter, a year, or several years?" This is the natural crypto-exchange analogue of "which
customers bring the most profit" from the phase brief: a Trader's available_balance and
invested_balance (P1 Users) show a live snapshot, but they say nothing about performance
over a chosen window, and nothing at all about whether a user's results are one lucky
quarter or a repeatable pattern. All of it is derivable from
transactions (defined in schema_creation.sql) as it already exists: every buy, sell
and fee is one signed row there (see UseCase0004 and
UseCase0005 for how each row is produced), so no new
column or table is needed.
The transactions table, from schema_creation.sql:
CREATE TABLE project.transactions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
amount numeric(18,4) NOT NULL,
currency char(3) NOT NULL DEFAULT 'USD',
related_order uuid REFERENCES project.orders(id),
created_at timestamptz NOT NULL DEFAULT now(),
description text
);
Given a period [from, to):
- Realized P/L =
SUM(amount)over that user'sbuy,sellandfeetransactions in the period (deposits excluded — they are not trading results). - Total invested = absolute value of the sum of that user's
buytransactions in the period (buy amounts are stored negative, per ERModel). - ROI % = realized P/L ÷ total invested × 100.
- The period is additionally bucketed into quarters internally, regardless of how wide
[from, to)is, to measure:- Profitable / losing periods — how many quarters inside the window had positive vs. negative P/L.
- Consistency % = profitable periods ÷ total periods with any activity × 100 — two users can have the same total P/L with very different risk profiles, and this is the number that tells them apart.
Solution SQL
Implemented as project.report_top_traders(p_from, p_to) in
schema_creation.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;
$$;
One SELECT, one WITH CTE — the CTE does the quarter bucketing per user, the outer query
rolls those buckets up into the totals, the ROI/consistency percentages and the ranking.
Verified run. reports_demo_data.sql adds five
quarters of round-trip trades (2025-07 through 2026-07) on top of the normal seed data
specifically so this report has more than one period to work with — see that file's header
(shown in full in the Demonstration data section below)
for exactly what it inserts and why it is optional rather than part of -init. Run against
PostgreSQL 16 with data_load.sql + reports_demo_data.sql loaded, through the actual CLI
([10] Report: top traders, range 2025-01-01 to 2026-09-17):
Username Realized P/L Invested ROI % Prof. Loss Total Consist. % ------------------------------------------------------------------------------------------ bob +991.0000 6300.0000 15.73 3 0 3 100.00 alice -475.0000 24250.0000 -1.96 2 3 5 40.00
Sorting by raw P/L alone would rank alice above bob if alice's numbers were all positive; here
it does the opposite, and that is the point of the report — alice traded a much larger total
(and one of her seeded round trips landed in the same quarter as the ETH buy already in
data_load.sql, tipping that quarter into a loss), while bob's three quarters were smaller
but every one of them profitable, giving him both the better ROI and a perfect consistency
score. A single "total profit" column would have hidden that difference completely.
Solution Relational Algebra
T_period = σ_{type ∈ {buy,sell,fee} ∧ created_at ≥ from ∧ created_at < to} (Transactions)
T_tagged = π_{user_id, created_at, amount,
(type = 'buy' ? amount : 0) → buy_amt} (T_period)
Periods = γ_{user_id, quarter(created_at) → period ;
SUM(amount) → period_pl, SUM(buy_amt) → period_buy} (T_tagged)
Totals = γ_{user_id ; SUM(period_pl) → realized_pl,
ABS(SUM(period_buy)) → total_invested,
COUNT(*) → total_periods} (Periods)
Profitable = γ_{user_id ; COUNT(*) → profitable_periods} (σ_{period_pl > 0} (Periods))
Losing = γ_{user_id ; COUNT(*) → losing_periods} (σ_{period_pl < 0} (Periods))
Combined = (Totals ⟕_{user_id} Profitable) ⟕_{user_id} Losing
Ranked = π_{user_id, realized_pl, total_invested,
(realized_pl / total_invested × 100) → roi_pct,
COALESCE(profitable_periods, 0) → profitable_periods,
COALESCE(losing_periods, 0) → losing_periods,
total_periods,
(COALESCE(profitable_periods, 0) / total_periods × 100) → consistency_pct}
(Combined)
Result = τ_{realized_pl ↓} (π_{username, realized_pl, total_invested, roi_pct,
profitable_periods, losing_periods, total_periods, consistency_pct}
(Ranked ⋈_{user_id = id} Users))
Totals/Profitable/Losing are three separate groupings of the same Periods relation
because plain aggregation has no built-in "count only where X" operator; the two outer joins
recombine them (⟕, not ⋈, because a user with zero losing quarters must still appear with
losing_periods = 0, not disappear from the result).
Market performance leaderboard
Data requirements description
"Which markets were actually worth making — high volume, real price movement, real user
interest — over a chosen period?" This is the "products that bring the most profit" /
"good locations" family of question from the phase brief, translated to markets instead of
physical products: a market with heavy volume but a dead price, or a big price swing nobody
actually traded, are both misleading on their own; this report puts volume, trade count,
price return and user participation side by side so a market's performance over a
quarter/year/multi-year window can be judged as a whole, not from one number in isolation.
Everything needed already exists: market_trades is the single source of truth for price and
volume for every market (PrototypeImplementation),
and orders is the only place a specific user is tied to a specific market
(ERModel) —
market_trades deliberately has no user_id column, since it also records the market
simulator's own fills.
Given a period [from, to), per market:
- Total volume =
SUM(quantity)over its trades in the period. - Trade count =
COUNT(*)over the same trades (real fills and simulated fills alike — this is activity, not just user activity). - Average trading price =
AVG(price)over the same trades. - Market return % =
(last trade price − first trade price) ÷ first trade price × 100, ordering trades byexecuted_atinside the period. - Participating users =
COUNT(DISTINCT user_id)from that market's executed orders in the period — the only correct source, sincemarket_tradescannot answer this question at all.
Solution SQL
Implemented as project.report_market_performance(p_from, p_to) in
schema_creation.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,
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,
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,
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;
$$;
FIRST_VALUE/LAST_VALUE pick the period's opening and closing price per market without a
self-join; LEFT JOIN participation is required, not optional — a market can have trades
from the simulator alone and legitimately zero participating users, and it must still show
0, not disappear from the report.
Verified run. Same seed as above (data_load.sql + reports_demo_data.sql, which also
adds a BTC/USD uptrend and an ETH/USD downtrend across the same five quarters — see that
file in the Demonstration data section below). Run through the CLI ([11] Report: market performance, 2025-01-01 to 2026-09-17):
Symbol Quote Volume Trades Avg Price Return % Users ----------------------------------------------------------------------------- DOGE USD 29500.0000 3 0.120583 +2.95 0 ADA USD 2500.0000 3 0.450750 +1.62 0 SOL USD 23.5000 3 165.283333 +1.13 0 ETH USD 14.3500 8 3622.312500 -12.00 2 BTC USD 3.9750 9 59447.400000 +67.85 2
BTC/USD and ETH/USD are the only two markets with historical (multi-quarter) data seeded, and
they show it: BTC's price nearly tripled over the period (+67.85%), while ETH quietly lost
12%. ADA/SOL/DOGE only have the few minutes of data_load.sql's own recent seed trades, so
their return numbers reflect that narrow window, and their 0 participating users is correct
— data_load.sql seeds trade history for every market but only ever places an order on ETH.
A price-volatility column (standard deviation of trade price) was dropped from this report after review — with only a handful of trades per market in most periods it read as noise rather than signal, and total volume plus return already carry the useful information.
Solution Relational Algebra
MT_period = σ_{executed_at ≥ from ∧ executed_at < to} (MarketTrades)
Bounds = γ_{market_id ; MIN(executed_at) → t_first, MAX(executed_at) → t_last} (MT_period)
FirstPx = π_{market_id, price → first_price}
(MT_period ⋈_{MT_period.market_id = Bounds.market_id
∧ executed_at = t_first} Bounds)
LastPx = π_{market_id, price → last_price}
(MT_period ⋈_{MT_period.market_id = Bounds.market_id
∧ executed_at = t_last} Bounds)
Stats = γ_{market_id ; SUM(quantity) → total_volume, COUNT(*) → trade_count,
AVG(price) → avg_price} (MT_period)
MarketStats = (Stats ⋈_{market_id} FirstPx) ⋈_{market_id} LastPx
O_period = σ_{status = 'executed' ∧ executed_at ≥ from ∧ executed_at < to} (Orders)
Participation = γ_{market_id ; COUNT_DISTINCT(user_id) → participating_users} (O_period)
Joined = ((MarketStats ⟕_{market_id} Participation)
⋈_{market_id = id} Markets) ⋈_{crypto_id = id} Crypto
Result = τ_{total_volume ↓} (
π_{symbol, quote_currency, total_volume, trade_count, avg_price,
(last_price − first_price) / first_price × 100 → market_return_pct,
COALESCE(participating_users, 0) → participating_users}
(Joined) )
FirstPx/LastPx express FIRST_VALUE/LAST_VALUE — which have no classical relational-
algebra equivalent — as an aggregation for the boundary timestamp per market followed by a
self-join back to MarketTrades to recover the price at that timestamp; this is the standard
way to express "value at the extreme of a group" in extended relational algebra.
Demonstration data
reports_demo_data.sql, the optional script both verified runs above were produced with:
-- reports_demo_data.sql
-- EduBerza - optional historical data for the P6 reports
-- Course: Databases 2025/2026 Winter, FINKI UKIM
--
-- data_load.sql only seeds ~10 minutes of trade history, which is enough to
-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or
-- report_market_performance() doing anything interesting: everything falls
-- into a single quarter, so "number of profitable periods" and "consistency"
-- are trivial and "market return" has almost no history to work with.
--
-- This script adds five quarters of synthetic transactions, market trades and
-- executed orders on top of an already-loaded data_load.sql, spanning
-- 2025-07 to 2026-07, so the two P6 reports have several periods and two
-- markets with opposite price trends to actually compare.
--
-- Deliberately NOT part of -init / -load-data: it only inserts into
-- transactions, market_trades and orders, and does not touch
-- users.available_balance/invested_balance or holdings, so it does not
-- disturb the balances the other use cases' documented "verified run"
-- sections depend on. Run it by hand, after data_load.sql, only to exercise
-- the two reports:
--
-- psql "$DATABASE_URL" -f server/db/schema_creation.sql
-- psql "$DATABASE_URL" -f server/db/data_load.sql
-- psql "$DATABASE_URL" -f server/db/reports_demo_data.sql
--
-- Idempotent: deletes its own previously-inserted rows (tagged via
-- description/source) before re-inserting.
SET search_path TO project, public;
DELETE FROM transactions WHERE description = 'P6 demo data';
DELETE FROM orders WHERE id IN (
'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
'e5555555-5555-5555-5555-555555555555'
);
DELETE FROM market_trades WHERE source = 'p6_demo';
-- ============================================================================
-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
-- ============================================================================
INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
('b1111111-1111-1111-1111-111111111111', 'buy', -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'sell', 5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'buy', -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'sell', 3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'buy', -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'sell', 6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'buy', -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'sell', 2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'buy', -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'sell', 5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');
-- ============================================================================
-- Bob: three quarterly round trips, all profitable (100% consistency),
-- smaller total P/L than Alice but a higher ROI.
-- ============================================================================
INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
('b2222222-2222-2222-2222-222222222222', 'buy', -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'sell', 2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'buy', -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'sell', 2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'buy', -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'sell', 2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');
-- ============================================================================
-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
-- source='p6_demo' keeps these separate from data_load.sql's own rows and
-- from live user/bot fills so this script can clean up after itself.
-- ============================================================================
INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy', 'p6_demo'),
('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy', 'p6_demo'),
('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy', 'p6_demo'),
('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy', 'p6_demo'),
('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00', 4000.000000, 3.000000, 'sell', 'p6_demo'),
('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00', 3800.000000, 2.500000, 'sell', 'p6_demo'),
('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00', 3600.000000, 2.000000, 'sell', 'p6_demo'),
('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00', 3550.000000, 1.800000, 'sell', 'p6_demo');
-- ============================================================================
-- Executed orders: who participated in which market, across the same quarters.
-- ============================================================================
INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
'2025-07-15 10:00', '2025-07-15 10:00'),
('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
'2025-10-15 10:00', '2025-10-15 10:00'),
('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
'2026-01-15 10:00', '2026-01-15 10:00'),
('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
'2026-04-15 10:00', '2026-04-15 10:00'),
('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
'2026-01-15 10:00', '2026-01-15 10:00');
AI usage
AI was used in this phase and is logged in full, per the course rule for P1 onward.
- Phase log: AdvancedReportsAIUsage — service used, what the AI produced, and what I decided myself.
Service: Claude Code (Anthropic), https://claude.com/claude-code — Claude subscription,
model Claude Sonnet 5.
In short: I specified both report questions in full — including the exact formulas for P/L, ROI, consistency, market return, volatility and user participation — and asked the AI to turn them into working SQL, wire them into the prototype as real reports, build the relational-algebra equivalents, and produce demonstration data rich enough to show the reports doing something non-trivial. In a follow-up, I asked for the price-volatility column to be dropped from the market performance report — see the "Follow-up — 2026-09-17" section of AdvancedReportsAIUsage for that change.
