You're mixing some things up. Yes, some agencies will have some POLICIES about not wanting to hire personnel with a history of drug abuse/use, but that is separate from the clearance adjudication process.

A secret clearance is a secret clearance, and you're correct that it's much simpler to get a basic secret than it is a TS-SCI or to be read into certain programs. But there isn't a "FBI" secret and an "Army" secret.

There's no timeline for how long it's been since you've smoked pot, or number of times, or anything. I think a poster said that it's about whether the investigation finds you trustworthy enough for the level of eligibility they're investigating you for, and that is correct - and there isn't a hard and fast rule necessarily.

If you do an investigation and are asked if you've ever used any illegal drugs and you say no, but in your criminal record you have a possession charge, that's bad. You're obviously lying, and not even being smart about it. If you say you used to smoke trees every day and are blazed right now, that's bad because you obviously don't give af about laws and stuff (not my opinion, this is the opinion of the Fed that still thinks it's illegal). If you say you used to smoke with your friend for a couple months in college a year ago but stopped and think that was probably a dumb decision, that's not necessarily bad, it all depends on how the interview goes. They'll ask for the names of who you smoked with and how you got the weed - so they can check if you were hanging out with known cartel members or just some other joe schmoe at UCWhatevs.

At the end of the day it's all based on context and a ton of factors. They dig a lot deeper and have a much higher standard for more selective clearances or programs, which shouldn't be a surprise to anyone? But it's all about whether you're trustworthy to keep certain sensitive information from unauthorized people.