SQL Views: Financial Portfolio Management

In this scenario, we will create SQL views to assist in managing and analyzing a financial portfolio. The portfolio management system includes several tables: investors, accounts, transactions, assets, and asset_prices. The goal is to create SQL views that provide insights into portfolio performance, asset allocation, and investor activity.

Table Structures

1. investors
   • investor_id (INT, Primary Key)
   • name (VARCHAR)
   • email (VARCHAR)
   • created_at (TIMESTAMP)
2. accounts
   • account_id (INT, Primary Key)
   • investor_id (INT, Foreign Key)
   • account_type (VARCHAR)
   • created_at (TIMESTAMP)
3. transactions
   • transaction_id (INT, Primary Key)
   • account_id (INT, Foreign Key)
   • asset_id (INT, Foreign Key)
   • transaction_date (TIMESTAMP)
   • transaction_type (VARCHAR)
   • quantity (DECIMAL)
   • price (DECIMAL)
4. assets
   • asset_id (INT, Primary Key)
   • name (VARCHAR)
   • asset_type (VARCHAR)
5. asset_prices
   • asset_id (INT, Foreign Key)
   • price_date (DATE)
   • price (DECIMAL)

View 1: Portfolio Performance

This view calculates the current value of each investor’s portfolio based on the latest asset prices.

CREATE VIEW portfolio_performance AS
SELECT
    i.investor_id,
    i.name AS investor_name,
    a.account_id,
    SUM(t.quantity * ap.price) AS portfolio_value
FROM
    investors i
JOIN
    accounts a ON i.investor_id = a.investor_id
JOIN
    transactions t ON a.account_id = t.account_id
JOIN
    (SELECT asset_id, price
     FROM asset_prices
     WHERE price_date = (SELECT MAX(price_date) FROM asset_prices)) ap ON t.asset_id = ap.asset_id
GROUP BY
    i.investor_id, i.name, a.account_id;

View 2: Asset Allocation

This view provides a breakdown of the portfolio by asset type for each investor.

CREATE VIEW asset_allocation AS
SELECT
    i.investor_id,
    i.name AS investor_name,
    ass.asset_type,
    SUM(t.quantity * ap.price) AS total_value
FROM
    investors i
JOIN
    accounts a ON i.investor_id = a.investor_id
JOIN
    transactions t ON a.account_id = t.account_id
JOIN
    assets ass ON t.asset_id = ass.asset_id
JOIN
    (SELECT asset_id, price
     FROM asset_prices
     WHERE price_date = (SELECT MAX(price_date) FROM asset_prices)) ap ON t.asset_id = ap.asset_id
GROUP BY
    i.investor_id, i.name, ass.asset_type;

View 3: Investor Activity

This view tracks the activity of each investor, including the number of transactions and the total volume traded.

CREATE VIEW investor_activity AS
SELECT
    i.investor_id,
    i.name AS investor_name,
    COUNT(t.transaction_id) AS total_transactions,
    SUM(t.quantity * t.price) AS total_volume
FROM
    investors i
JOIN
    accounts a ON i.investor_id = a.investor_id
JOIN
    transactions t ON a.account_id = t.account_id
GROUP BY
    i.investor_id, i.name;

View 4: Asset Price Trends

This view provides the historical price trends of each asset for analysis and reporting.

CREATE VIEW asset_price_trends AS
SELECT
    a.asset_id,
    a.name AS asset_name,
    ap.price_date,
    ap.price
FROM
    assets a
JOIN
    asset_prices ap ON a.asset_id = ap.asset_id
ORDER BY
    a.asset_id, ap.price_date;

Usage

These views can now be used to generate reports or integrated into financial analysis tools to provide real-time insights into the portfolio’s performance. For example:

• Portfolio Performance: Monitor the current value of each investor’s portfolio.
• Asset Allocation: Analyze the distribution of investments across different asset types.
• Investor Activity: Track the trading activity of investors to identify trends and patterns.
• Asset Price Trends: Analyze historical price trends for investment strategies.

Advanced Techniques

1. Materialized Views: For performance reasons, consider using materialized views if the underlying data does not change frequently.
2. Indexes: Create indexes on columns used in JOINs and WHERE clauses to speed up query execution.
3. Partitioning: If the dataset is large, partitioning the tables by date (e.g., transactions by month) can improve performance.