Profitable Stocks

Profitable Stocks is one of the five questions in the HackerRank SQL (Basic) certification test. It is short. The two things to get right are the comparison and the sort.

What the question asks

There is one table of today’s stock prices and another of predicted prices for tomorrow. A stock counts as profitable if tomorrow’s price is strictly higher than today’s. Return the stock_code of every profitable stock, sorted in ascending order.

Tables

Both tables have the same two columns, and stock_code is the primary key in each.

Solution

SELECT t.stock_code
FROM price_today t
JOIN price_tomorrow n ON n.stock_code = t.stock_code
WHERE n.price > t.price
ORDER BY t.stock_code ASC;

How it works

1. The join puts today’s price and tomorrow’s price for the same stock on one row.
2. n.price > t.price keeps the stocks that go up. Equal prices are not profitable.
3. ORDER BY t.stock_code ASC sorts the codes the way the question asks.

Both tables have a column called price, which is why the aliases matter. A bare price in the WHERE clause makes MySQL stop with an “ambiguous column” error.

Mistakes that fail the hidden tests

• Using >=. A stock whose price stays the same is not profitable. In my test, >= added WIPRO, whose price did not move.
• Comparing the wrong way round. t.price > n.price returns the stocks that fall. Name your aliases so you can tell them apart (I use t for today and n for next day).
• Missing ORDER BY. The question asks for ascending order. Don’t rely on the order MySQL happens to return.
• Using a LEFT JOIN and then filtering on NULLs. A stock that is missing from one table has nothing to compare, so it can’t be profitable. A plain JOIN drops it cleanly.

Try it on sample data

These are my own rows. They include a price that doesn’t change, a rise of just 1, and stocks that appear in only one table.

price_today

price_tomorrow

Output

INFY falls, WIPRO stays flat, SBIN has no price for tomorrow and ZOMATO has no price for today, so all four are out. ITC is in because a rise of 1 is still a rise. I ran this in MySQL and SQLite and got the same three rows in the same order.