1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
);
|
