Device Status without Timestamps
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Table: article_views
article_id INT
author_id INT
viewer_id INT
view_dt DATE
Count authors who have never viewed any of their own articles.
On 2024‑11‑11, how many members viewed more than one distinct article?
Overview: This question evaluates data manipulation and behavioral analytics competencies, emphasizing aggregation, deduplication, distinct counts, and date-based filtering to measure author and viewer activity.
Authors never self-viewed
Using the article_views table, count the number of distinct authors who have at least one article view record but have never viewed any of their own articles (i.e., there is no row where viewer_id = author_id for that author). Return a single row with this count.
Tables
article_views(article_id INT, author_id INT, viewer_id INT, view_dt DATE)
Hints
- Start from the set of distinct author_id values in article_views.
- Use NOT EXISTS to exclude authors who have any row where viewer_id equals author_id.
Multi-article viewers on specific date
Using the article_views table, on the date 2024-11-11, count how many distinct viewers (viewer_id) viewed more than one distinct article. Return a single row with this count.
Tables
article_views(article_id INT, author_id INT, viewer_id INT, view_dt DATE)
Hints
- Filter article_views to rows where view_dt = DATE '2024-11-11' before aggregating.
- Group by viewer_id and use HAVING COUNT(DISTINCT article_id) > 1 to keep only multi-article viewers.