Article Views II

Article Views II

What is this problem about?

The Article Views II interview question is an extension of the basic "Article Views" database problem. In this version, you are tasked with finding all users who viewed more than one article on the same date. This Article Views II coding problem requires you to perform multi-column grouping and aggregate filtering to identify high-activity users.

Why is this asked in interviews?

LinkedIn and other data-driven companies use this to test a candidate's proficiency with SQL GROUP BY and HAVING clauses. It evaluates your ability to handle complex conditions involving multiple dimensions (user, article, and date) and ensures you can correctly differentiate between a user viewing the same article multiple times versus distinct articles on the same day.

Algorithmic pattern used

This problem follows the Database interview pattern of "Group-Aggregate-Filter." You group the records by viewer_id and view_date, and then use the COUNT(DISTINCT article_id) function to count how many unique articles each viewer saw on that specific day. Finally, a HAVING clause filters for counts greater than 1.

Example explanation

Imagine the Views table:

• User 1, Date '2023-01-01', Article 10
• User 1, Date '2023-01-01', Article 11 (Match! User 1 viewed 2 unique articles on this date)
• User 2, Date '2023-01-01', Article 10
• User 2, Date '2023-01-01', Article 10 (User 2 viewed the same article twice, count is 1) The query groups by User and Date. For User 1 on '2023-01-01', the distinct article count is 2. For User 2, it is 1. Only User 1 is returned.

Common mistakes candidates make

• Counting Rows vs. Articles: Using COUNT() instead of COUNT(DISTINCT article_id). If a user refreshes the same article twice, COUNT() would be 2, but the user hasn't viewed more than one article.
• Missing Grouping Columns: Forgetting to include view_date in the GROUP BY clause, which would count articles across all time instead of per day.
• Result Sorting: Neglecting to sort the final viewer_id list if the problem specifies a required order.

Interview preparation tip

Whenever you see a requirement like "more than X on the same Y," immediately think of GROUP BY Y combined with a HAVING clause. Practice using COUNT(DISTINCT ...) to handle duplicate entries in log-style data.