Report the Capital gain/loss for each stock. The Capital gain/loss of a stock is total gain or loss after buying and selling the stock one or many times.
This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.
Report the Capital gain/loss for each stock. The Capital gain/loss of a stock is total gain or loss after buying and selling the stock one or many times.
This is a sign-normalization problem. Financial reporting, inventory movement, and ledger analysis often depend on converting different event types into positive and negative contributions so a single aggregate can compute the final net result.
stock_nameoperationoperation_daypriceSELECT
stock_name,
SUM(
CASE
WHEN operation = 'Buy' THEN -price
ELSE price
END
) AS capital_gain_loss
FROM stocks
GROUP BY stock_nameThis is the cleanest solution. It translates each row into signed cash flow first, then adds the flows per stock to get the net gain or loss.
WITH stock_flows AS (
SELECT
stock_name,
SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END) AS total_sold,
SUM(CASE WHEN operation = 'Buy' THEN price ELSE 0 END) AS total_bought
FROM stocks
GROUP BY stock_name
)
SELECT
stock_name,
total_sold - total_bought AS capital_gain_loss
FROM stock_flowsThe two-bucket version is more verbose, but it can be easier to explain when you want to show the inflow and outflow components separately before subtracting them.
The main mistake is summing raw prices without flipping the sign for buys. That treats money spent as if it were profit. Another is trying to match buy and sell rows pairwise when the prompt only asks for total net result per stock.
Because the metric asked for in this challenge is total capital gain or loss per stock after all transactions, not realized gain per individual lot. Net cash flow is enough for this schema and prompt.
It reinforces conditional aggregation and sign normalization: translating different row types into a common metric scale so they can be added meaningfully.
Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.
Turn multiple queries into one with conditional aggregation. Learn how CASE WHEN inside aggregate functions creates powerful single-query reports and pivot tables.
Learn how to add conditional logic to your SQL queries using CASE statements. Transform data and categorize results with ease.
Master SQL aggregate functions with practical examples. Learn when and how to use COUNT, SUM, AVG, MIN, and MAX effectively.
If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.
Analytics
A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.
Advanced SQL
Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.
If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.
Roll monthly transaction reporting into one query while splitting total volume from approved volume by country.
Calculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.
Return only the customers whose distinct purchased product set covers every product listed in the catalog.