Sure, let's break down the problem and determine the SQL logic needed to achieve the desired outcome.
### Step-by-Step Solution:
1. Identify the Users and Articles Liked by Their Followees:
- First, we need a list of articles that are liked by users who are followed by a target user. For example, if user 123 follows user 345, we need to get the articles liked by user 345.
2. Count the Number of Likes for Each Article by Followees:
- Next, we need to count how many times each article has been liked by the users that each user follows. This helps us determine the most popular articles among the followees.
3. Exclude Articles Already Liked by the User:
- We need to make sure that we don't recommend an article that the user has already liked. This requires filtering out those articles from the list.
4. Select the Most Liked Article:
- Lastly, we need to select the article with the highest like count for each user. In case of a tie (i.e., multiple articles have the same like count), choose the article with the smallest ID.
### SQL Query:
Below is the SQL query that achieves this step-by-step procedure:
```sql
WITH articles_liked_by_followees AS (
SELECT
f.this_user_id AS user_id,
al.article_id
FROM
follows f
JOIN article_likes al ON f.follows_this_user_id = al.user_id
),
article_like_counts AS (
SELECT
albf.user_id,
albf.article_id,
COUNT(albf.article_id) AS like_count
FROM
articles_liked_by_followees albf
GROUP BY
albf.user_id,
albf.article_id
)
SELECT
alc.user_id,
alc.article_id
FROM
article_like_counts alc
WHERE
NOT EXISTS (
SELECT 1
FROM article_likes al
WHERE alc.user_id = al.user_id
AND alc.article_id = al.article_id
)
ORDER BY
alc.user_id,
alc.like_count DESC,
alc.article_id ASC
LIMIT 1;
```
### Explanation:
1. Common Table Expression (CTE) - `articles_liked_by_followees`:
- This CTE fetches a list of articles liked by each user's followees. It joins the `follows` table with the `article_likes` table on the condition that `follows_this_user_id` matches `article_likes`.`user_id`.
2. Common Table Expression (CTE) - `article_like_counts`:
- This CTE calculates the number of times each article has been liked by the followees of a user. It groups by `user_id` and `article_id` and counts the occurrences.
3. Main Query - Filter and Select Top Articles:
- The main query filters out articles that have already been liked by the user using a `NOT EXISTS` clause.
- It orders the results by `user_id`, `like_count` in descending order, and `article_id` in ascending order. The `LIMIT 1` ensures that only the top recommended article is selected for each user based on the criteria.
Hence, this SQL query provides the required recommendations following the rules specified.
