20251117-0-summary-view.sql: Add account_symbol_summary_viewHEADmain

1 files changed, 65 insertions, 0 deletions

diff --git a/src/main/resources/db/20251117-0-summary-view.sql b/src/main/resources/db/20251117-0-summary-view.sql
new file mode 100644
index 0000000..a4e8004
--- /dev/null
+++ b/src/main/resources/db/20251117-0-summary-view.sql
@@ -0,0 +1,65 @@
+-- Yucky slow view, since it's sub-querying for each of the summation fields
+-- AND has a subquery on the inner join to find the right symbols. Anyways it
+-- should preview relative strength of treating equity transaction data as
+-- immutable. Any application on top of this should get the full list of
+-- records so it can construct helpful views of summations based on the history
+-- of changes, and/or show price data over time
+CREATE VIEW account_symbol_summary_view
+AS
+SELECT
+    acc.fiduciary_account_id,
+    acc.fiduciary_account_name,
+    acc.fiduciary_account_description,
+    acc.fiduciary_account_number,
+    acc.fiduciary_account_created_timestamp,
+    sym.equity_symbol_id,
+    sym.equity_symbol_type,
+    sym.equity_symbol_name,
+    sym.equity_symbol_managing_company,
+    (SELECT
+        SUM(ev.equity_change_quantity * ev.equity_change_type_score)
+       FROM equity_change_event ev
+      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
+        AND ev.equity_symbol_id = sym.equity_symbol_id
+    ) AS account_symbol_quantity,
+    (SELECT
+        printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score) / SUM(ev.equity_change_quantity * ev.equity_change_type_score))
+       FROM equity_change_event ev
+      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
+        AND ev.equity_symbol_id = sym.equity_symbol_id
+    ) AS account_symbol_cost_basis_price,
+    (SELECT
+        printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score))
+       FROM equity_change_event ev
+      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
+        AND ev.equity_symbol_id = sym.equity_symbol_id
+    ) AS account_symbol_cost_basis_total_usd,
+    (SELECT
+        printf( '%.2f', SUM(ev.equity_change_quantity) * (
+            SELECT
+                iev.equity_change_est_price
+              FROM equity_change_event iev
+             WHERE iev.fiduciary_account_id = acc.fiduciary_account_id
+               AND iev.equity_symbol_id = sym.equity_symbol_id
+              ORDER BY iev.equity_change_timestamp DESC
+              LIMIT 1
+        ))
+       FROM equity_change_event ev
+      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
+        AND ev.equity_symbol_id = sym.equity_symbol_id
+    ) AS account_symbol_last_effective_price,
+    (SELECT
+        ev.equity_change_timestamp
+       FROM equity_change_event ev
+      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
+        AND ev.equity_symbol_id = sym.equity_symbol_id
+       ORDER BY ev.equity_change_timestamp DESC
+       LIMIT 1
+    ) AS account_symbol_last_update_timestamp
+FROM fiduciary_account acc
+INNER JOIN equity_symbol sym ON EXISTS(
+    SELECT *
+      FROM equity_change_event e
+     WHERE e.fiduciary_account_id = acc.fiduciary_account_id
+       AND e.equity_symbol_id = sym.equity_symbol_id
+);
\ No newline at end of file