A filtering proxy allows users to remove unwanted stuff from Web pages as they browse them. What "unwanted stuff" is obviously depends on the individual user, but things which are commonly regarded as annoyances include banner ads, user behaviour tracking via cookies, animated pictures, JavaScript, VBScript, ActiveX (dangerous as well as annoying).
Some of those things can be avoided by filtering URIs, which Squid can already do via an external redirect program. Others require a content filter.
Usually, a filtering proxy runs standalone and does nothing but filtering. Users have to configure this proxy in their browsers, and if they use a caching proxy too, chain them after the filter. In situations where the user runs Squid anyway (mostly because of caching for different browsers or a small LAN), it is convenient to build this capability into Squid.