1683. Invalid Tweets

Problem¶

Given a Tweets(tweet_id, content) table, return the tweet_id of every tweet whose content length is strictly greater than 15.

Input:

# Tweets
---------+-------------------
tweet_id | content
---------+-------------------
1        | 'abcdefghijklmno'
2        | 'abcdefghijklmnop'
3        | 'hi there!'

Output:

--------
tweet_id
--------
2

Key trick¶

Use the string-length function and filter with a strict comparison.

Trap¶

Using >= 15 instead of > 15.
Forgetting that spaces count as characters.
Returning content when only tweet_id is required.

Why is it interesting?¶

This is a simple filtering problem that checks whether you know the correct string-length function in SQL and Pandas.

SQL solution¶

SELECT
    tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;

Pandas solution¶

import pandas as pd

def invalid_tweets(tweets: pd.DataFrame) -> pd.DataFrame:
    # str.len() counts characters, including spaces.
    return tweets.loc[tweets["content"].str.len() > 15, ["tweet_id"]]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_QUERY = """
SELECT
    tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;
"""


def invalid_tweets(tweets: pd.DataFrame) -> pd.DataFrame:
    return tweets.loc[tweets["content"].str.len() > 15, ["tweet_id"]]


@pytest.mark.parametrize(
    "rows, expected_ids",
    [
        (
            [
                (1, "Let us Code"),
                (2, "More than fifteen chars are here!"),
            ],
            [2],
        ),
        (
            [
                (1, "abcdefghijklmno"),
                (2, "abcdefghijklmnop"),
                (3, ""),
            ],
            [2],
        ),
        (
            [
                (1, "123456789012345"),
                (2, "123456789012345!"),
                (3, "spaces count ok"),
            ],
            [2],
        ),
        (
            [
                (1, "short"),
                (2, "also short"),
            ],
            [],
        ),
    ],
)
def test_invalid_tweets_sql_and_pandas(rows, expected_ids):
    tweets = pd.DataFrame(rows, columns=["tweet_id", "content"])

    expected = pd.DataFrame({"tweet_id": expected_ids}).astype({"tweet_id": "int64"})

    with sqlite3.connect(":memory:") as conn:
        conn.execute("CREATE TABLE Tweets(tweet_id INTEGER, content TEXT)")
        conn.executemany("INSERT INTO Tweets(tweet_id, content) VALUES (?, ?)", rows)

        sql_result = pd.read_sql_query(SQL_QUERY, conn)

    pandas_result = invalid_tweets(tweets)

    sql_result = sql_result.sort_values("tweet_id").reset_index(drop=True)
    pandas_result = pandas_result.sort_values("tweet_id").reset_index(drop=True)
    expected = expected.sort_values("tweet_id").reset_index(drop=True)

    assert_frame_equal(sql_result, expected)
    assert_frame_equal(pandas_result, expected)

Comment on my solution¶

Your Pandas solution is correct and concise.

It uses the right strict condition.
It returns only tweet_id, as required.
The result order is acceptable because the problem allows any order.

import pandas as pd

def invalid_tweets(tweets: pd.DataFrame) -> pd.DataFrame:
    return tweets.loc[tweets["content"].str.len() > 15, ["tweet_id"]]