source: server/db/reports_demo_data.sql

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

Wiki docs, phase 6 and phase 7 added
  • Property mode set to 100644
File size: 8.6 KB
Line 
1-- reports_demo_data.sql
2-- EduBerza - optional historical data for the P6 reports
3-- Course: Databases 2025/2026 Winter, FINKI UKIM
4--
5-- data_load.sql only seeds ~10 minutes of trade history, which is enough to
6-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or
7-- report_market_performance() doing anything interesting: everything falls
8-- into a single quarter, so "number of profitable periods" and "consistency"
9-- are trivial and "market return" has almost no history to work with.
10--
11-- This script adds five quarters of synthetic transactions, market trades and
12-- executed orders on top of an already-loaded data_load.sql, spanning
13-- 2025-07 to 2026-07, so the two P6 reports have several periods and two
14-- markets with opposite price trends to actually compare.
15--
16-- Deliberately NOT part of -init / -load-data: it only inserts into
17-- transactions, market_trades and orders, and does not touch
18-- users.available_balance/invested_balance or holdings, so it does not
19-- disturb the balances the other use cases' documented "verified run"
20-- sections depend on. Run it by hand, after data_load.sql, only to exercise
21-- the two reports:
22--
23-- psql "$DATABASE_URL" -f server/db/schema_creation.sql
24-- psql "$DATABASE_URL" -f server/db/data_load.sql
25-- psql "$DATABASE_URL" -f server/db/reports_demo_data.sql
26--
27-- Idempotent: deletes its own previously-inserted rows (tagged via
28-- description/source) before re-inserting.
29
30-- P7: users' cash (available + reserved) must equal their ledger at
31-- COMMIT, so the balance moves by exactly what this script removes and
32-- re-adds to the ledger, all in one transaction. The historical orders are
33-- imported as completely filled.
34
35BEGIN;
36
37SET search_path TO project, public;
38
39UPDATE users u
40 SET available_balance = u.available_balance - d.total
41 FROM (SELECT user_id, SUM(amount) AS total FROM transactions
42 WHERE description = 'P6 demo data' GROUP BY user_id) d
43 WHERE u.id = d.user_id;
44
45DELETE FROM transactions WHERE description = 'P6 demo data';
46DELETE FROM orders WHERE id IN (
47 'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
48 'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
49 'e5555555-5555-5555-5555-555555555555'
50);
51DELETE FROM market_trades WHERE source = 'p6_demo';
52
53-- ============================================================================
54-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
55-- ============================================================================
56INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
57 ('b1111111-1111-1111-1111-111111111111', 'buy', -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
58 ('b1111111-1111-1111-1111-111111111111', 'sell', 5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
59 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
60
61 ('b1111111-1111-1111-1111-111111111111', 'buy', -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
62 ('b1111111-1111-1111-1111-111111111111', 'sell', 3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
63 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
64
65 ('b1111111-1111-1111-1111-111111111111', 'buy', -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
66 ('b1111111-1111-1111-1111-111111111111', 'sell', 6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
67 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
68
69 ('b1111111-1111-1111-1111-111111111111', 'buy', -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
70 ('b1111111-1111-1111-1111-111111111111', 'sell', 2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
71 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
72
73 ('b1111111-1111-1111-1111-111111111111', 'buy', -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
74 ('b1111111-1111-1111-1111-111111111111', 'sell', 5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
75 ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');
76
77-- ============================================================================
78-- Bob: three quarterly round trips, all profitable (100% consistency),
79-- smaller total P/L than Alice but a higher ROI.
80-- ============================================================================
81INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
82 ('b2222222-2222-2222-2222-222222222222', 'buy', -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
83 ('b2222222-2222-2222-2222-222222222222', 'sell', 2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
84 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
85
86 ('b2222222-2222-2222-2222-222222222222', 'buy', -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
87 ('b2222222-2222-2222-2222-222222222222', 'sell', 2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
88 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
89
90 ('b2222222-2222-2222-2222-222222222222', 'buy', -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
91 ('b2222222-2222-2222-2222-222222222222', 'sell', 2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
92 ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');
93
94-- ============================================================================
95-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
96-- source='p6_demo' keeps these separate from data_load.sql's own rows and
97-- from live user/bot fills so this script can clean up after itself.
98-- ============================================================================
99INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
100 ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy', 'p6_demo'),
101 ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy', 'p6_demo'),
102 ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy', 'p6_demo'),
103 ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy', 'p6_demo'),
104
105 ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00', 4000.000000, 3.000000, 'sell', 'p6_demo'),
106 ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00', 3800.000000, 2.500000, 'sell', 'p6_demo'),
107 ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00', 3600.000000, 2.000000, 'sell', 'p6_demo'),
108 ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00', 3550.000000, 1.800000, 'sell', 'p6_demo');
109
110-- ============================================================================
111-- Executed orders: who participated in which market, across the same quarters.
112-- ============================================================================
113INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
114 ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
115 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 0.5000, 40000.000000,
116 '2025-07-15 10:00', '2025-07-15 10:00'),
117 ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
118 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 3.0000, 4000.000000,
119 '2025-10-15 10:00', '2025-10-15 10:00'),
120 ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
121 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 1.2000, 55000.000000,
122 '2026-01-15 10:00', '2026-01-15 10:00'),
123 ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
124 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 1.0000, 60000.000000,
125 '2026-04-15 10:00', '2026-04-15 10:00'),
126 ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
127 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 2.0000, 3600.000000,
128 '2026-01-15 10:00', '2026-01-15 10:00');
129
130UPDATE users u
131 SET available_balance = u.available_balance + d.total
132 FROM (SELECT user_id, SUM(amount) AS total FROM transactions
133 WHERE description = 'P6 demo data' GROUP BY user_id) d
134 WHERE u.id = d.user_id;
135
136COMMIT;
Note: See TracBrowser for help on using the repository browser.