For amateur breath-hold diving, there are 3 limiting factors:
-how long can you hold your breath?
-how fast can you swim while holding your breath?
-how good are you at equalizing the pressure on you ears?
For a typical 20-40-year old who can swim but has no freediving experience, I’d be impressed if they made it deeper than 15 feet.
For scuba diving, breath-holding is not in play, and you have more time to descend and clear your ears slowly. But there are other limiting factors:
-amount of air (or other breathing gas) in your tank-- they come in different sizes, the most common being an AL-80 (which actually holds only about 77 cubic feet of air, not 80.) The biggest standard size holds 130 cubic feet, but you can bring more than one tank. How much air you need depends on many factors, depth being a very significant one. Every 33 feet increases the pressure by the same amount as the entire earth’s atmosphere, so at 33 feet you’ll breathe through your tank twice as fast as you would on the surface, and at the recreational depth limit of 132 feet, it’ll be 5 times as fast. Body size and exertion/stress level count too. Most people breathe between .3 and .7 cubic feet of air per minute on the surface when sitting on the couch. But new divers often blow through an AL-80 in as little as 20-30 minutes on relatively shallow (above 60 feet) dives.
-avoiding decompression sickness, aka the bends. Most divers do this by observing the “no decompression limit,” a sliding scale of depth and time that, if you stay under it, you can come up to the surface without making decompression stops and without getting bent. Dive computers and tables can help you fine-tune this, but a good rule of thumb is the depth in feet plus the time in minutes should not exceed 120. So, you can stay at 60 feet for an hour, but as you go deeper you get less time. If you go deeper or stay longer, you need to bring extra tanks, because mere minutes beyond the no decompression limit at greater depths can translate into hours of decompression stops.
-gas narcosis, which, as pointed out above, can kick in at relatively shallow depths well above 100 feet, but pretty much everyone starts to get drunk-ish below that depth.
But there are human rules that kick in before the laws of physics will stop you. If you’re not a trained and certified scuba diver, you’ll be hard-pressed to find a shop that will rent you equipment or fill your tank. You can hire a divemaster to take you on a Discover Dive, sort of a tandem experience with minimal training, but they’re not supposed to take you below 40 feet. If you had an idiot diver pal who agreed to help you try to cheat death, you might pull off a bounce dive to somewhere around 150 feet and come back alive. But there’s a lot that could go wrong in the attempt.