Interview Question Bank · CASE inside SUM · HAVING on a ratio
6. Creators who post mostly video
MediumSocial mediaQuestion #256
Concept: CASE inside SUM · HAVING on a ratioshow
To compare a subset with the whole group, count the subset inside the aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END) next to COUNT(*). Filtering the subset in WHERE would remove the other rows before they are counted. Conditions on group totals go in HAVING, and the ratio needs decimal math (100.0 or ::numeric) or a whole-number division rounds it to 0.
Example (different data):
SELECT author_id, COUNT(*) AS reviews, ROUND(100.0 * SUM(CASE WHEN stars = 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS five_star_pct FROM reviews GROUP BY author_id HAVING COUNT(*) >= 10;
Your task
Loopline is inviting video-first creators to a new short-video tab. A creator qualifies with at least 5 posts, more than half of them videos.
Return handle, posts (all their posts), video_posts and video_share_pct (video_posts as a % of posts, rounded to 1 decimal) for every creator with 5 or more posts whose video share is above 50%. Sort by video_share_pct descending, then handle.
Starting Postgres in your browser…
Checking your access…