Trying to count similar users from sqlite database

Question

I have a table like this:

user_id | subscription_id
-------------------------
1       | 1
1       | 2
2       | 3
2       | 4
3       | 1
3       | 2
4       | 3
5       | 3

What I want to do is count how many users have similar subscriptions:

user_id | same_subscriptions
----------------------------
1       | 1
2       | 0
3       | 1
4       | 1
5       | 1

Is this even possible? How can I achieve this...

Best I managed to do is get a table like this with group_concat:

user_id | subscriptions
-----------------------
1       | 1,2
2       | 3,4
3       | 1,2
4       | 3
5       | 3

This is how I achieved it:

SELECT A.user_id, group_concat(B.subscription_id) 
  FROM Subscriptions A LEFT JOIN Subscriptions B ON 
  A.user_id=B.user_id GROUP BY A.user_id;

Answer 1

The aggregate function GROUP_CONCAT() does not help in this case because in SQLite it does not support an ORDER BY clause, so that a safe comparison can be done.
But you can use GROUP_CONCAT() window function instead:

SELECT user_id, 
       COUNT(*) OVER (PARTITION BY subs) - 1 same_subscriptions
FROM (
  SELECT user_id, 
    GROUP_CONCAT(subscription_id) OVER (PARTITION BY user_id ORDER BY subscription_id) subs,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY subscription_id DESC) rn
  FROM Subscriptions
)
WHERE rn = 1
ORDER BY user_id

See the demo.
Results:

> user_id | same_subscriptions
> ------: | -----------------:
>       1 |                  1
>       2 |                  0
>       3 |                  1
>       4 |                  1
>       5 |                  1