First-In, First-Out (FIFO) cost basis in Snowflake SQL

Calculating the First-In, First-Out (FIFO) cost basis within a Snowflake table involves assigning shares sold to the earliest purchases, thereby determining the cost basis for each sale transaction. Below, we’ll walk through:

Setting Up the Transactions Table: Creating and populating a sample transactions table.
Implementing FIFO Cost Basis Calculation: Using SQL with window functions and Common Table Expressions (CTEs) to assign sold shares to purchases following FIFO principles.
Generating the Cost Basis for Each Sale: Aggregating the results to determine the cost basis per sell transaction.

Setting Up the Transactions Table

First, we’ll create a transactions table to store all buy and sell transactions, along with their dates, number of shares, and prices.

CREATE OR REPLACE TABLE transactions (
    transaction_id INT AUTOINCREMENT,
    transaction_date DATE,
    type VARCHAR(4), -- 'BUY' or 'SELL'
    shares INT,
    price_per_share FLOAT
);

Let’s insert some sample data to illustrate the FIFO calculation. This dataset includes three buy transactions and two sell transactions.

INSERT INTO transactions (transaction_date, type, shares, price_per_share) VALUES
('2022-01-01', 'BUY', 100, 20.00),   -- Transaction 1
('2022-06-01', 'BUY', 100, 25.00),   -- Transaction 2
('2022-12-01', 'BUY', 100, 30.00),   -- Transaction 3
('2023-05-01', 'SELL', 150, 35.00),  -- Transaction 4
('2023-08-01', 'SELL', 50, 40.00);   -- Transaction 5

You can verify the inserted data using:

SELECT * FROM transactions ORDER BY transaction_date, transaction_id;

Sample Output:

TRANSACTION_ID	TRANSACTION_DATE	TYPE	SHARES	PRICE_PER_SHARE
1	2022-01-01	BUY	100	20.00
2	2022-06-01	BUY	100	25.00
3	2022-12-01	BUY	100	30.00
4	2023-05-01	SELL	150	35.00
5	2023-08-01	SELL	50	40.00

Implementing FIFO Cost Basis Calculation

To calculate the FIFO cost basis for each sell transaction, we’ll use a series of CTEs that perform the following steps:

Separate Buys and Sells: Identify and process buy and sell transactions separately.
Calculate Cumulative Shares: Compute cumulative shares bought and sold up to each transaction.
Assign Share Ranges: For each buy and sell, assign a range of share numbers based on cumulative totals.
Determine Overlapping Shares: Assign shares from buys to sells based on overlapping ranges, adhering to FIFO.

WITH
-- Step 1: Separate Buy Transactions and Calculate Cumulative Shares
buys AS (
    SELECT
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS buy_cum,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) - shares + 1 AS buy_start,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS buy_end
    FROM transactions
    WHERE type = 'BUY'
),

-- Step 2: Separate Sell Transactions and Calculate Cumulative Shares
sells AS (
    SELECT
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS sell_cum,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) - shares + 1 AS sell_start,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS sell_end
    FROM transactions
    WHERE type = 'SELL'
),

-- Step 3: Allocate Shares from Buys to Sells Based on Overlapping Ranges
allocations AS (
    SELECT
        s.transaction_id AS sell_transaction_id,
        b.transaction_id AS buy_transaction_id,
        GREATEST(0, LEAST(b.buy_end, s.sell_end) - GREATEST(b.buy_start, s.sell_start) + 1) AS shares_allocated,
        b.price_per_share
    FROM sells s
    CROSS JOIN buys b
    WHERE b.buy_end >= s.sell_start
      AND b.buy_start <= s.sell_end
),

-- Step 4: Filter Allocations with Allocated Shares > 0
filtered_allocations AS (
    SELECT
        sell_transaction_id,
        buy_transaction_id,
        shares_allocated,
        price_per_share
    FROM allocations
    WHERE shares_allocated > 0
)

-- Final Step: Present the Allocation Details
SELECT
    s.transaction_id AS sell_transaction_id,
    s.transaction_date AS sell_date,
    b.transaction_id AS buy_transaction_id,
    b.transaction_date AS buy_date,
    a.shares_allocated,
    b.price_per_share,
    ROUND(a.shares_allocated * b.price_per_share, 2) AS cost_basis
FROM sells s
JOIN filtered_allocations a ON s.transaction_id = a.sell_transaction_id
JOIN buys b ON a.buy_transaction_id = b.transaction_id
ORDER BY s.transaction_date, s.transaction_id, b.transaction_date, b.transaction_id;

Explanation

Buys CTE:

Filters BUY transactions.
Calculates cumulative shares bought (buy_cum).
Assigns a buy_start and buy_end to represent the range of share numbers for each buy.

Sells CTE:

Filters SELL transactions.
Calculates cumulative shares sold (sell_cum).
Assigns a sell_start and sell_end to represent the range of share numbers for each sell.

Allocations CTE:

Performs a cross join between sells and buys to examine all possible allocations.
Calculates shares_allocated by determining the overlap between each sell and buy’s share ranges using the GREATEST and LEAST functions.
Ensures allocation only occurs if there’s an overlapping range (shares_allocated > 0).

Filtered Allocations CTE:

Filters out any allocations where shares_allocated is zero, retaining only relevant allocations.

Final SELECT Statement:

Joins the sells, filtered_allocations, and buys CTEs to present the allocation details.
Calculates the cost_basis for each allocated share.
Orders the results by sell and buy dates for clarity.

Execute the above SQL script in your Snowflake environment. The expected output based on our sample data is as follows:

Sample Output:

SELL_TRANSACTION_ID	SELL_DATE	BUY_TRANSACTION_ID	BUY_DATE	SHARES_ALLOCATED	PRICE_PER_SHARE	COST_BASIS
4	2023-05-01	1	2022-01-01	100	20.00	2000.00
4	2023-05-01	2	2022-06-01	50	25.00	1250.00
5	2023-08-01	2	2022-06-01	50	25.00	1250.00

Explanation of the Output:

Transaction 4 (Sell 150 shares on 2023-05-01):

100 shares allocated from Transaction 1 (Buy 100 shares at $20.00).
50 shares allocated from Transaction 2 (Buy 100 shares at $25.00).
Total Cost Basis for Transaction 4: ( (100 \times 20) + (50 \times 25) = 2000 + 1250 = $3,250 ).

Transaction 5 (Sell 50 shares on 2023-08-01):

50 shares allocated from Transaction 2 (Buy 100 shares at $25.00).
Total Cost Basis for Transaction 5: ( 50 \times 25 = $1,250 ).

Generating the Cost Basis for Each Sale

While the previous query provides detailed allocations, you might also want a summarized view showing the total cost basis and capital gains for each sell transaction.

Calculate Total Cost Basis and Capital Gains per Sell

WITH
-- Reuse the previous CTEs
buys AS (
    SELECT
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS buy_cum,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) - shares + 1 AS buy_start,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS buy_end
    FROM transactions
    WHERE type = 'BUY'
),
sells AS (
    SELECT
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS sell_cum,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) - shares + 1 AS sell_start,
        SUM(shares) OVER (ORDER BY transaction_date, transaction_id) AS sell_end
    FROM transactions
    WHERE type = 'SELL'
),
allocations AS (
    SELECT
        s.transaction_id AS sell_transaction_id,
        b.transaction_id AS buy_transaction_id,
        GREATEST(0, LEAST(b.buy_end, s.sell_end) - GREATEST(b.buy_start, s.sell_start) + 1) AS shares_allocated,
        b.price_per_share
    FROM sells s
    CROSS JOIN buys b
    WHERE b.buy_end >= s.sell_start
      AND b.buy_start <= s.sell_end
),
filtered_allocations AS (
    SELECT
        sell_transaction_id,
        buy_transaction_id,
        shares_allocated,
        price_per_share
    FROM allocations
    WHERE shares_allocated > 0
),
-- Aggregate Cost Basis per Sell
cost_basis_per_sell AS (
    SELECT
        s.transaction_id AS sell_transaction_id,
        s.transaction_date AS sell_date,
        s.shares AS shares_sold,
        s.price_per_share AS sell_price_per_share,
        ROUND(SUM(a.shares_allocated * a.price_per_share), 2) AS total_cost_basis,
        ROUND((s.shares * s.price_per_share) - SUM(a.shares_allocated * a.price_per_share), 2) AS capital_gain
    FROM sells s
    JOIN filtered_allocations a ON s.transaction_id = a.sell_transaction_id
    JOIN buys b ON a.buy_transaction_id = b.transaction_id
    GROUP BY s.transaction_id, s.transaction_date, s.shares, s.price_per_share
)

-- Final Selection
SELECT
    sell_transaction_id,
    sell_date,
    shares_sold,
    sell_price_per_share,
    total_cost_basis,
    ROUND(s.shares_sold * s.sell_price_per_share, 2) AS proceeds,
    capital_gain
FROM cost_basis_per_sell s
ORDER BY s.sell_date, s.sell_transaction_id;

Explanation of the Aggregated Results

Sample Output:

SELL_TRANSACTION_ID	SELL_DATE	SHARES_SOLD	SELL_PRICE_PER_SHARE	TOTAL_COST_BASIS	PROCEEDS	CAPITAL_GAIN
4	2023-05-01	150	35.00	3250.00	5250.00	2000.00
5	2023-08-01	50	40.00	1250.00	2000.00	750.00

Explanation:

Transaction 4 (Sell 150 shares on 2023-05-01):

Total Cost Basis: $3,250.00 (as previously calculated).
Proceeds: ( 150 \times 35 = $5,250.00 ).
Capital Gain: ( 5250 - 3250 = $2,000.00 ).

Transaction 5 (Sell 50 shares on 2023-08-01):

Total Cost Basis: $1,250.00.
Proceeds: ( 50 \times 40 = $2,000.00 ).
Capital Gain: ( 2000 - 1250 = $750.00 ).

For clarity, here’s how the calculations align with the sample data:

Sell Transaction	Allocated Buy Transaction(s)	Shares Allocated	Cost Basis per Buy
4	1	100	(100 \times 20 = 2000)
4	2	50	(50 \times 25 = 1250)
5	2	50	(50 \times 25 = 1250)

Transaction 4:
- Allocates 100 shares from Transaction 1 and 50 shares from Transaction 2.
Transaction 5:
- Allocates the remaining 50 shares from Transaction 2.

Additional Considerations and Enhancements

Handling Multiple Assets

If your transactions table includes multiple assets (e.g., different stock tickers), you should modify the above queries to partition the calculations by asset. Here’s how to adjust the table and queries:

Modify the Transactions Table to Include Asset Identifier

CREATE OR REPLACE TABLE transactions (
    transaction_id INT AUTOINCREMENT,
    asset_symbol VARCHAR(10), -- e.g., 'AAPL', 'GOOGL'
    transaction_date DATE,
    type VARCHAR(4), -- 'BUY' or 'SELL'
    shares INT,
    price_per_share FLOAT
);

Adjust the CTEs to Partition by Asset Symbol

Add asset_symbol to each CTE and partition calculations accordingly.

WITH
-- Step 1: Separate Buy Transactions and Calculate Cumulative Shares per Asset
buys AS (
    SELECT
        asset_symbol,
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) AS buy_cum,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) - shares + 1 AS buy_start,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) AS buy_end
    FROM transactions
    WHERE type = 'BUY'
),

-- Step 2: Separate Sell Transactions and Calculate Cumulative Shares per Asset
sells AS (
    SELECT
        asset_symbol,
        transaction_id,
        transaction_date,
        shares,
        price_per_share,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) AS sell_cum,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) - shares + 1 AS sell_start,
        SUM(shares) OVER (PARTITION BY asset_symbol ORDER BY transaction_date, transaction_id) AS sell_end
    FROM transactions
    WHERE type = 'SELL'
),

-- Step 3: Allocate Shares from Buys to Sells Based on Overlapping Ranges per Asset
allocations AS (
    SELECT
        s.asset_symbol,
        s.transaction_id AS sell_transaction_id,
        b.transaction_id AS buy_transaction_id,
        GREATEST(0, LEAST(b.buy_end, s.sell_end) - GREATEST(b.buy_start, s.sell_start) + 1) AS shares_allocated,
        b.price_per_share
    FROM sells s
    CROSS JOIN buys b
    ON s.asset_symbol = b.asset_symbol
    WHERE b.buy_end >= s.sell_start
      AND b.buy_start <= s.sell_end
),

-- Step 4: Filter Allocations with Allocated Shares > 0
filtered_allocations AS (
    SELECT
        asset_symbol,
        sell_transaction_id,
        buy_transaction_id,
        shares_allocated,
        price_per_share
    FROM allocations
    WHERE shares_allocated > 0
)

-- Final Step: Present the Allocation Details per Asset
SELECT
    s.asset_symbol,
    s.sell_transaction_id,
    s.sell_date,
    b.buy_transaction_id,
    b.buy_date,
    a.shares_allocated,
    b.price_per_share,
    ROUND(a.shares_allocated * b.price_per_share, 2) AS cost_basis
FROM transactions_table s
JOIN filtered_allocations a 
    ON s.transaction_id = a.sell_transaction_id
JOIN buys b 
    ON a.buy_transaction_id = b.transaction_id
ORDER BY s.asset_symbol, s.sell_date, s.sell_transaction_id, b.buy_date, b.transaction_id;

Note: Adjust the final SELECT statement as needed to include asset-specific calculations.

Error Handling and Validation

Ensure that:

Sufficient Buys Exist: Before performing allocations, verify that the total shares sold do not exceed the total shares bought for each asset.

SELECT
    asset_symbol,
    SUM(CASE WHEN type = 'BUY' THEN shares ELSE -shares END) AS net_shares
FROM transactions
GROUP BY asset_symbol
HAVING SUM(CASE WHEN type = 'BUY' THEN shares ELSE -shares END) < 0;

Data Integrity: Ensure that all transactions are correctly labeled as ‘BUY’ or ‘SELL’ and that shares and prices are positive numbers.

Automating the Process

For dynamic datasets where transactions are continuously added, consider encapsulating the FIFO calculation logic within a Stored Procedure or a User-Defined Function (UDF) in Snowflake. This allows for reusability and automation without manually running SQL scripts.