I had the same question, a little searching turned up the answer:
DISTINCTclause must appear immediately after the
… [Y]ou place a column or a list of columns after the
DISTINCTkeyword. If you use one column, SQLite uses values in that column to evaluate the duplicate. [If] you use multiple columns, SQLite uses the combination of values in these columns to evaluate the duplicate.
So in our case, DISTINCT is looking for unique combinations of the 4 columns, not just unique user_id’s.