Content Viewer Penetration

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

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
Rockerbox

Problem

What percentage of our registered users have at least one view in content_views? We measure adoption against everyone in users, so accounts that have never watched anything still count toward the total.

What share of the user base has viewed at least one piece of content

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

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

Expected output

First 1 of 1 rows
viewer_pct
43

Worked solution and explanation

This is a set-membership ratio dressed up as a growth metric: of the people in users, how many appear at least once in content_views? Everyone writes a join and a division. What separates candidates is which table the join is anchored on and what gets counted. Anchor on content_views, or inner-join it, and the 114 users who never watched anything drop out of the base, so adoption reads 100%. Count joined rows instead of people and every viewer counts twice, which lands at 60.14%. The real answer, 86 viewers out of 200 users, is 43.0%.

Building it

Step 1: Anchor the base on users

Start from users and LEFT JOIN content_views on user_id. Every registered user survives the join, and the 114 who never viewed come through as one row with a NULL cv.user_id. The 28 view rows with no user_id match nobody, so they cannot inflate anything.

Step 2: Count each viewer once

The join repeats a viewer once per view. On this data every viewer has two rows, 172 in all. COUNT(DISTINCT cv.user_id) collapses them to 86 people, and because COUNT skips NULL, the never-viewers add nothing to the numerator.

Step 3: Count the base as people too

COUNT(*) after the join is 286: 172 view rows plus 114 never-viewer rows. The base is people, so COUNT(DISTINCT u.user_id) gives 200. Multiply by 100.0 before dividing and round to two places, and you get 43.0.

Adoption over the full user base
SELECT ROUND(100.0 * COUNT(DISTINCT cv.user_id) / COUNT(DISTINCT u.user_id), 2) AS viewer_pct
FROM users u
LEFT JOIN content_views cv ON u.user_id = cv.user_id
Inner join
users JOIN content_views keeps only users who have a view. Both counts come out to 86, so the ratio is a flat 100%: a number that looks like a win and means nothing.
Left join from users
users LEFT JOIN content_views keeps all 200 users. The numerator is still 86, the denominator is 200, and the ratio is 43.0%.

Common follow-up questions