Article views ii

# Table: Views
# 
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | article_id    | int     |
# | author_id     | int     |
# | viewer_id     | int     |
# | view_date     | date    |
# +---------------+---------+
# This table may have duplicate rows.
# Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
# Note that equal author_id and viewer_id indicate the same person.
# 
#  
# 
# Write a solution to find all the people who viewed more than one article on the same date.
# 
# Return the result table sorted by id in ascending order.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Views table:
# +------------+-----------+-----------+------------+
# | article_id | author_id | viewer_id | view_date  |
# +------------+-----------+-----------+------------+
# | 1          | 3         | 5         | 2019-08-01 |
# | 3          | 4         | 5         | 2019-08-01 |
# | 1          | 3         | 6         | 2019-08-02 |
# | 2          | 7         | 7         | 2019-08-01 |
# | 2          | 7         | 6         | 2019-08-02 |
# | 4          | 7         | 1         | 2019-07-22 |
# | 3          | 4         | 4         | 2019-07-21 |
# | 3          | 4         | 4         | 2019-07-21 |
# +------------+-----------+-----------+------------+
# Output:
# +------+
# | id   |
# +------+
# | 5    |
# | 6    |
# +------+

import pandas as pd

def article_views(views: pd.DataFrame) -> pd.DataFrame:
  deduped = views.drop_duplicates(subset=['viewer_id', 'article_id', 'view_date'])
  counts = deduped.groupby(['viewer_id', 'view_date'])['article_id'].count().reset_index()
  result = counts[counts['article_id'] > 1][['viewer_id']].drop_duplicates()
  result.columns = ['id']
  return result.sort_values('id').reset_index(drop=True)