s at time zone 'utc' AS period_start, LEAD(s) OVER ( ORDER BY s ) at time zone 'utc' AS period_end
Maybe doesn't help overall but this can be equivalently written as:
s + '1 day'::interval as period_end
Resorting to a window function here is expensive waste, the lead() value can be computed, not queried.
SELECT p.period_start, p.period_end, COUNT (distinct d.id) FROM periods p LEFT JOIN data d ON d.timestamp >= (p.period_start) AND d."timestamp" < (p.period_end) AND d.sn = 'BLAH'
This seems better written (semantically, not sure about execution dynamics) as:
FROM periods AS p
LEFT JOIN LATERAL (SELECT count(distinct? d.id) FROM data AS d WHERE d.timestamp >= p.period_start AND d.timestamp < p.period_end AND d.sn = 'BLAH') AS cnt_d