Count viewed article types

Q: Count viewed article types

This question evaluates a candidate's competency in SQL data manipulation and analytics, focusing on joins between event and reference tables, distinct-count aggregation, date-based filtering, and building distributional summaries by user.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

You are given two tables about article views.

Table 1: article_views

user_id INT
article_id INT
view_date DATE — UTC calendar date of the view

Table 2: articles

article_id INT
article_type STRING

Relationship:

article_views.article_id = articles.article_id

Assumptions:

Each row in article_views represents one article view event.
Dates are interpreted as UTC calendar dates.

Write SQL for the following:

On 2019-01-01 UTC, compute the number of distinct article types viewed by each user.
- Output columns: user_id, num_article_types
Build a histogram of the number of distinct article types viewed per user across all available dates.
- For each distinct value of num_article_types , return how many users have that count.
- Output columns: num_article_types, num_users

Count viewed article types

Quick Overview

Comments (0)