source: docs/P6-AdvancedReports/wiki/AdvancedReportsAIUsage.md

main
Last change on this file was ef1c1c7, checked in by Stefan <trsunovstefan@…>, 6 days ago

Wiki docs, phase 6 and phase 7 added
  • Property mode set to 100644
File size: 21.8 KB
Line 
1= Advanced Reports AI Usage =
2
3== Name of AI service/solution that was used ==
4
5'''Claude Code''' (Anthropic)
6
7 * '''URL:''' `https://claude.com/claude-code`
8 * '''Type of service/subscription:''' Claude subscription, model Claude Sonnet 5.
9
10== Final result ==
11
12=== Diagram ===
13
14None. Both reports read `transactions`, `market_trades`, `orders`, `markets`, `crypto` and
15`users` exactly as they already existed after
16Normalization — no attribute or relation was missing,
17so [wiki:ERModel] and
18[wiki:RelationalDesign] needed no changes and there is
19no new diagram for this phase. This is stated explicitly rather than left implicit because the
20phase rubric specifically calls out modifying the design as the fallback when a good report
21idea can't be answered by the data on hand — it wasn't needed here.
22
23=== Results in details / description ===
24
25The AI:
26
27 * Turned my two fully-specified report questions (the exact P/L, ROI, consistency, volume,
28 return, volatility and participation formulas were mine) into two single-statement SQL
29 queries, each wrapped as a `LANGUAGE sql STABLE` function
30 (`project.report_top_traders`, `project.report_market_performance`) in
31 `schema_creation.sql`, so the phase's "just one SQL
32 query" requirement is met by the query text itself, while still giving the prototype a
33 clean, parameterised, named thing to call.
34
35The two functions, from `schema_creation.sql`:
36
37{{{
38-- report_top_traders: realized trading performance per user over [p_from, p_to),
39-- bucketed into quarters to measure how consistently each user was profitable.
40CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
41RETURNS TABLE (
42 username varchar,
43 realized_pl numeric,
44 total_invested numeric,
45 roi_pct numeric,
46 profitable_periods bigint,
47 losing_periods bigint,
48 total_periods bigint,
49 consistency_pct numeric
50)
51LANGUAGE sql STABLE AS $$
52 WITH period_pl AS (
53 SELECT
54 t.user_id,
55 date_trunc('quarter', t.created_at) AS period,
56 SUM(t.amount) AS period_pl,
57 SUM(t.amount) FILTER (WHERE t.type = 'buy') AS period_buy
58 FROM project.transactions t
59 WHERE t.type IN ('buy', 'sell', 'fee')
60 AND t.created_at >= p_from
61 AND t.created_at < p_to
62 GROUP BY t.user_id, date_trunc('quarter', t.created_at)
63 )
64 SELECT
65 u.username,
66 SUM(pp.period_pl) AS realized_pl,
67 ABS(SUM(pp.period_buy)) AS total_invested,
68 ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2) AS roi_pct,
69 COUNT(*) FILTER (WHERE pp.period_pl > 0) AS profitable_periods,
70 COUNT(*) FILTER (WHERE pp.period_pl < 0) AS losing_periods,
71 COUNT(*) AS total_periods,
72 ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
73 / NULLIF(COUNT(*), 0) * 100, 2) AS consistency_pct
74 FROM period_pl pp
75 JOIN project.users u ON u.id = pp.user_id
76 GROUP BY u.id, u.username
77 ORDER BY realized_pl DESC;
78$$;
79
80-- report_market_performance: trading activity and price behaviour per market
81-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades
82-- (the complete tape — user fills and simulated fills alike); participating
83-- users can only come from orders, since market_trades has no user_id column.
84CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
85RETURNS TABLE (
86 symbol varchar,
87 quote_currency char(3),
88 total_volume numeric,
89 trade_count bigint,
90 avg_price numeric,
91 market_return_pct numeric,
92 participating_users bigint
93)
94LANGUAGE sql STABLE AS $$
95 WITH trades AS (
96 SELECT
97 market_id, price, quantity, executed_at,
98 FIRST_VALUE(price) OVER w AS first_price,
99 LAST_VALUE(price) OVER (PARTITION BY market_id ORDER BY executed_at
100 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
101 FROM project.market_trades
102 WHERE executed_at >= p_from AND executed_at < p_to
103 WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
104 ),
105 market_stats AS (
106 SELECT
107 market_id,
108 SUM(quantity) AS total_volume,
109 COUNT(*) AS trade_count,
110 AVG(price) AS avg_price,
111 MAX(first_price) AS first_price,
112 MAX(last_price) AS last_price
113 FROM trades
114 GROUP BY market_id
115 ),
116 participation AS (
117 SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
118 FROM project.orders
119 WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
120 GROUP BY market_id
121 )
122 SELECT
123 c.symbol,
124 m.quote_currency,
125 ms.total_volume,
126 ms.trade_count,
127 ROUND(ms.avg_price, 6) AS avg_price,
128 ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
129 COALESCE(p.participating_users, 0) AS participating_users
130 FROM market_stats ms
131 JOIN project.markets m ON m.id = ms.market_id
132 JOIN project.crypto c ON c.id = m.crypto_id
133 LEFT JOIN participation p ON p.market_id = ms.market_id
134 ORDER BY ms.total_volume DESC;
135$$;
136}}}
137
138 * Wired both into the running CLI as real menu options — `server/reports.go`, options
139 `[10]`/`[11]` in `server/cli.go` — rather than leaving them as documentation-only SQL, per
140 the phase's own framing ("used as reports within your application").
141 * Wrote the relational-algebra equivalent of each query, including how to express
142 `FIRST_VALUE`/`LAST_VALUE` (which have no classical RA equivalent) as an aggregation for the
143 boundary timestamp followed by a self-join, and how to express `FILTER (WHERE …)`-style
144 conditional counts as separate groupings recombined with left outer joins.
145 * Noticed that the existing `data_load.sql` seed data (a few minutes of trade history) cannot
146 demonstrate either report meaningfully — everything falls into one quarter, so "consistency"
147 and "market return over time" have nothing to show — and wrote
148 `reports_demo_data.sql`, an optional, separate,
149 idempotent script adding five quarters of synthetic transactions, market trades and executed
150 orders, deliberately excluded from `-init`/`-load-data` so it cannot disturb the balances the
151 other use cases' documented "verified run" sections depend on.
152
153`reports_demo_data.sql`:
154
155{{{
156-- reports_demo_data.sql
157-- EduBerza - optional historical data for the P6 reports
158-- Course: Databases 2025/2026 Winter, FINKI UKIM
159--
160-- data_load.sql only seeds ~10 minutes of trade history, which is enough to
161-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or
162-- report_market_performance() doing anything interesting: everything falls
163-- into a single quarter, so "number of profitable periods" and "consistency"
164-- are trivial and "market return" has almost no history to work with.
165--
166-- This script adds five quarters of synthetic transactions, market trades and
167-- executed orders on top of an already-loaded data_load.sql, spanning
168-- 2025-07 to 2026-07, so the two P6 reports have several periods and two
169-- markets with opposite price trends to actually compare.
170--
171-- Deliberately NOT part of -init / -load-data: it only inserts into
172-- transactions, market_trades and orders, and does not touch
173-- users.available_balance/invested_balance or holdings, so it does not
174-- disturb the balances the other use cases' documented "verified run"
175-- sections depend on. Run it by hand, after data_load.sql, only to exercise
176-- the two reports:
177--
178-- psql "$DATABASE_URL" -f server/db/schema_creation.sql
179-- psql "$DATABASE_URL" -f server/db/data_load.sql
180-- psql "$DATABASE_URL" -f server/db/reports_demo_data.sql
181--
182-- Idempotent: deletes its own previously-inserted rows (tagged via
183-- description/source) before re-inserting.
184
185SET search_path TO project, public;
186
187DELETE FROM transactions WHERE description = 'P6 demo data';
188DELETE FROM orders WHERE id IN (
189 'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
190 'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
191 'e5555555-5555-5555-5555-555555555555'
192);
193DELETE FROM market_trades WHERE source = 'p6_demo';
194
195-- ============================================================================
196-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
197-- ============================================================================
198INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
199 ('b1111111-1111-1111-1111-111111111111', 'buy', -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
200 ('b1111111-1111-1111-1111-111111111111', 'sell', 5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
201 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
202
203 ('b1111111-1111-1111-1111-111111111111', 'buy', -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
204 ('b1111111-1111-1111-1111-111111111111', 'sell', 3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
205 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
206
207 ('b1111111-1111-1111-1111-111111111111', 'buy', -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
208 ('b1111111-1111-1111-1111-111111111111', 'sell', 6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
209 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
210
211 ('b1111111-1111-1111-1111-111111111111', 'buy', -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
212 ('b1111111-1111-1111-1111-111111111111', 'sell', 2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
213 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
214
215 ('b1111111-1111-1111-1111-111111111111', 'buy', -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
216 ('b1111111-1111-1111-1111-111111111111', 'sell', 5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
217 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');
218
219-- ============================================================================
220-- Bob: three quarterly round trips, all profitable (100% consistency),
221-- smaller total P/L than Alice but a higher ROI.
222-- ============================================================================
223INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
224 ('b2222222-2222-2222-2222-222222222222', 'buy', -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
225 ('b2222222-2222-2222-2222-222222222222', 'sell', 2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
226 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
227
228 ('b2222222-2222-2222-2222-222222222222', 'buy', -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
229 ('b2222222-2222-2222-2222-222222222222', 'sell', 2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
230 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
231
232 ('b2222222-2222-2222-2222-222222222222', 'buy', -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
233 ('b2222222-2222-2222-2222-222222222222', 'sell', 2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
234 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');
235
236-- ============================================================================
237-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
238-- source='p6_demo' keeps these separate from data_load.sql's own rows and
239-- from live user/bot fills so this script can clean up after itself.
240-- ============================================================================
241INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
242 ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy', 'p6_demo'),
243 ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy', 'p6_demo'),
244 ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy', 'p6_demo'),
245 ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy', 'p6_demo'),
246
247 ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00', 4000.000000, 3.000000, 'sell', 'p6_demo'),
248 ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00', 3800.000000, 2.500000, 'sell', 'p6_demo'),
249 ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00', 3600.000000, 2.000000, 'sell', 'p6_demo'),
250 ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00', 3550.000000, 1.800000, 'sell', 'p6_demo');
251
252-- ============================================================================
253-- Executed orders: who participated in which market, across the same quarters.
254-- ============================================================================
255INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
256 ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
257 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
258 '2025-07-15 10:00', '2025-07-15 10:00'),
259 ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
260 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
261 '2025-10-15 10:00', '2025-10-15 10:00'),
262 ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
263 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
264 '2026-01-15 10:00', '2026-01-15 10:00'),
265 ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
266 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
267 '2026-04-15 10:00', '2026-04-15 10:00'),
268 ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
269 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
270 '2026-01-15 10:00', '2026-01-15 10:00');
271}}}
272
273 * Ran both reports against a live PostgreSQL 16 database with that demo data loaded, through
274 the actual CLI, and used the real output (including a run where alice's seeded quarter
275 interacted with a pre-existing `data_load.sql` transaction and flipped a profitable quarter
276 into a loss) as the verified evidence in [wiki:AdvancedReports], rather
277 than inventing example numbers.
278
279== Summary of AI involvement ==
280
281|| ||= This session — 2026-09-16 =||
282||= What I brought =|| The phase rubric, plus both report questions fully specified down to the exact aggregate formulas ||
283||= What the AI did =|| Wrote the SQL, wrote the relational algebra, wired the reports into the CLI, designed and ran the demonstration data, verified everything against a live database ||
284||= What I decided =|| To keep both reports as SQL functions rather than plain ad-hoc queries so they are actually usable from the application; to accept the AI's synthetic multi-quarter demo dataset rather than wait for enough real usage history to accumulate ||
285
286The two ideas and their formulas were mine, specified in enough detail (P/L as the sum of
287buy+sell+fee transactions, ROI relative to total buys, consistency as a share of profitable
288quarters, market return as first-vs-last trade price, volatility as price standard deviation,
289participation from orders rather than trades) that there was no separate "AI alternative
290idea" to borrow from and document a change against, unlike the more open-ended P1–P3 phases —
291the AI's job here was implementation and verification of a fully-specified design, which is
292what is logged above and in the prompt below.
293
294== Entire AI usage log ==
295
296=== 2026-09-16 ===
297
298'''Intent:''' hand over the P6 rubric together with both report ideas, fully specified, and
299have the whole phase — SQL, relational algebra, prototype integration, and demonstration data
300— produced and verified in one pass.
301
302'''Prompt (student, verbatim):'''
303> Phase P6: Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
304> [the full phase rubric was pasted: 2 complex analytical reports solvable each with one SQL
305> query, usable as reports within the application, with a note that helper views/functions/
306> procedures are acceptable when pure SQL isn't enough, that the design should be extended if
307> a good idea needs data that doesn't exist yet, a requirement for the corresponding relational
308> algebra, the exact `AdvancedReports`/`AdvancedReportsAIUsage` wiki templates, and the phase's
309> AI-use rules]
310>
311> I will describe for you the 2 more complex sql queries:
312> 1. Find the users who performed the best in a selected quarter, year, or multi-year period
313> based on their realized trading performance, and show their total profit/loss, total
314> amount invested, ROI, number of profitable and losing periods, and consistency of
315> returns. Calculate realized P/L as SUM(sell transactions + buy transactions + fee
316> transactions), calculate ROI as realized P/L / absolute value of total buy transactions ×
317> 100, and calculate consistency as profitable periods / total periods × 100.
318> 2. Find which cryptocurrency markets performed best during a selected quarter, year, or
319> multi-year period based on trading activity and price behavior, and report the markets
320> with the highest trading volume, number of trades, number of participating users, and
321> price return. Calculate total volume as SUM(trade quantity), average trading price as
322> AVG(price), market return as (last trade price − first trade price) / first trade price ×
323> 100, price volatility as standard deviation of trade prices, and user participation as
324> COUNT(DISTINCT user_id) from the related executed orders.
325
326'''Response (AI, summarised):'''
327 * Confirmed both report questions were answerable from the existing schema with no new
328 columns or tables, and said so explicitly rather than silently skipping that check.
329 * Wrote `report_top_traders` and `report_market_performance` as SQL functions, added to
330 `schema_creation.sql` under a new `REPORTS (P6)` section.
331 * Wrote `server/reports.go` (`ShowTopTraders`, `ShowMarketPerformance`, a shared
332 `promptPeriod` helper) and added menu options `[10]`/`[11]` to `server/cli.go`.
333 * Wrote `server/db/reports_demo_data.sql` for a richer, multi-quarter demonstration, and
334 documented in its own header exactly why it is optional and what it deliberately does not
335 touch (user balances, holdings).
336 * Built the prototype, ran both reports against the live database with the demo data loaded,
337 cross-checked the SQL output against hand-computed expected values for both seeded users,
338 and used that real output as the verified evidence.
339 * Wrote out the relational-algebra derivation for each query and [wiki:AdvancedReports].
340
341'''What I decided:''' to keep the demo dataset out of `-init`/`-load-data` rather than merge it
342into `data_load.sql`, since the other phases' documented expected values (specific balances in
343[wiki:BuildInstructions]) depend on the seed data staying
344exactly as it is.
345
346> '''Student action required.''' Read [wiki:AdvancedReports] end to end
347> before the defense, and be ready to compute one period's realized P/L or one market's return
348> by hand from the raw `transactions`/`market_trades` rows — the numbers in the verified run
349> are real output, not invented, so they can be checked against
350> `reports_demo_data.sql` (shown above) directly. Append any further
351> prompts here if you ask for revisions.
352
353=== Follow-up — 2026-09-17 ===
354
355'''Intent:''' simplify the market performance report after seeing it run — with only a handful
356of trades per market in most periods, price volatility read as noise rather than a useful
357signal.
358
359'''Prompt (student, verbatim):'''
360> Can you tell me the exact database_url so I can execute the schema creating and the data_load?
361>
362> From the Market Performance can we remove Volatility, I don't think that is that useful, also
363> can you tell me what is users column?
364
365'''Response (AI, summarised):'''
366 * Gave the local dev connection string (`postgresql://bp_project:1234@localhost:5433/bp_database`,
367 from `.env`) and explained `participating_users` is `COUNT(DISTINCT user_id)` from that
368 market's executed orders, not from `market_trades` (which has no `user_id`).
369 * Removed `price_volatility`/`STDDEV(price)` from `report_market_performance()` in
370 `schema_creation.sql`, from its `RETURNS TABLE` and its `SELECT`, and from the corresponding
371 column in `server/reports.go`'s `ShowMarketPerformance`.
372 * Updated the relational algebra (`Stats`, `Result`) and the SQL listing, verified run output,
373 and prose in [wiki:AdvancedReports] to match, and added a short note
374 explaining why the column was dropped.
375 * Rebuilt, re-ran `-init` and `reports_demo_data.sql` against the live database, and
376 re-verified the market performance report through the actual CLI before and after the
377 change to confirm only the one column disappeared and every other number is unchanged.
378
379'''What I decided:''' to drop the column entirely rather than keep it computed-but-hidden,
380since an unused computation left in the query is exactly the kind of thing that should not
381survive a review.
Note: See TracBrowser for help on using the repository browser.