SELECT
    s.username AS username,
    COUNT(cs.id) AS total_streams,
    CEIL(AVG(cs.duration)) AS avg_stream_duration,
    COUNT(DISTINCT v.id) AS total_viewers,
    COUNT(DISTINCT CASE WHEN cs.duration > 30 THEN v.id END) AS uniq_viewers_gt_30min,
    COUNT(DISTINCT CASE WHEN cs.duration <= 30 THEN v.id END) AS uniq_viewers_lte_30min
FROM streamers AS s
LEFT JOIN completed_streams AS cs ON s.id = cs.streamer_id
LEFT JOIN viewers AS v ON cs.id = v.stream_id
GROUP BY s.username
ORDER BY s.username ASC;