From c41d9036ed444c06f01bf80ed1fbcb06bbc1a42a Mon Sep 17 00:00:00 2001 From: Kevin Hoerr Date: Mon, 17 Nov 2025 03:11:10 -0500 Subject: 20251117-0-summary-view.sql: Add account_symbol_summary_view --- src/main/resources/db/20251117-0-summary-view.sql | 65 +++++++++++++++++++++++ 1 file changed, 65 insertions(+) create mode 100644 src/main/resources/db/20251117-0-summary-view.sql (limited to 'src/main/resources/db/20251117-0-summary-view.sql') 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 -- cgit v1.3