Drill 6 of 8 in Social media questions
0

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…