1148. Article Views I

Problem¶

Given a Views table, return each author who viewed their own article at least once, sorted by author id.

Input:

# Views
-----------+-----------+-----------+------------
article_id | author_id | viewer_id | view_date
-----------+-----------+-----------+------------
1          | 3         | 5         | 2019-08-01
2          | 7         | 7         | 2019-08-01
3          | 4         | 4         | 2019-07-21
3          | 4         | 4         | 2019-07-21

Output:

--
id
--
4
7

Key trick¶

Filter rows where author_id = viewer_id, then deduplicate authors and sort.

Trap¶

Forgetting DISTINCT or drop_duplicates, because the table may contain duplicate rows.
Returning author_id instead of renaming it to id.
Sorting by the wrong column or not sorting at all.

Why is it interesting?¶

It tests basic filtering, deduplication, projection, aliasing, and ordering, which are core SQL and Pandas interview skills.

SQL solution¶

-- SQLite
SELECT DISTINCT
    author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

Pandas solution¶

import pandas as pd

def article_views(views: pd.DataFrame) -> pd.DataFrame:
    # Keep only self-views, deduplicate authors, sort, and expose the required column name.
    return (
        views.loc[views["author_id"].eq(views["viewer_id"]), ["author_id"]]
        .drop_duplicates()
        .rename(columns={"author_id": "id"})
        .sort_values("id")
        .reset_index(drop=True)
    )

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQLITE_QUERY = """
SELECT DISTINCT
    author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;
"""


def article_views(views: pd.DataFrame) -> pd.DataFrame:
    return (
        views.loc[views["author_id"].eq(views["viewer_id"]), ["author_id"]]
        .drop_duplicates()
        .rename(columns={"author_id": "id"})
        .sort_values("id")
        .reset_index(drop=True)
    )


def run_sqlite(rows):
    conn = sqlite3.connect(":memory:")
    conn.execute(
        """
        CREATE TABLE Views (
            article_id INTEGER,
            author_id INTEGER,
            viewer_id INTEGER,
            view_date TEXT
        );
        """
    )
    conn.executemany(
        """
        INSERT INTO Views (article_id, author_id, viewer_id, view_date)
        VALUES (?, ?, ?, ?);
        """,
        rows,
    )

    result = pd.read_sql_query(SQLITE_QUERY, conn)
    conn.close()
    return result


@pytest.mark.parametrize(
    "rows, expected_ids",
    [
        (
            [
                (1, 3, 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"),
            ],
            [4, 7],
        ),
        (
            [
                (1, 1, 1, "2020-01-01"),
                (1, 1, 1, "2020-01-01"),
                (2, 2, 1, "2020-01-02"),
            ],
            [1],
        ),
        (
            [
                (1, 10, 20, "2020-01-01"),
                (2, 20, 10, "2020-01-02"),
            ],
            [],
        ),
        (
            [
                (1, 5, 5, "2020-01-01"),
                (2, 3, 3, "2020-01-02"),
                (3, 4, 4, "2020-01-03"),
            ],
            [3, 4, 5],
        ),
    ],
)
def test_article_views_sqlite_and_pandas(rows, expected_ids):
    expected = pd.DataFrame({"id": expected_ids})

    views = pd.DataFrame(
        rows,
        columns=["article_id", "author_id", "viewer_id", "view_date"],
    )
    views["view_date"] = pd.to_datetime(views["view_date"])

    sql_result = run_sqlite(rows)
    pandas_result = article_views(views)

    pd.testing.assert_frame_equal(sql_result, expected)
    pd.testing.assert_frame_equal(pandas_result, expected)

Comment on my solution¶

Your SQL solution is correct and minimal.

Your Pandas solution is also correct:

It filters self-views properly.
It removes duplicates.
It sorts ascending.
It renames the output column to id.

A small improvement is to add reset_index(drop=True) so the returned DataFrame has a clean index, which is often expected in tests.

SELECT DISTINCT
    author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

import pandas as pd

def article_views(views: pd.DataFrame) -> pd.DataFrame:
    result = (
        views.loc[views["author_id"].eq(views["viewer_id"]),
                  ["author_id"]]
        .drop_duplicates()
        .sort_values("author_id", ascending=True)
        .rename(columns={"author_id": "id"})
    )
    return result