The downside of that is that all code is in the header. As in you will compile all the library code whenever you include the header. As third-party library code is something you probably seldom change yourself, you don't want your build to spend time building it all the time.

Sure some of those libs may have some define you need to set in one of your .c files so the implementation will go there but then whenever you change that file it will result in a recompile of the third-party lib. So then you go and create a separate .c file just for the lib and we're back to why the lib author didn't do that in the first place?

No, I think what sqlite does is pretty neat. If you're doing development in the sqlite code-base you have lots of files (so you can manage it) but if you just want to use sqlite, you have the amalgamation which is one .c and one .h.