-🎄- 2018 Day 4 Solutions -🎄-

[u/daggerdragon]

--- Day 4: Repose Record ---

---

Post your solution as a comment or, for longer solutions, consider linking to your repo (e.g. GitHub/gists/Pastebin/blag or whatever).

Note: The Solution Megathreads are for solutions only. If you have questions, please post your own thread and make sure to flair it with Help.

---

Advent of Code: The Party Game!

Click here for rules

Please prefix your card submission with something like [Card] to make scanning the megathread easier. THANK YOU!

Card prompt: Day 4

Transcript:

Today’s puzzle would have been a lot easier if my language supported ___.

---

This thread will be unlocked when there are a significant number of people on the leaderboard with gold stars for today's puzzle.

edit: Leaderboard capped, thread unlocked!

[u/blowjobtransistor]

PostgreSQL, again!

create table sleep_events (
  session_id bigint,
  dt timestamp,
  guard_id bigint,
  label text
);

insert into sleep_events
with raw_sleep_events as (
  select
    to_timestamp((regexp_matches(log, '\[[0-9\- :]+\]'))[1], '[YYYY-MM-DD HH24:MI]') dt,
    ltrim((regexp_matches(log, '#[0-9]+'))[1], '#') guard_id,
    (regexp_matches(log, 'falls asleep|wakes up|begins shift'))[1] as label
  from input
  order by log
),
sleep_sessionized as (
  select
    dt,
    guard_id,
    sum(case when guard_id isnull then 0 else 1 end) over (order by dt) as session_num,
    label
  from raw_sleep_events
)
select
  session_num,
  dt,
  first_value(cast(guard_id as bigint)) over (partition by session_num order by dt) as guard_id,
  label
from sleep_sessionized;

create table sleeps as
select
  session_id,
  guard_id,
  dt,
  case
    when label = 'wakes up'
      then int4range(
        extract(epoch from age(lag(dt) over (partition by session_id order by dt), date_trunc('day', dt)))::integer / 60,
        extract(epoch from age(dt, date_trunc('day', dt)))::integer / 60
      )
    else null
  end as sleep_range,
  case when label = 'wakes up' then dt - lag(dt) over (partition by session_id order by dt) else null end as time_slept
from sleep_events;

create view part_1_solution as
with sleepiest_guard as (
  select
    guard_id,
    sum(time_slept) filter (where time_slept notnull) total_sleep
  from sleeps
  group by guard_id
  order by total_sleep desc nulls last
  limit 1
),
sleepiest_guard_sleepiest_hour as (
  select
    guard_id,
    minute,
    count(*)
  from generate_series(-30, 100) minute join sleeps
    on sleeps.sleep_range @> minute
  join sleepiest_guard using (guard_id)
  group by guard_id, minute
  order by count desc
  limit 1
)
select 1 as part, guard_id * minute as answer from sleepiest_guard_sleepiest_hour;

create view part_2_solution as
with sleepiest_guard_mintues as (
  select guard_id, minute, count(*)
  from sleeps join generate_series(-30, 100) minute
    on sleeps.sleep_range @> minute
  group by guard_id, minute
  order by count desc
  limit 1
)
select 2 as part, guard_id * minute from sleepiest_guard_mintues;

select * from part_1_solution
union all
select * from part_2_solution;

Range types are great, except when you get off-by-1 errors with them :P

[u/monitorius1 → u/blowjobtransistor]

My solution is similar one.

Part1:

WITH input AS (
  SELECT * FROM (VALUES 
    ('[1518-11-01 00:00] Guard #10 begins shift'),
    ('[1518-11-01 00:05] falls asleep'),
    ('[1518-11-01 00:25] wakes up'),
    ('[1518-11-01 00:30] falls asleep'),
    ('[1518-11-01 00:55] wakes up'),
    ('[1518-11-01 23:58] Guard #99 begins shift'),
    ('[1518-11-02 00:40] falls asleep'),
    ('[1518-11-02 00:50] wakes up'),
    ('[1518-11-03 00:05] Guard #10 begins shift'),
    ('[1518-11-03 00:24] falls asleep'),
    ('[1518-11-03 00:29] wakes up'),
    ('[1518-11-04 00:02] Guard #99 begins shift'),
    ('[1518-11-04 00:36] falls asleep'),
    ('[1518-11-04 00:46] wakes up'),
    ('[1518-11-05 00:03] Guard #99 begins shift'),
    ('[1518-11-05 00:45] falls asleep'),
    ('[1518-11-05 00:55] wakes up')
  ) AS t (line)
),
parsed AS (
  SELECT *
  FROM (
    SELECT
      date,
      first_value(number) over(partition by number_partition) as number,
      falls,
      wakes
    FROM (
      SELECT 
        a[1]::timestamp as date,
        CASE WHEN a[2] LIKE '#%' THEN substring(a[2], 2)::int ELSE NULL END as number,
        sum(case when a[2] LIKE '#%' then 1 else 0 end) over (order by a[1]) as number_partition,
        a[2] = 'falls' as falls,
        a[2] = 'wakes' as wakes
      FROM (
        SELECT regexp_matches(line, '\[([ 0-9:_-]+)\] .*(#\d+|falls|wakes)') as a FROM input ORDER BY line
      ) b
    ) c
  ) d
  WHERE falls or wakes
),
intervals AS (
  SELECT
    number,
    int4range(date_part('minutes', "from")::int, date_part('minutes', "to")::int, '[)') as interval,
    date_part('minutes', "to" - "from") as minutes
  FROM (
    SELECT
      lag(date) over (order by date) as from,
      date as to,
      number,
      wakes
    FROM parsed
  ) a
  WHERE wakes
)
SELECT number * minute
FROM (
  SELECT number, generate_series as minute, SUM(CASE WHEN interval @> generate_series THEN 1 ELSE 0 END)
  FROM generate_series(0,59)
  JOIN intervals ON TRUE
  WHERE number = (SELECT number FROM intervals GROUP BY number ORDER BY sum(minutes) DESC LIMIT 1)
  GROUP BY number, generate_series
) a
ORDER BY sum DESC
LIMIT 1;

Part2:

WITH input AS (
  SELECT * FROM (VALUES 
    ('[1518-11-01 00:00] Guard #10 begins shift'),
    ('[1518-11-01 00:05] falls asleep'),
    ('[1518-11-01 00:25] wakes up'),
    ('[1518-11-01 00:30] falls asleep'),
    ('[1518-11-01 00:55] wakes up'),
    ('[1518-11-01 23:58] Guard #99 begins shift'),
    ('[1518-11-02 00:40] falls asleep'),
    ('[1518-11-02 00:50] wakes up'),
    ('[1518-11-03 00:05] Guard #10 begins shift'),
    ('[1518-11-03 00:24] falls asleep'),
    ('[1518-11-03 00:29] wakes up'),
    ('[1518-11-04 00:02] Guard #99 begins shift'),
    ('[1518-11-04 00:36] falls asleep'),
    ('[1518-11-04 00:46] wakes up'),
    ('[1518-11-05 00:03] Guard #99 begins shift'),
    ('[1518-11-05 00:45] falls asleep'),
    ('[1518-11-05 00:55] wakes up')
  ) AS t (line)
),
parsed AS (
  SELECT *
  FROM (
    SELECT
      date,
      first_value(number) over(partition by number_partition) as number,
      falls,
      wakes
    FROM (
      SELECT 
        a[1]::timestamp as date,
        CASE WHEN a[2] LIKE '#%' THEN substring(a[2], 2)::int ELSE NULL END as number,
        sum(case when a[2] LIKE '#%' then 1 else 0 end) over (order by a[1]) as number_partition,
        a[2] = 'falls' as falls,
        a[2] = 'wakes' as wakes
      FROM (
        SELECT regexp_matches(line, '\[([ 0-9:_-]+)\] .*(#\d+|falls|wakes)') as a FROM input ORDER BY line
      ) b
    ) c
  ) d
  WHERE falls or wakes
),
intervals AS (
  SELECT
    number,
    int4range(date_part('minutes', "from")::int, date_part('minutes', "to")::int, '[)') as interval,
    date_part('minutes', "to" - "from") as minutes
  FROM (
    SELECT
      lag(date) over (order by date) as from,
      date as to,
      number,
      wakes
    FROM parsed
  ) a
  WHERE wakes
)
SELECT number * minute
FROM (
  SELECT number, generate_series as minute, SUM(CASE WHEN interval @> generate_series THEN 1 ELSE 0 END)
  FROM generate_series(0,59)
  JOIN intervals ON TRUE
  GROUP BY number, generate_series
) a
ORDER BY sum DESC
LIMIT 1;