Here is a step-wise process. Queries were made with Spatialite-gui and visualizations with OpenJUMP.
Take some points into table "points" with an attribute "radius".
Buffer points by taking the radius from an attribute with SQL
CREATE TABLE "buffers" AS
SELECT ST_Buffer("geometry", "radius") as geometry FROM points;
Union the buffer areas with SQL
CREATE TABLE "combined" AS
SELECT ST_Union(geometry) as geometry FROM buffers;
Union is one big multipolygon at this stage. Split it to three distinct geometries with SQL
SELECT ElementaryGeometries('combined', 'geometry', 'elements', 'ID', 'poly_ID');
Have a look at the new table "elements"
The result is close to your sketch, isn't it?
Note: You must execute some additional SQL statements or use the tools of the spatialite-gui besides the ones I have written for registering the geometry columns of the interim tables. I hope my answer is complete enough for a proof of concept.
You can write ST_Union and ST_Buffer into same SQL but it is necessary to create a physical table "combined" and register it into geometry_columns for making ElementaryGeometries function to work.