I think the system that would work best would use both levelling up and levelling down. To use the system proposed, firstly you would have to deal with a loss in the motivation caused by lack of levelling up. Secondly, you would have to contrive a reason for the player to start at a Godlike level then get consistently worse in all ways. While plenty of people have suggested stories where this could be done, many of them look pretty similar (powerful wizard/demigod suffers from a curse and needs to find the cure) and you would be pushed up to come up with more than a few games with this as the premise.
It would be much more flexible to come up with a system where you start as your fairly average man, jack of all trades, and the skills and attributes you use the most improve, the ones you use the least atrophy. This is in no way contrived, it happens naturally; the wizards body gets weaker as he relies on magic, the fighter's mind suffers from lack of use and a few to many clubs to the head. Furthermore, it deals somewhat with the loss in player motivation, as they will be thinking "well, I may lose some of my magic ability this time around, but soon I'll be getting more strength to compensate." Players can also experiment with what skills they would like to develop when they are the jack-of-all-trades, and which ones they find themselves not using much at all.
From a narrative standpoint, it means you can break out of the recurring scenario where a level 1 weakling is deemed best in the land to be the saviour of mankind (the inevitable "chosen one" trope), as instead you are "a talented man who has got an aptitude in combat, magic and sneaking, who just needs to hone his abilities to defeat the big bad at the end". For the dramatic tension Yahtzee talks about, it still holds up. Even though you won't be a complete weaking, you would instead be, say a wizard who has given up almost all his strength and endurance in order to have enough magic to win out in the end, or a fighter who sacrificed the magical abilities which he had developed.
The designers can also be flexible about how quickly the player improves/gets worse, depending on the scenario they want to create. My suggestion may have implied that the player gains 1 point for every one they lose, but they could, say, lose 1 for every 2 (or 3, 4,...) to still get the "classic RPG continually getting stronger" style hero, or lose 2 (or 3, 4,...) for every 1 they gain for the Yahtzee style hero.