There is an element of click bait but for many young developers Git is the only VCS they ever knew so Git in the title would give more hints about the article content.
As a younger developer, I agree with you. I've heard of Subversion & Fossil before but know practically nothing about them. By the time I hit the stage of learning version control in my development as a programmer, there was no questioning git would be what I would learn, any discussion I saw of other version control systems was written with a tone that suggested they were antiques to no longer be used. Sort of how psych majors learn about Freud for the historical significance but don't actually use his theories anymore.
Also because of the mention of "fossil" would have meant nothing to these young developers. The use of "git" immediately brings to mind version control and the question: what does sqlite use then?
On the other hand, the internet isn't just for young developers, and even if there are a billion other VCS options to pick from, the ones with the most traction are the most relevant. If you put "I can do Fossil and Subversion" on your CV but everyone is hiring for Git, you're not a great match.
I personally don't like this style of writing and/or discussions either, but wanted to point the fact in most neutral way I can manage.
From my observations, some people think that they can extract more arguments or have a more fruitful discussion by being slightly spiky like that. I do not share this view.
If this title were "SQLite uses Fossil", it'd be more apparently just another ad for Fossil.