I've been learning SQLite and have had trouble coming up with a satisfactory solution to this query prompt. My strategy involves calculating the complement of users following everyone John Doe follows. However, it is messy and I can't help thinking there must be a cleaner and more simple solution.
Follows(flwer, flwee, date)
Users(usr, name)
where flwer, flwee, and usr are all int user ids and keys
Find the id of users who follow every user followed by John Doe. The match for John Doe should be case-insensitive.
Select flwer from
(
Select * from follows
Except
Select *
From follows f2
Where not exists (
Select flwer, flwee
From follows, users u1
Where flwee = f2.flwee and flwer = u1.usr and upper(u1.name) = upper("John Doe")
)
)
group by flwer having count(*) >= (Select Count(*) From follows, users u1 where flwer = u1.usr and upper(u1.name) = upper("John Doe"))
Any guiding advice on this? In set notation I want all x s.t {x following} is a super sets of {John Doe following}
You could consider using the Count()
Window Function to get the count of a follower's followees for your John Doe count compare. This is similar to the direction you are taking in your SQL.
I'm also encapsulating some of the reusable logic in a CTE to keep the code clean:
WITH JohnDoe AS (
SELECT flwee
FROM Follows
WHERE flwer = (SELECT usr FROM Users WHERE name = 'John Doe')
)
SELECT DISTINCT Users.Name
FROM (
SELECT flwer, flwee, COUNT(flwee) OVER (PARTITION BY flwer) as flwee_count
FROM Follows
WHERE flwee IN (SELECT flwee FROM JohnDoe)
) as sub
INNER JOIN Users
ON sub.flwer = Users.usr
WHERE flwee_count >= (SELECT COUNT(*) FROM JohnDoe);