For basketball I collect HOF RC cards with heavy focus on 50's and 60's. For that stuff, I only buy SGC or PSA graded. There are a couple i'll likely never grab. 1957 Bill Russell, 1961 Fleer Wilt Chamberlain. Those cards, even in PSA 1-2 condition (poor to fair - both of which are pretty rough), will still go for over a grand. Either of those cards with eye appeal at all (PSA 4 minimum) will run 2K, which is more than i'll spend currently on a single card.
My better basketball cards are a 1957 Bob Cousy PSA 6, 1969 John Havlicek PSA 8, and 1970 Pete Maravich PSA 7. The cards i've been after recently are a Jerry West and Oscar Robertson rookie, in PSA / SGC 6 condition.
For modern baseketball - I do 3 types of collecting: game used mem cards from HOF's i'm a fan of. I have a couple Jerry West, Bill Russell, Kareem, and Oscar auto and patch cards in the Immaculate and Flawless lines. I paid $75-150 ea for those cards. They are all serial numbered (low) and are neat pieces of guys I like. I have no interest in buying boxes of that stuff - it is for speculators, yes. Most people don't buy boxes of them, rather a genre of retailer/collector called "Breakers" buy them, and then sell them off by team or by player. Search ebay for something like "flawless basketball break". For say $50 you get a part of that box, maybe you spend $50 and get "Boston Celtics". Any Larry Bird or Jayson Tatum cards pulled are yours. There's only 10 cards though, so at best, 10 of 30 people will get something - 20 won't. It's a way to "buy" into that type of high end card, but again, that is pure gambling and it isn't for my tastes. I'd rather spend $150 and get a card I really think is cool, of someone I enjoy, that is unlikely to move in value one way or the other. A serialed Bill Russell auto was $100~ 10 years ago and is the same today. Actual number and type of card will drive that slightly one way or the other, but not much.
For stuff to open - with my boys, we buy Panini Prizm and Panini Donruss Optic. They are both the lower end of the hobby side - $3-8 a pack or so. We look for good cards of people we like, we sleeve any rookies that aren't doing much, and will trade/sell any hits of players we aren't fans of, and trade/sell high. Meaning, guys like Mo Bamba and Lauri Markkinen are inflated right now - their prices currently are based on them having HOF careers, so if they do that, the current values will stay the same, anything short of that and as you say, this Optic Holo Bamba Auto that's $200 today, will be $2 in 10 years. We trade/sell the crap out of that stuff, and to an extent it helps support the hobby. Those hits are pretty rare of course, but we get enough here and there to buy more packs and try to pull cards we like. Again though, for my actual collecting, I just buy the actual cards I want, not packs.
For modern rookie collecting - again, only guys I really like, and only things I feel like aren't speculative. I'm not and won't pay $500 for a Luca auto, but I have paid that for a Bowman Chrome /25 Westbrook auto. So for newer stuff, i'm collecting limited RC's of future HOF's that I like, that I feel the price on isn't too risky either. Curry, Harden, Westbrook. Guys like Durant and James have similarly priced limited RC's, but I'm not fans of theirs, so don't have any of their stuff.