| 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 | SET search_path TO project, public;
|
|---|
| 31 |
|
|---|
| 32 | DELETE FROM transactions WHERE description = 'P6 demo data';
|
|---|
| 33 | DELETE FROM orders WHERE id IN (
|
|---|
| 34 | 'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222',
|
|---|
| 35 | 'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444',
|
|---|
| 36 | 'e5555555-5555-5555-5555-555555555555'
|
|---|
| 37 | );
|
|---|
| 38 | DELETE FROM market_trades WHERE source = 'p6_demo';
|
|---|
| 39 |
|
|---|
| 40 | -- ============================================================================
|
|---|
| 41 | -- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency)
|
|---|
| 42 | -- ============================================================================
|
|---|
| 43 | INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
|
|---|
| 44 | ('b1111111-1111-1111-1111-111111111111', 'buy', -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'),
|
|---|
| 45 | ('b1111111-1111-1111-1111-111111111111', 'sell', 5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
|
|---|
| 46 | ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'),
|
|---|
| 47 |
|
|---|
| 48 | ('b1111111-1111-1111-1111-111111111111', 'buy', -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'),
|
|---|
| 49 | ('b1111111-1111-1111-1111-111111111111', 'sell', 3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
|
|---|
| 50 | ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'),
|
|---|
| 51 |
|
|---|
| 52 | ('b1111111-1111-1111-1111-111111111111', 'buy', -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'),
|
|---|
| 53 | ('b1111111-1111-1111-1111-111111111111', 'sell', 6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
|
|---|
| 54 | ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'),
|
|---|
| 55 |
|
|---|
| 56 | ('b1111111-1111-1111-1111-111111111111', 'buy', -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'),
|
|---|
| 57 | ('b1111111-1111-1111-1111-111111111111', 'sell', 2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
|
|---|
| 58 | ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'),
|
|---|
| 59 |
|
|---|
| 60 | ('b1111111-1111-1111-1111-111111111111', 'buy', -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'),
|
|---|
| 61 | ('b1111111-1111-1111-1111-111111111111', 'sell', 5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'),
|
|---|
| 62 | ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data');
|
|---|
| 63 |
|
|---|
| 64 | -- ============================================================================
|
|---|
| 65 | -- Bob: three quarterly round trips, all profitable (100% consistency),
|
|---|
| 66 | -- smaller total P/L than Alice but a higher ROI.
|
|---|
| 67 | -- ============================================================================
|
|---|
| 68 | INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES
|
|---|
| 69 | ('b2222222-2222-2222-2222-222222222222', 'buy', -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'),
|
|---|
| 70 | ('b2222222-2222-2222-2222-222222222222', 'sell', 2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
|
|---|
| 71 | ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'),
|
|---|
| 72 |
|
|---|
| 73 | ('b2222222-2222-2222-2222-222222222222', 'buy', -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'),
|
|---|
| 74 | ('b2222222-2222-2222-2222-222222222222', 'sell', 2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
|
|---|
| 75 | ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'),
|
|---|
| 76 |
|
|---|
| 77 | ('b2222222-2222-2222-2222-222222222222', 'buy', -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'),
|
|---|
| 78 | ('b2222222-2222-2222-2222-222222222222', 'sell', 2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'),
|
|---|
| 79 | ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data');
|
|---|
| 80 |
|
|---|
| 81 | -- ============================================================================
|
|---|
| 82 | -- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters.
|
|---|
| 83 | -- source='p6_demo' keeps these separate from data_load.sql's own rows and
|
|---|
| 84 | -- from live user/bot fills so this script can clean up after itself.
|
|---|
| 85 | -- ============================================================================
|
|---|
| 86 | INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES
|
|---|
| 87 | ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy', 'p6_demo'),
|
|---|
| 88 | ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy', 'p6_demo'),
|
|---|
| 89 | ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy', 'p6_demo'),
|
|---|
| 90 | ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy', 'p6_demo'),
|
|---|
| 91 |
|
|---|
| 92 | ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00', 4000.000000, 3.000000, 'sell', 'p6_demo'),
|
|---|
| 93 | ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00', 3800.000000, 2.500000, 'sell', 'p6_demo'),
|
|---|
| 94 | ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00', 3600.000000, 2.000000, 'sell', 'p6_demo'),
|
|---|
| 95 | ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00', 3550.000000, 1.800000, 'sell', 'p6_demo');
|
|---|
| 96 |
|
|---|
| 97 | -- ============================================================================
|
|---|
| 98 | -- Executed orders: who participated in which market, across the same quarters.
|
|---|
| 99 | -- ============================================================================
|
|---|
| 100 | INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
|
|---|
| 101 | ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
|
|---|
| 102 | 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
|
|---|
| 103 | '2025-07-15 10:00', '2025-07-15 10:00'),
|
|---|
| 104 | ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
|
|---|
| 105 | 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
|
|---|
| 106 | '2025-10-15 10:00', '2025-10-15 10:00'),
|
|---|
| 107 | ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
|
|---|
| 108 | 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
|
|---|
| 109 | '2026-01-15 10:00', '2026-01-15 10:00'),
|
|---|
| 110 | ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
|
|---|
| 111 | 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
|
|---|
| 112 | '2026-04-15 10:00', '2026-04-15 10:00'),
|
|---|
| 113 | ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
|
|---|
| 114 | 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
|
|---|
| 115 | '2026-01-15 10:00', '2026-01-15 10:00');
|
|---|
