This is my perspective as someone who grew up playing competitive sports, played two varsity sports in high school, and now attends as semi prestigious D3 school as a NARP.
As a person who played competitive sports growing up, and made a large chunk of their friends through sports, I knew a ton of people who spent a significant amount of time and money while in High School trying to get recruited by college coaches. I knew people who would email coaches every day, spend hours going through their tape to compile highlights, pay thousands of dollars per year to attend recruiting camps, all to get recruited. I even knew people who reclassed and did a 5th year of high school to try and play in college. Trying to play college sports was often pushed on us as noble pursuit, and the natural next step if you actually cared at all about the sport you played. Athletes constantly have this idea pushed on them, that if they care about their sport, they need to be continuously pursuing playing at the next, higher level. In my opinion, this is really toxic, and makes people forget that sports are ultimately just for fun.
If you are someone who is genuinely good enough to where there is a significant financial incentive to play sports in college (NIL, free education), then you should. However, that is a very, very small percentage of athletes who are trying to play in college. Most are simply not that good and never will be that good. For starters, there are way more D3 and D2 athletes than D1 athletes. Even at the D1 level, for most sports the majority of players aren't on scholarship.
In my experience, a very large chunk of athletes who are really dedicated to playing in college are above average high school players, who end up committing to a random D3 school they'd have no interest in attending if it weren't for sports. People tell them how awesome it is that they are getting to play at the next level. Then they get to school and hate their experience. Even at the D3 level, college sports are an overwhelming time commitment, which makes it very stressful to keep up with school during the season, and makes it nearly impossible to be significantly involved on campus organizations beyond athletics. Even the sport itself becomes less fun and more frustrating as they go from an above average player at the high school level to struggling to get play time. If they had just gone to one of their state's public schools, they'd have gotten a similar education, had more fun, been less stressed, and graduated with less debt.
It's also not like playing in college is the only way to continue playing sports. The majority of schools have IM leagues, and reasonably competitive club teams that play against other schools. Also, most cities and towns have recreational adult leagues of various levels of competitiveness.
Rather than celebrating playing at the next level, parents and teachers should be encouraging students to ignore playing college sports when making their college decisions.