paulopinheirocosta

paulopinheirocosta / groups.sql

Created November 8, 2023 00:27 — forked from EcZachly/groups.sql

How to write an algorithm to group people in optimized groups based on timezone and track

	-- first query all the users
	WITH offsets AS (SELECT a.*,
	EXTRACT(hour FROM ptn.utc_offset) AS utc_offset
	FROM bootcamp.attendees a
	JOIN pg_timezone_names ptn ON a.timezone = ptn.name
	WHERE a.bootcamp_version = 3
	AND a.timezone IS NOT NULL
	AND a.content_delivery = 'Live'::text
	),
	-- then aggregate the users by track and offset, we want matching timezones to fill up first