sql

🔷 SQL: Conditional Aggregation (CASE WHEN)

Replace multiple queries with one:

-- Click-through rate per app
SELECT app_id,
  ROUND(100.0 *
    SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0)
  , 2) AS ctr
FROM events
GROUP BY app_id;

Also useful for pivoting:

SELECT user_id,
  SUM(CASE WHEN platform = 'ios' THEN sessions ELSE 0 END) AS ios_sessions,
  SUM(CASE WHEN platform = 'android' THEN sessions ELSE 0 END) AS android_sessions
FROM user_activity
GROUP BY user_id;