Imagine you are a level 5 Variant Human, selected a class with access to martial weapons and Extra Attack, and chose the Crossbow Expert and Sharpshooter feats.
You are able to attack two times a turn with a heavy crossbow as an action and three times with a hand crossbow as an action plus a bonus action. The damage you would do (assuming 16 Dex) with a heavy crossbow is 2×(1d10+3+10)=37 on average. The damage with a hand crossbow is 3×(1d6+3+10)=49.5 on average.
If you do not have a reliable use of your bonus action (e.g. Rogue’s Cunning action, etc.), when would a heavy crossbow be superior to a hand crossbow?