Changeset 9577c79 for server/db/schema_creation.sql

Timestamp:

09/16/26 23:37:15 (13 days ago)

Author:

Stefan <trsunovstefan@…>

Branches:

main

Children:

8b447ef

Parents:

df05838

Message:

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1

File:

• server/db/schema_creation.sql (modified) (3 diffs)

server/db/schema_creation.sql

@@ -59 +59 @@
 -- ============================================================================
 CREATE TABLE project.holdings (
-    id         uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
-    user_id    uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
-    crypto_id  uuid           NOT NULL REFERENCES project.crypto(id),
-    quantity   numeric(20,4)  NOT NULL CHECK (quantity >= 0),
+    id                uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id           uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
+    crypto_id         uuid           NOT NULL REFERENCES project.crypto(id),
+    quantity          numeric(20,4)  NOT NULL CHECK (quantity >= 0),
+    -- Committed to the user's own open sell orders, not yet removed from the
+    -- position. quantity - reserved_quantity is what is actually free to
+    -- sell — the crypto-side equivalent of users.available_balance.
+    reserved_quantity numeric(20,4)  NOT NULL DEFAULT 0
+                                      CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
     -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
     -- v_portfolio can never silently produce NULL for an existing position.
-    avg_price  numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
-    created_at timestamptz    NOT NULL DEFAULT now(),
-    updated_at timestamptz,
+    avg_price         numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
+    created_at        timestamptz    NOT NULL DEFAULT now(),
+    updated_at        timestamptz,
     CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
 );
@@ -184 +189 @@
        c.symbol,
        h.quantity,
+       h.reserved_quantity,
+       (h.quantity - h.reserved_quantity) AS available_quantity,
        h.avg_price,
        lp.price                           AS current_price,
@@ -192 +199 @@
 LEFT   JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
 LEFT   JOIN project.v_latest_prices lp ON lp.market_id = m.id;
+
+-- ============================================================================
+-- REPORTS (P6 — Complex DB Reports)
+-- Both are single SELECT statements (with CTEs), wrapped as SQL functions so
+-- they can be called as parameterised reports from the prototype instead of
+-- being copy-pasted SQL text. See docs/P6-AdvancedReports/AdvancedReports.md.
+-- ============================================================================
+
+-- report_top_traders: realized trading performance per user over [p_from, p_to),
+-- bucketed into quarters to measure how consistently each user was profitable.
+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;
+$$;
+
+-- report_market_performance: trading activity and price behaviour per market
+-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades
+-- (the complete tape — user fills and simulated fills alike); participating
+-- users can only come from orders, since market_trades has no user_id column.
+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;
+$$;