Katkat
Back

Count Article Types Viewed

LinkedInData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Count Article Types Viewed evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.

Question

You are given article view events and article metadata.

Table 1: article_views — one row per article view event.

ColumnTypeDescription
user_idINTThe user who viewed the article
article_idINTThe article that was viewed
view_dateDATEUTC calendar date of the view

Table 2: articles — maps each article to its type.

ColumnTypeDescription
article_idINTArticle identifier
article_typeVARCHARThe category/type of the article

Relationship: article_views.article_id = articles.article_id

Assumptions

  • Each row in article_views represents one article view event.
  • view_date is a calendar date; treat dates as UTC and assume no timezone conversion is needed.
  • If a user views multiple articles of the same article_type, that type is counted only once for that user (distinct types).

Write SQL for the following:

  1. Daily distinct types per user. For 2019-01-01, return the number of distinct article types viewed by each user.
  • Output columns: user_id, num_article_types
  1. Histogram across all dates. Build a histogram of the number of distinct article types viewed per user across the full dataset (all available dates). For each possible value of num_article_types, return how many users viewed exactly that many distinct article types.
  • Output columns: num_article_types, num_users