Drill 1 of 4 in Social media questions
0

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…