Interview Question Bank · COALESCE · NULLIF · safe ratios
1. Engagement rate per post
EasySocial mediaQuestion #123
Concept: COALESCE · NULLIF · safe ratiosshow
Arithmetic with NULL gives NULL: 84 + 12 + NULL is NULL, not 96. When a missing value really means zero, wrap it in COALESCE(column, 0). On the other side of the division, NULLIF(denominator, 0) turns a zero into NULL, so the result is NULL instead of a division-by-zero error.
Example (different data):
SELECT campaign_id, COALESCE(clicks, 0) AS clicks, ROUND(100.0 * COALESCE(clicks, 0) / NULLIF(sends, 0), 2) AS click_pct FROM email_campaigns;
Your task
Creators on Murmurly, a social app, keep reporting blank engagement rates on their dashboard, and one post crashes the report entirely.
Return post_id, creator_id, engagements (likes + comments + shares, where a NULL shares counts as 0) and engagement_rate_pct (engagements as a percentage of impressions, ROUND(..., 2), NULL when impressions is 0). Sort by engagement_rate_pct highest first with NULLs last, then post_id.
Starting Postgres in your browser…
Checking your access…