Starting on 'SHA' and going all the way to the post-'Innuendo' sessions, the amount of songs per album he wrote were 1, 1, 1, 2, 2, 2, 2, 1.5, 1, 2, 2*, 0.2, 0.5
* I'm counting 'My Life Has Been Saved' as part of 'The Miracle' since it was written and recorded for that album and first released as a B-Side.
It could be said, then, that his peak was 1980 (four songs, including the band's biggest hit), then he dropped for 'Hot Space' and 'The Works', recovered again for 'Magic' and 'Miracle', opted out again for 'Innuendo' and then did a bit more in the 1991 recordings, on which he was more involved and it was Roger instead who sort of withdrew.
Never having met him, I cannot testify to that, but some people who did know him commented on how uncharacteristic of him was to suddenly take up smoking in 1983. He also claimed a few times that he wasn't at all fond of 'Hot Space' and even claimed it was the worst part of their career, whilst the tour in South America was one of the best and had happened shortly prior to that.
There's a correlation between John's and Brian's input from 'The Miracle' onwards, which might be coincidental (or not): Brian claimed he (Brian) was somewhat absent during 'The Miracle' and did not contribute much (as it happens, he wrote two great songs which were singles, but two songs is half of Brian's usual quota) but then did a lot more for 'Innuendo'. Coincidentally, John was the opposite: a lot of input on 'The Miracle' sessions and very little (creatively speaking) on 'Innuendo'.
After Frederick passed, John and Roger started off the project and their letters to the fan club implied was very much hands-on at that point. Ashley Alexander (who engineered the first post-1991 sessions at Abbey Road, assisting Noel Harris) said John produced some of the keyboard parts Mike Crossley played. When Brian joined the project in '94, John suddenly started going on holidays in France, and later on there were two teams: Brian and Justin working at Brian's hill, and Roger, John and Josh doing their bits a few miles away at Roger's mill.
When tying loose ends at The Town House, Ashley's recollections seem to imply Brian was very involved (even spending an evening with Dave doing the famous 'Track No 13') whilst he rarely mentions John. It was Roger, not Brian, who got in touch with John for 'No-One But You'. As soon as Brian's solo career failed and he suddenly was eager to be part of Queen again (after years of emphatically saying he would not do that), John was out of the picture.
John hated Hot Space. Frederick's favourite singer was not Paul Rodgers. Roger did not compose 'Innuendo.' Witness testimonies are often inaccurate. Scotland's not in England. 'Bo Rhap' hasn't got 180 voices.