Scenario

You work for a private equity firm and have been given two tables: one containing each firm's shareholdings per company and another containing daily closing prices per company.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("portfolio") }} with {{ ref("prices") }} on the company column using INNER JOIN
2. Computes portfolio_value as CAST(SUM(shares * closing_price) AS INTEGER) grouped by PE firm and date
3. Returns PE_firm, date, and portfolio_value

Schema

portfolio

prices

Example

portfolio:

prices:

Expected Output:

Note: Each firm's daily portfolio value is the sum of (shares held times closing price) across all companies in their portfolio. For example, Apex on 2024-03-01 is (800 x 45) + (1500 x 32) = 84000.

WITH daily_values AS (
    SELECT
        p.PE_firm,
        pr.date,
        CAST(SUM(p.shares * pr.closing_price) AS INTEGER) AS portfolio_value
    FROM {{ ref("portfolio") }} AS p
    INNER JOIN {{ ref("prices") }} AS pr
        ON p.company = pr.company
    GROUP BY p.PE_firm, pr.date
)
SELECT * FROM daily_values

Explanation

Step 1: Joining portfolio and prices

FROM {{ ref("portfolio") }} AS p
INNER JOIN {{ ref("prices") }} AS pr
    ON p.company = pr.company

An INNER JOIN pairs each shareholding row with every price row for the same company. If a firm holds shares in companies A and B and there are two dates of price data, each holding row matches against each date for that company. Only companies that appear in both tables are included.

Step 2: Aggregating with SUM and GROUP BY

CAST(SUM(p.shares * pr.closing_price) AS INTEGER) AS portfolio_value
...
GROUP BY p.PE_firm, pr.date

For each (PE_firm, date) pair, the expression p.shares * pr.closing_price computes the value of a single holding on that date. SUM then totals those values across all companies in the firm's portfolio. CAST(... AS INTEGER) converts the result from a floating-point number to a whole number.

Step 3: Alternative without a CTE

You can write the same logic without a Common Table Expression:

SELECT
    p.PE_firm,
    pr.date,
    CAST(SUM(p.shares * pr.closing_price) AS INTEGER) AS portfolio_value
FROM {{ ref("portfolio") }} AS p
INNER JOIN {{ ref("prices") }} AS pr
    ON p.company = pr.company
GROUP BY p.PE_firm, pr.date

Both approaches produce identical results. The CTE version separates the transformation logic into a named block, which improves readability when the query grows more complex.