I'm a teacher, worked in 4 schools and have experience of others through my training etc.
First school "good"- I actually think it was an excellent school which suffered from being a non-grammar school in city with some good grammars. Probably, overall, good was a fair judgement- the school was improving from some past issues. I really liked it though! SEN support seemed very good.
Second school "outstanding"- a historical judgement from 2010, I worked there in 2018/2019. Worst school I have been in for behaviour of students, very affluent catchment which ensured headline results were good. Support for some students with SEN seemed very poor. This is not their fault but the school buildings also felt very crowded and poor corridor behaviour probably made it feel worse! Most of the staff working there were new since the OFSTED judgement. It wasn't the same school as had received that judgement.
For interest, they had a recent Ofsted, which rated them "good" and commented on a dip in progress and recent changes to the behaviour system. I think this Ofsted is probably fair BUT I think if their results were lower, they would have probably got "requires improvement".
Third school "inadequate"- This is a bit of a weird one, because if you read the report, most areas of the school were "good" but one specific aspect of safeguarding was not up to scratch, so therefore only the inadequate judgement could be given- which is reasonable, because safeguarding is very important.
When I first joined the school, it was a good place to work, staff were mainly happy, kids were mainly happy, results were good given the very mixed catchment. But it was taken over by a MAT as a result of the judgement, and they went with a bit of a broom sweeps clean approach, which the school possibly did not need. Being taken over by a MAT also impacted our budget and we lost support staff, I'd say SEN provision became worse, over time the new MAT approach wore staff down, lots of staff left, behaviour got worse and it became a bit of a vicious cycle. By the time I left, it was not a nice place to work and a lot of the students were unhappy. It's very much the opposite of the Mumsnet narrative that inadequate schools get loads of help and money to improve.
Current school- "outstanding". This is a judgement from 3 years ago and the school is in many ways very good. Results are great although the school is also very good at advising students on "suitable" courses. It's not selective, but certain courses within the school are. Pastoral care is very good, SEN support is less so, but only because of numbers- the school has grown a lot since the outstanding judgement and the SEN team hasn't grown with it. The support for students with existing diagnosis/ECHP is good, but getting new support for a student can be an issue.
I do think, because of the judgement, the school has been through some somewhat unsustainable growth- it was oversubscribed for the first time this September (location means this wouldn't normally happen) so space etc is an issue, and we'll likely be oversubscribed again next September. If this continues to happen it could put pressure on the school. We also have students with increasingly long commutes, and ironically this means they can't take part in all the good things the school offers.
Anyway, I do think it's worth bearing in mind Ofsted do provide more than a one word judgement. It's better to read the whole report (and note the date) than just base your opinion of a school on one word.