Views by Content Type

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L3
Asked in
Tredence

Problem

The content team is prioritizing which formats to promote based on engagement. How many views has each content type received?

Count content views broken down by content type

Tables

content_views

Columns: view_id INTEGER, content_id INTEGER, user_id INTEGER, device_id INTEGER, viewed_at TEXT, watch_seconds INTEGER

content_views: 10 of 200 rows
view_idcontent_iduser_iddevice_idviewed_atwatch_seconds
705337213614042026-02-02 01:07:0041
710650126226072026-03-03 02:14:0072
715963038838102026-04-04 03:21:00103
7212759514410132026-05-05 04:28:00134
7265888640512162026-06-06 05:35:00165
73181017766614192026-07-07 06:42:00196
73711146NULL16222026-08-08 07:49:00227
7424127548818252026-09-09 08:56:00258
747714041749NULL2026-01-10 09:03:00289
75301533301022312026-02-11 10:10:00320

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

content_items: 10 of 200 rows
content_idtitlecontent_typeduration_secondscreator_idpublish_date
243The Ultimate Guide to AI TrendsarticleNULL1972026-02-02
286Top 10 Mobile Appspodcast1542942025-03-03
329Understanding Machine Learning [DRAFT]short2013912026-04-04
372Mastering Tech Startupslivestream2484882025-05-05
415Introduction to Cybersecurityvideo2955852026-06-06
458Deep Dive: Cloud ComputingarticleNULL6822025-07-07
501Behind the Scenes of Product Managementpodcast3897792026-08-08
544Breaking Down DevOpsshort4368762025-09-09
587Exploring Web Developmentlivestream483NULL2026-01-10
630How to Data Sciencevideo53010702025-11-11

Expected output

First 5 of 5 rows
content_typeview_count
article42
livestream47
podcast34
short39
video38

Worked solution and explanation

Why this problem exists in real interviews

This tests a two-table join with GROUP BY on a dimension attribute. Interviewers check whether you can aggregate a fact table by a dimension column that requires a join.

Break down the requirements

Step 1: Join views to content items

JOIN content_items ON content_views.content_id = content_items.content_id enriches each view with the content type.

Step 2: Group by content type and count

GROUP BY content_type with COUNT(*) produces the view count per type.

The solution

Dimension join with grouped count
SELECT ci.content_type, COUNT(*) AS view_count
FROM content_views cv
JOIN content_items ci ON cv.content_id = ci.content_id
GROUP BY ci.content_type

Common follow-up questions