Practical SQL Puzzles That Will Level Up Your Skill

Stay Ahead, Stay ONMINE

Practical SQL Puzzles That Will Level Up Your Skill

There are some Sql patterns that, once you know them, you start seeing them everywhere. The solutions to the puzzles that I will show you today are actually very simple SQL queries, but understanding the concept behind them will surely unlock new solutions to the queries you write on a day-to-day basis. These challenges are all based on real-world scenarios, as over the past few months I made a point of writing down every puzzle-like query that I had to build. I also encourage you to try them for yourself, so that you can challenge yourself first, which will improve your learning! All queries to generate the datasets will be provided in a PostgreSQL and DuckDB-friendly syntax, so that you can easily copy and play with them. At the end I will also provide you a link to a GitHub repo containing all the code, as well as the answer to the bonus challenge I will leave for you! I organized these puzzles in order of increasing difficulty, so, if you find the first ones too easy, at least take a look at the last one, which uses a technique that I truly believe you won’t have seen before. Okay, let’s get started. I love this puzzle because of how short and simple the final query is, even though it deals with many edge cases. The data for this challenge shows tickets moving in between Kanban stages, and the objective is to find how long, on average, tickets stay in the Doing stage. The data contains the ID of the ticket, the date the ticket was created, the date of the move, and the “from” and “to” stages of the move. The stages present are New, Doing, Review, and Done. Some things you need to know (edge cases): Tickets can move backwards, meaning tickets can go back to the Doing stage. You should not include tickets that are still stuck in the Doing stage, as there is no way to know how long they will stay there for. Tickets are not always created in the New stage. “`SQL CREATE TABLE ticket_moves ( ticket_id INT NOT NULL, create_date DATE NOT NULL, move_date DATE NOT NULL, from_stage TEXT NOT NULL, to_stage TEXT NOT NULL ); “` “`SQL INSERT INTO ticket_moves (ticket_id, create_date, move_date, from_stage, to_stage) VALUES — Ticket 1: Created in “New”, then moves to Doing, Review, Done. (1, ‘2024-09-01’, ‘2024-09-03’, ‘New’, ‘Doing’), (1, ‘2024-09-01’, ‘2024-09-07’, ‘Doing’, ‘Review’), (1, ‘2024-09-01’, ‘2024-09-10’, ‘Review’, ‘Done’), — Ticket 2: Created in “New”, then moves: New → Doing → Review → Doing again → Review. (2, ‘2024-09-05’, ‘2024-09-08’, ‘New’, ‘Doing’), (2, ‘2024-09-05’, ‘2024-09-12’, ‘Doing’, ‘Review’), (2, ‘2024-09-05’, ‘2024-09-15’, ‘Review’, ‘Doing’), (2, ‘2024-09-05’, ‘2024-09-20’, ‘Doing’, ‘Review’), — Ticket 3: Created in “New”, then moves to Doing. (Edge case: no subsequent move from Doing.) (3, ‘2024-09-10’, ‘2024-09-16’, ‘New’, ‘Doing’), — Ticket 4: Created already in “Doing”, then moves to Review. (4, ‘2024-09-15’, ‘2024-09-22’, ‘Doing’, ‘Review’); “` A summary of the data: Ticket 1: Created in the New stage, moves normally to Doing, then Review, and then Done. Ticket 2: Created in New, then moves: New → Doing → Review → Doing again → Review. Ticket 3: Created in New, moves to Doing, but it is still stuck there. Ticket 4: Created in the Doing stage, moves to Review afterward. It might be a good idea to stop for a bit and think how you would deal with this. Can you find out how long a ticket stays on a single stage? Honestly, this sounds intimidating at first, and it looks like it will be a nightmare to deal with all the edge cases. Let me show you the full solution to the problem, and then I will explain what is happening afterward. “`SQL WITH stage_intervals AS ( SELECT ticket_id, from_stage, move_date – COALESCE( LAG(move_date) OVER ( PARTITION BY ticket_id ORDER BY move_date ), create_date ) AS days_in_stage FROM ticket_moves ) SELECT SUM(days_in_stage) / COUNT(DISTINCT ticket_id) as avg_days_in_doing FROM stage_intervals WHERE from_stage = ‘Doing’; “` The first CTE uses the LAG function to find the previous move of the ticket, which will be the time the ticket entered that stage. Calculating the duration is as simple as subtracting the previous date from the move date. What you should notice is the use of the COALESCE in the previous move date. What that does is that if a ticket doesn’t have a previous move, then it uses the date of creation of the ticket. This takes care of the cases of tickets being created directly into the Doing stage, as it still will properly calculate the time it took to leave the stage. This is the result of the first CTE, showing the time spent in each stage. Notice how the Ticket 2 has two entries, as it visited the Doing stage in two separate occasions. With this done, it’s just a matter of getting the average as the SUM of total days spent in doing, divided by the distinct number of tickets that ever left the stage. Doing it this way, instead of simply using the AVG, makes sure that the two rows for Ticket 2 get properly accounted for as a single ticket. Not so bad, right? The goal of this second challenge is to find the most recent contract sequence of every employee. A break of sequence happens when two contracts have a gap of more than one day between them. In this dataset, there are no contract overlaps, meaning that a contract for the same employee either has a gap or ends a day before the new one starts. “`SQL CREATE TABLE contracts ( contract_id integer PRIMARY KEY, employee_id integer NOT NULL, start_date date NOT NULL, end_date date NOT NULL ); INSERT INTO contracts (contract_id, employee_id, start_date, end_date) VALUES — Employee 1: Two continuous contracts (1, 1, ‘2024-01-01’, ‘2024-03-31’), (2, 1, ‘2024-04-01’, ‘2024-06-30’), — Employee 2: One contract, then a gap of three days, then two contracts (3, 2, ‘2024-01-01’, ‘2024-02-15’), (4, 2, ‘2024-02-19’, ‘2024-04-30’), (5, 2, ‘2024-05-01’, ‘2024-07-31’), — Employee 3: One contract (6, 3, ‘2024-03-01’, ‘2024-08-31’); “` As a summary of the data: Employee 1: Has two continuous contracts. Employee 2: One contract, then a gap of three days, then two contracts. Employee 3: One contract. The expected result, given the dataset, is that all contracts should be included except for the first contract of Employee 2, which is the only one that has a gap. Before explaining the logic behind the solution, I would like you to think about what operation can be used to join the contracts that belong to the same sequence. Focus only on the second row of data, what information do you need to know if this contract was a break or not? I hope it’s clear that this is the perfect situation for window functions, again. They are incredibly useful for solving problems like this, and understanding when to use them helps a lot in finding clean solutions to problems. First thing to do, then, is to get the end date of the previous contract for the same employee with the LAG function. Doing that, it’s simple to compare both dates and check if it was a break of sequence. “`SQL WITH ordered_contracts AS ( SELECT *, LAG(end_date) OVER (PARTITION BY employee_id ORDER BY start_date) AS previous_end_date FROM contracts ), gapped_contracts AS ( SELECT *, — Deals with the case of the first contract, which won’t have — a previous end date. In this case, it’s still the start of a new — sequence. CASE WHEN previous_end_date IS NULL OR previous_end_date < start_date – INTERVAL '1 day' THEN 1 ELSE 0 END AS is_new_sequence FROM ordered_contracts ) SELECT * FROM gapped_contracts ORDER BY employee_id ASC; “` An intuitive way to continue the query is to number the sequences of each employee. For example, an employee who has no gap, will always be on his first sequence, but an employee who had 5 breaks in contracts will be on his 5th sequence. Funnily enough, this is done by another window function. “`SQL — — Previous CTEs — sequences AS ( SELECT *, SUM(is_new_sequence) OVER (PARTITION BY employee_id ORDER BY start_date) AS sequence_id FROM gapped_contracts ) SELECT * FROM sequences ORDER BY employee_id ASC; “` Notice how, for Employee 2, he starts his sequence #2 after the first gapped value. To finish this query, I grouped the data by employee, got the value of their most recent sequence, and then did an inner join with the sequences to keep only the most recent one. “`SQL — — Previous CTEs — max_sequence AS ( SELECT employee_id, MAX(sequence_id) AS max_sequence_id FROM sequences GROUP BY employee_id ), latest_contract_sequence AS ( SELECT c.contract_id, c.employee_id, c.start_date, c.end_date FROM sequences c JOIN max_sequence m ON c.sequence_id = m.max_sequence_id AND c.employee_id = m.employee_id ORDER BY c.employee_id, c.start_date ) SELECT * FROM latest_contract_sequence; “` As expected, our final result is basically our starting query just with the first contract of Employee 2 missing! Finally, the last puzzle — I’m glad you made it this far. For me, this is the most mind-blowing one, as when I first encountered this problem I thought of a completely different solution that would be a mess to implement in SQL. For this puzzle, I’ve changed the context from what I had to deal with for my job, as I think it will make it easier to explain. Imagine you’re a data analyst at an event venue, and you’re analyzing the talks scheduled for an upcoming event. You want to find the time of day where there will be the highest number of talks happening at the same time. This is what you should know about the schedules: Rooms are booked in increments of 30min, e.g. from 9h-10h30. The data is clean, there are no overbookings of meeting rooms. There can be back-to-back meetings in a single meeting room. Meeting schedule visualized (this is the actual data). “`SQL CREATE TABLE meetings ( room TEXT NOT NULL, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL ); INSERT INTO meetings (room, start_time, end_time) VALUES — Room A meetings ('Room A', '2024-10-01 09:00', '2024-10-01 10:00'), ('Room A', '2024-10-01 10:00', '2024-10-01 11:00'), ('Room A', '2024-10-01 11:00', '2024-10-01 12:00'), — Room B meetings ('Room B', '2024-10-01 09:30', '2024-10-01 11:30'), — Room C meetings ('Room C', '2024-10-01 09:00', '2024-10-01 10:00'), ('Room C', '2024-10-01 11:30', '2024-10-01 12:00'); “` The way to solve this is using what is called a Sweep Line Algorithm, or also known as an event-based solution. This last name actually helps to understand what will be done, as the idea is that instead of dealing with intervals, which is what we have in the original data, we deal with events instead. To do this, we need to transform every row into two separate events. The first event will be the Start of the meeting, and the second event will be the End of the meeting. “`SQL WITH events AS ( — Create an event for the start of each meeting (+1) SELECT start_time AS event_time, 1 AS delta FROM meetings UNION ALL — Create an event for the end of each meeting (-1) SELECT — Small trick to work with the back-to-back meetings (explained later) end_time – interval '1 minute' as end_time, -1 AS delta FROM meetings ) SELECT * FROM events; “` Take the time to understand what is happening here. To create two events from a single row of data, we’re simply unioning the dataset on itself; the first half uses the start time as the timestamp, and the second part uses the end time. You might already notice the delta column created and see where this is going. When an event starts, we count it as +1, when it ends, we count it as -1. You might even be already thinking of another window function to solve this, and you’re actually right! But before that, let me just explain the trick I used in the end dates. As I don’t want back-to-back meetings to count as two concurrent meetings, I’m subtracting a single minute of every end date. This way, if a meeting ends and another starts at 10h30, it won’t be assumed that two meetings are concurrently happening at 10h30. Okay, back to the query and yet another window function. This time, though, the function of choice is a rolling SUM. “`SQL — — Previous CTEs — ordered_events AS ( SELECT event_time, delta, SUM(delta) OVER (ORDER BY event_time, delta DESC) AS concurrent_meetings FROM events ) SELECT * FROM ordered_events ORDER BY event_time DESC; “` The rolling SUM at the Delta column is essentially walking down every record and finding how many events are active at that time. For example, at 9 am sharp, it sees two events starting, so it marks the number of concurrent meetings as two! When the third meeting starts, the count goes up to three. But when it gets to 9h59 (10 am), then two meetings end, bringing the counter back to one. With this data, the only thing missing is to find when the highest value of concurrent meetings happens. “`SQL — — Previous CTEs — max_events AS ( — Find the maximum concurrent meetings value SELECT event_time, concurrent_meetings, RANK() OVER (ORDER BY concurrent_meetings DESC) AS rnk FROM ordered_events ) SELECT event_time, concurrent_meetings FROM max_events WHERE rnk = 1; “` That’s it! The interval of 9h30–10h is the one with the largest number of concurrent meetings, which checks out with the schedule visualization above! This solution looks incredibly simple in my opinion, and it works for so many situations. Every time you are dealing with intervals now, you should think if the query wouldn’t be easier if you thought about it in the perspective of events. But before you move on, and to really nail down this concept, I want to leave you with a bonus challenge, which is also a common application of the Sweep Line Algorithm. I hope you give it a try! Bonus challenge The context for this one is still the same as the last puzzle, but now, instead of trying to find the period when there are most concurrent meetings, the objective is to find bad scheduling. It seems that there are overlaps in the meeting rooms, which need to be listed so it can be fixed ASAP. How would you find out if the same meeting room has two or more meetings booked at the same time? Here are some tips on how to solve it: It’s still the same algorithm. This means you will still do the UNION, but it will look slightly different. You should think in the perspective of each meeting room. You can use this data for the challenge: “`SQL CREATE TABLE meetings_overlap ( room TEXT NOT NULL, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL ); INSERT INTO meetings_overlap (room, start_time, end_time) VALUES — Room A meetings ('Room A', '2024-10-01 09:00', '2024-10-01 10:00'), ('Room A', '2024-10-01 10:00', '2024-10-01 11:00'), ('Room A', '2024-10-01 11:00', '2024-10-01 12:00'), — Room B meetings ('Room B', '2024-10-01 09:30', '2024-10-01 11:30'), — Room C meetings ('Room C', '2024-10-01 09:00', '2024-10-01 10:00'), — Overlaps with previous meeting. ('Room C', '2024-10-01 09:30', '2024-10-01 12:00'); “` If you’re interested in the solution to this puzzle, as well as the rest of the queries, check this GitHub repo. The first takeaway from this blog post is that window functions are overpowered. Ever since I got more comfortable with using them, I feel that my queries have gotten so much simpler and easier to read, and I hope the same happens to you. If you’re interested in learning more about them, you would probably enjoy reading this other blog post I’ve written, where I go over how you can understand and use them effectively. The second takeaway is that these patterns used in the challenges really do happen in many other places. You might need to find sequences of subscriptions, customer retention, or you might need to find overlap of tasks. There are many situations when you will need to use window functions in a very similar fashion to what was done in the puzzles. The third thing I want you to remember is about this solution to using events besides dealing with intervals. I’ve looked at some problems I solved a long time ago that I could’ve used this pattern on to make my life easier, and unfortunately, I didn’t know about it at the time. I really do hope you enjoyed this post and gave a shot to the puzzles yourself. And I’m sure that if you made it this far, you either learned something new about SQL or strengthened your knowledge of window functions! Thank you so much for reading. If you have questions or just want to get in touch with me, don’t hesitate to contact me at mtrentz.com. All images by the author unless stated otherwise.

These challenges are all based on real-world scenarios, as over the past few months I made a point of writing down every puzzle-like query that I had to build. I also encourage you to try them for yourself, so that you can challenge yourself first, which will improve your learning!

All queries to generate the datasets will be provided in a PostgreSQL and DuckDB-friendly syntax, so that you can easily copy and play with them. At the end I will also provide you a link to a GitHub repo containing all the code, as well as the answer to the bonus challenge I will leave for you!

I organized these puzzles in order of increasing difficulty, so, if you find the first ones too easy, at least take a look at the last one, which uses a technique that I truly believe you won’t have seen before.

Okay, let’s get started.

I love this puzzle because of how short and simple the final query is, even though it deals with many edge cases. The data for this challenge shows tickets moving in between Kanban stages, and the objective is to find how long, on average, tickets stay in the Doing stage.

The data contains the ID of the ticket, the date the ticket was created, the date of the move, and the “from” and “to” stages of the move. The stages present are New, Doing, Review, and Done.

Some things you need to know (edge cases):

Tickets can move backwards, meaning tickets can go back to the Doing stage.
You should not include tickets that are still stuck in the Doing stage, as there is no way to know how long they will stay there for.
Tickets are not always created in the New stage.

```SQL

CREATE TABLE ticket_moves (
    ticket_id INT NOT NULL,
    create_date DATE NOT NULL,
    move_date DATE NOT NULL,
    from_stage TEXT NOT NULL,
    to_stage TEXT NOT NULL
);

```

```SQL

INSERT INTO ticket_moves (ticket_id, create_date, move_date, from_stage, to_stage)
    VALUES
        -- Ticket 1: Created in "New", then moves to Doing, Review, Done.
        (1, '2024-09-01', '2024-09-03', 'New', 'Doing'),
        (1, '2024-09-01', '2024-09-07', 'Doing', 'Review'),
        (1, '2024-09-01', '2024-09-10', 'Review', 'Done'),
        -- Ticket 2: Created in "New", then moves: New → Doing → Review → Doing again → Review.
        (2, '2024-09-05', '2024-09-08', 'New', 'Doing'),
        (2, '2024-09-05', '2024-09-12', 'Doing', 'Review'),
        (2, '2024-09-05', '2024-09-15', 'Review', 'Doing'),
        (2, '2024-09-05', '2024-09-20', 'Doing', 'Review'),
        -- Ticket 3: Created in "New", then moves to Doing. (Edge case: no subsequent move from Doing.)
        (3, '2024-09-10', '2024-09-16', 'New', 'Doing'),
        -- Ticket 4: Created already in "Doing", then moves to Review.
        (4, '2024-09-15', '2024-09-22', 'Doing', 'Review');
```

A summary of the data:

Ticket 1: Created in the New stage, moves normally to Doing, then Review, and then Done.
Ticket 2: Created in New, then moves: New → Doing → Review → Doing again → Review.
Ticket 3: Created in New, moves to Doing, but it is still stuck there.
Ticket 4: Created in the Doing stage, moves to Review afterward.

It might be a good idea to stop for a bit and think how you would deal with this. Can you find out how long a ticket stays on a single stage?

Honestly, this sounds intimidating at first, and it looks like it will be a nightmare to deal with all the edge cases. Let me show you the full solution to the problem, and then I will explain what is happening afterward.

```SQL

WITH stage_intervals AS (
    SELECT
        ticket_id,
        from_stage,
        move_date 
        - COALESCE(
            LAG(move_date) OVER (
                PARTITION BY ticket_id 
                ORDER BY move_date
            ), 
            create_date
        ) AS days_in_stage
    FROM
        ticket_moves
)
SELECT
    SUM(days_in_stage) / COUNT(DISTINCT ticket_id) as avg_days_in_doing
FROM
    stage_intervals
WHERE
    from_stage = 'Doing';
```

The first CTE uses the LAG function to find the previous move of the ticket, which will be the time the ticket entered that stage. Calculating the duration is as simple as subtracting the previous date from the move date.

What you should notice is the use of the COALESCE in the previous move date. What that does is that if a ticket doesn’t have a previous move, then it uses the date of creation of the ticket. This takes care of the cases of tickets being created directly into the Doing stage, as it still will properly calculate the time it took to leave the stage.

This is the result of the first CTE, showing the time spent in each stage. Notice how the Ticket 2 has two entries, as it visited the Doing stage in two separate occasions.

With this done, it’s just a matter of getting the average as the SUM of total days spent in doing, divided by the distinct number of tickets that ever left the stage. Doing it this way, instead of simply using the AVG, makes sure that the two rows for Ticket 2 get properly accounted for as a single ticket.

Not so bad, right?

The goal of this second challenge is to find the most recent contract sequence of every employee. A break of sequence happens when two contracts have a gap of more than one day between them.

In this dataset, there are no contract overlaps, meaning that a contract for the same employee either has a gap or ends a day before the new one starts.

```SQL
CREATE TABLE contracts (
    contract_id integer PRIMARY KEY,
    employee_id integer NOT NULL,
    start_date date NOT NULL,
    end_date date NOT NULL
);

INSERT INTO contracts (contract_id, employee_id, start_date, end_date)
VALUES 
    -- Employee 1: Two continuous contracts
    (1, 1, '2024-01-01', '2024-03-31'),
    (2, 1, '2024-04-01', '2024-06-30'),
    -- Employee 2: One contract, then a gap of three days, then two contracts
    (3, 2, '2024-01-01', '2024-02-15'),
    (4, 2, '2024-02-19', '2024-04-30'),
    (5, 2, '2024-05-01', '2024-07-31'),
    -- Employee 3: One contract
    (6, 3, '2024-03-01', '2024-08-31');
```

As a summary of the data:

Employee 1: Has two continuous contracts.
Employee 2: One contract, then a gap of three days, then two contracts.
Employee 3: One contract.

The expected result, given the dataset, is that all contracts should be included except for the first contract of Employee 2, which is the only one that has a gap.

Before explaining the logic behind the solution, I would like you to think about what operation can be used to join the contracts that belong to the same sequence. Focus only on the second row of data, what information do you need to know if this contract was a break or not?

I hope it’s clear that this is the perfect situation for window functions, again. They are incredibly useful for solving problems like this, and understanding when to use them helps a lot in finding clean solutions to problems.

First thing to do, then, is to get the end date of the previous contract for the same employee with the LAG function. Doing that, it’s simple to compare both dates and check if it was a break of sequence.

```SQL
WITH ordered_contracts AS (
    SELECT
        *,
        LAG(end_date) OVER (PARTITION BY employee_id ORDER BY start_date) AS previous_end_date
    FROM
        contracts
),
gapped_contracts AS (
    SELECT
        *,
        -- Deals with the case of the first contract, which won't have
        -- a previous end date. In this case, it's still the start of a new
        -- sequence.
        CASE WHEN previous_end_date IS NULL
            OR previous_end_date < start_date - INTERVAL '1 day' THEN
            1
        ELSE
            0
        END AS is_new_sequence
    FROM
        ordered_contracts
)
SELECT * FROM gapped_contracts ORDER BY employee_id ASC;
```

An intuitive way to continue the query is to number the sequences of each employee. For example, an employee who has no gap, will always be on his first sequence, but an employee who had 5 breaks in contracts will be on his 5th sequence. Funnily enough, this is done by another window function.

```SQL
--
-- Previous CTEs
--
sequences AS (
    SELECT
        *,
        SUM(is_new_sequence) OVER (PARTITION BY employee_id ORDER BY start_date) AS sequence_id
FROM
    gapped_contracts
)
SELECT * FROM sequences ORDER BY employee_id ASC;
```

Notice how, for Employee 2, he starts his sequence #2 after the first gapped value. To finish this query, I grouped the data by employee, got the value of their most recent sequence, and then did an inner join with the sequences to keep only the most recent one.

```SQL
--
-- Previous CTEs
--
max_sequence AS (
    SELECT
        employee_id,
        MAX(sequence_id) AS max_sequence_id
FROM
    sequences
GROUP BY
    employee_id
),
latest_contract_sequence AS (
    SELECT
        c.contract_id,
        c.employee_id,
        c.start_date,
        c.end_date
    FROM
        sequences c
        JOIN max_sequence m ON c.sequence_id = m.max_sequence_id
            AND c.employee_id = m.employee_id
        ORDER BY
            c.employee_id,
            c.start_date
)
SELECT
    *
FROM
    latest_contract_sequence;
```

As expected, our final result is basically our starting query just with the first contract of Employee 2 missing!

Finally, the last puzzle — I’m glad you made it this far.

For me, this is the most mind-blowing one, as when I first encountered this problem I thought of a completely different solution that would be a mess to implement in SQL.

For this puzzle, I’ve changed the context from what I had to deal with for my job, as I think it will make it easier to explain.

Imagine you’re a data analyst at an event venue, and you’re analyzing the talks scheduled for an upcoming event. You want to find the time of day where there will be the highest number of talks happening at the same time.

This is what you should know about the schedules:

Rooms are booked in increments of 30min, e.g. from 9h-10h30.
The data is clean, there are no overbookings of meeting rooms.
There can be back-to-back meetings in a single meeting room.

Meeting schedule visualized (this is the actual data).

```SQL
CREATE TABLE meetings (
    room TEXT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL
);

INSERT INTO meetings (room, start_time, end_time) VALUES
    -- Room A meetings
    ('Room A', '2024-10-01 09:00', '2024-10-01 10:00'),
    ('Room A', '2024-10-01 10:00', '2024-10-01 11:00'),
    ('Room A', '2024-10-01 11:00', '2024-10-01 12:00'),
    -- Room B meetings
    ('Room B', '2024-10-01 09:30', '2024-10-01 11:30'),
    -- Room C meetings
    ('Room C', '2024-10-01 09:00', '2024-10-01 10:00'),
    ('Room C', '2024-10-01 11:30', '2024-10-01 12:00');
```

The way to solve this is using what is called a Sweep Line Algorithm, or also known as an event-based solution. This last name actually helps to understand what will be done, as the idea is that instead of dealing with intervals, which is what we have in the original data, we deal with events instead.

To do this, we need to transform every row into two separate events. The first event will be the Start of the meeting, and the second event will be the End of the meeting.

```SQL
WITH events AS (
  -- Create an event for the start of each meeting (+1)
  SELECT 
    start_time AS event_time, 
    1 AS delta
  FROM meetings
  UNION ALL
  -- Create an event for the end of each meeting (-1)
  SELECT 
   -- Small trick to work with the back-to-back meetings (explained later)
    end_time - interval '1 minute' as end_time,
    -1 AS delta
  FROM meetings
)
SELECT * FROM events;
```

Take the time to understand what is happening here. To create two events from a single row of data, we’re simply unioning the dataset on itself; the first half uses the start time as the timestamp, and the second part uses the end time.

You might already notice the delta column created and see where this is going. When an event starts, we count it as +1, when it ends, we count it as -1. You might even be already thinking of another window function to solve this, and you’re actually right!

But before that, let me just explain the trick I used in the end dates. As I don’t want back-to-back meetings to count as two concurrent meetings, I’m subtracting a single minute of every end date. This way, if a meeting ends and another starts at 10h30, it won’t be assumed that two meetings are concurrently happening at 10h30.

Okay, back to the query and yet another window function. This time, though, the function of choice is a rolling SUM.

```SQL
--
-- Previous CTEs
--
ordered_events AS (
  SELECT
    event_time,
    delta,
    SUM(delta) OVER (ORDER BY event_time, delta DESC) AS concurrent_meetings
  FROM events
)
SELECT * FROM ordered_events ORDER BY event_time DESC;
```

The rolling SUM at the Delta column is essentially walking down every record and finding how many events are active at that time. For example, at 9 am sharp, it sees two events starting, so it marks the number of concurrent meetings as two!

When the third meeting starts, the count goes up to three. But when it gets to 9h59 (10 am), then two meetings end, bringing the counter back to one. With this data, the only thing missing is to find when the highest value of concurrent meetings happens.

```SQL
--
-- Previous CTEs
--
max_events AS (
  -- Find the maximum concurrent meetings value
  SELECT 
    event_time, 
    concurrent_meetings,
    RANK() OVER (ORDER BY concurrent_meetings DESC) AS rnk
  FROM ordered_events
)
SELECT event_time, concurrent_meetings
FROM max_events
WHERE rnk = 1;
```

That’s it! The interval of 9h30–10h is the one with the largest number of concurrent meetings, which checks out with the schedule visualization above!

This solution looks incredibly simple in my opinion, and it works for so many situations. Every time you are dealing with intervals now, you should think if the query wouldn’t be easier if you thought about it in the perspective of events.

But before you move on, and to really nail down this concept, I want to leave you with a bonus challenge, which is also a common application of the Sweep Line Algorithm. I hope you give it a try!

Bonus challenge

The context for this one is still the same as the last puzzle, but now, instead of trying to find the period when there are most concurrent meetings, the objective is to find bad scheduling. It seems that there are overlaps in the meeting rooms, which need to be listed so it can be fixed ASAP.

How would you find out if the same meeting room has two or more meetings booked at the same time? Here are some tips on how to solve it:

It’s still the same algorithm.
This means you will still do the UNION, but it will look slightly different.
You should think in the perspective of each meeting room.

You can use this data for the challenge:

```SQL
CREATE TABLE meetings_overlap (
    room TEXT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL
);

INSERT INTO meetings_overlap (room, start_time, end_time) VALUES
    -- Room A meetings
    ('Room A', '2024-10-01 09:00', '2024-10-01 10:00'),
    ('Room A', '2024-10-01 10:00', '2024-10-01 11:00'),
    ('Room A', '2024-10-01 11:00', '2024-10-01 12:00'),
    -- Room B meetings
    ('Room B', '2024-10-01 09:30', '2024-10-01 11:30'),
    -- Room C meetings
    ('Room C', '2024-10-01 09:00', '2024-10-01 10:00'),
    -- Overlaps with previous meeting.
    ('Room C', '2024-10-01 09:30', '2024-10-01 12:00');
```

If you’re interested in the solution to this puzzle, as well as the rest of the queries, check this GitHub repo.

The first takeaway from this blog post is that window functions are overpowered. Ever since I got more comfortable with using them, I feel that my queries have gotten so much simpler and easier to read, and I hope the same happens to you.

If you’re interested in learning more about them, you would probably enjoy reading this other blog post I’ve written, where I go over how you can understand and use them effectively.

The second takeaway is that these patterns used in the challenges really do happen in many other places. You might need to find sequences of subscriptions, customer retention, or you might need to find overlap of tasks. There are many situations when you will need to use window functions in a very similar fashion to what was done in the puzzles.

The third thing I want you to remember is about this solution to using events besides dealing with intervals. I’ve looked at some problems I solved a long time ago that I could’ve used this pattern on to make my life easier, and unfortunately, I didn’t know about it at the time.

I really do hope you enjoyed this post and gave a shot to the puzzles yourself. And I’m sure that if you made it this far, you either learned something new about SQL or strengthened your knowledge of window functions!

Thank you so much for reading. If you have questions or just want to get in touch with me, don’t hesitate to contact me at mtrentz.com.

All images by the author unless stated otherwise.

Stay Ahead

Explore More Insights

Stay ahead with more perspectives on cutting-edge power, infrastructure, energy, bitcoin and AI solutions. Explore these articles to uncover strategies and insights shaping the future of industries.

Routine maintenance as a failure vector in modern networks

Pre-checks should include both control-plane and data-plane evidence. Control-plane checks confirm configuration, synchronization, device health, routing tables, interface status and object availability. Data-plane checks validate real traffic movement: TCP handshakes, TLS negotiation, HTTP status codes, API responses, session persistence, source NAT behavior and return-path consistency. During the change, monitoring should

Eni North Galal continues contracting for Kutei North Hub field development offshore Indonesia

Eni North Ganal Ltd., a subsidiary of Searah Ltd., has let a contract to SLB OneSubsea to deliver a steel tube umbilical system for the Kutei North Hub field development project offshore East Kalimantan, Indonesia. SLB OneSubsea will engineer, procure, and manufacture 94.6 km of umbilical for water depths of

AI job titles expand beyond tech as IT hiring remains strong

Separately, according to CompTIA’s latest Tech Jobs Report, employers posted more than 280,000 new technology job postings in June, marking the sixth consecutive month of growth. Active technology job postings approached 600,000, while employment in tech occupations increased by 47,000 positions. The unemployment rate for tech occupations fell to 2.9%,

Broadcom will sell more US-made components to Apple

Apple has agreed to buy up to $30 billion-worth of additional chips and other wireless components from Broadcom over the next few years. The new agreement could lead to Broadcom making up to 15 billion more chips in the US, and enable it to modernize its manufacturing facilities in Fort

PTTEP achieves Thailand’s first wellhead platform reuse in Gulf of Thailand

PTT Exploration and Production Public Co. Ltd. (PTTEP) has completed Thailand’s first total wellhead platform reuse project by redeploying an entire decommissioned petroleum wellhead platform as a complete structure in Funan field in the Gulf of Thailand. The reuse project comes as part of PTTEP’s program to maximize value and extend utilization of wellhead platforms that remain structurally sound and safe after depleting resources at a location by redeploying the platform as a complete structure. The first implementation was carried out at the Jakrawan K wellhead platform (JKWK), in Funan field under the G1/61 Project. As part of the project, PTTEP adopted the wet-tow method to relocate the jacket, helping curb energy consumption and minimize impacts on marine life attached to the platform structure, supporting a balance between energy production and marine environmental stewardship. The topside, jacket, and selected pile sections were relocated and reinstalled for use within the same field, reducing the overall construction and installation period to only 6 months, down from about 20 months for a newly built platform. Additionally, the approach cut construction costs by about 35–50% compared with construction of an entirely new wellhead platform. PTTEP said it expects the initiative to also reduce greenhouse gas emissions by about 3,270 tonnes of CO2e/platform by limiting the use of steel and other equipment required for construction of new platforms. PTTEP is operator of the G1/61 project (60%) with partner Mubadala Investment Co. (40%).

Trump declares Iran ceasefire over; oil surges on renewed supply risk

US President Donald Trump said the ceasefire and memorandum of understanding (MOU) reached with Iran last month is effectively over following a fresh exchange of strikes, reigniting supply concerns and sending crude prices sharply higher. Speaking alongside NATO Secretary-General Mark Rutte at the alliance’s summit in Ankara, Pres. Trump said Washington no longer sees value in maintaining the ceasefire framework with Tehran, though he left open the possibility of continued talks. He added that further US military action against Iran remains likely after strikes overnight. Stay updated on oil price volatility, shipping disruptions, LNG market analysis, and production output at OGJ’s Iran war content hub. The escalation was triggered by alleged Iranian attacks on three commercial vessels transiting the Strait of Hormuz on July 7. US Central Command said it responded with strikes on more than 80 Iranian targets, including air defense systems, command-and-control infrastructure, anti-ship missile capabilities, and over 60 Islamic Revolutionary Guard Corps (IRGC) fast boats operating in and near the strait. US Central Command described the tanker attacks as a clear violation of the June 17 agreement. Iran’s Foreign Ministry called the US strikes a breach of the MOU and said Tehran would continue to defend its sovereignty. The IRGC said it retaliated with drone and missile strikes targeting US military facilities in Bahrain and Kuwait. Authorities in both countries reported intercepting incoming projectiles, with no material damage confirmed. Trump said on July 8 the US is considering reinstating a naval blockade targeting Iranian ports and vessels. He also raised the possibility of strikes on civilian infrastructure, including electric plants and desalination facilities, as well as a potential move to take control of Kharg Island, home to the bulk of Iran’s crude export infrastructure. He said Tuesday’s strikes had reached the island but had not targeted its

US EIA forecasts declining oil prices as supply disruptions ease

In its July 7 Short-Term Energy Outlook (STEO) report, the US Energy Information Administration (EIA) said it expects global oil prices to decline as supply disruptions linked to the Strait of Hormuz ease and production recovers. On June 18, the US and Iran signed a memorandum of understanding to end the conflict and reopen the strait, which had been largely closed since Feb. 28. The disruption to this critical oil transit chokepoint constrained global flows, driving major price volatility. Brent crude averaged $85/bbl in June, down $22/bbl from May and $32/bbl below its April peak. Prices fell below $70/bbl on July 1 as tanker traffic through the strait increased sharply, easing supply concerns. EIA now expects most shut-in crude production to return to near pre-conflict levels by yearend, with full restoration largely to be completed by first-quarter 2027. Despite the recovery in flows, global inventories remain significantly depleted following earlier draws. EIA estimates oil inventories declined by an average of 5.1 million b/d in second-quarter 2026 and will fall by a further 2.2 million b/d in third-quarter 2026, as much of the recent tanker movement reflects previously stranded cargoes. As a result, the market is expected to remain relatively tight through most of third-quarter 2026 before shifting back into oversupply. EIA forecasts global oil consumption will decline by 1.2 million b/d in 2026, led by a 0.8 million b/d drop in non-OECD demand, particularly in the Asia Pacific. Demand is expected to rebound in 2027 as prices ease and supply normalizes, with consumption rising by 2.0 million b/d to 104.8 million b/d. As supply growth outpaces demand, inventories are projected to build by 2.7 million b/d in fourth-quarter 2026 and by 5.0 million b/d in 2027. This shift is expected to place sustained downward pressure on prices. EIA forecasts Brent

Eni lets EPCI contract for Kutei North Hub field FPSO

Eni North Ganal has let an engineering, procurement, construction, and installation (EPCI) contract to a joint venture between PT Saipem Indonesia and PT Tripatra Engineers and Constructors for a floating production, storage, and offloading (FPSO) unit for the Kutei North Hub Field Development Project in Kutei basin, offshore Indonesia, about 70 km off East Kalimantan. The project execution, with an estimated duration of 48 months, includes project management, engineering, procurement of materials, fabrication, construction and installation activities, as well as commissioning and start-up of the FPSO unit. The contract is valued at about $2 billion for Saipem’s share. The Kutei FPSO project is part of the Kutei North Hub Development, which comprises a subsea development tied back to the new FPSO, a dedicated gas export pipeline to the Bontang LNG plant, and domestic gas users via the existing East Kalimantan System. Eni North Ganal is controlled by Searah Ltd., which was formed through a strategic partnership between Eni and Petronas.

EIA: US crude inventories up 3.0 million bbl

US crude oil inventories for the week ended July 3, excluding the Strategic Petroleum Reserve, increased by 3.0 million bbl from the previous week, according to data from the US Energy Information Administration (EIA). At 411.4 million bbl, US crude oil inventories are about 6% below the 5-year average for this time of year, the EIA report indicated. EIA said total motor gasoline inventories decreased by 1.9 million bbl from last week and are about 6% below the 5-year average for this time of year. Finished gasoline inventories and blending components inventories both decreased last week. Distillate fuel inventories decreased by 5.0 million bbl last week and are about 12% below the 5-year average for this time of year. Propane-propylene inventories decreased by 800,000 bbl from last week and are 29% above the 5-year average for this time of year, EIA said. US crude oil refinery inputs averaged 17.0 million b/d for the week ended July 3, which was 173,000 b/d less than the previous week’s average. Refineries operated at 95.8% of capacity. Gasoline production decreased, averaging 9.7 million b/d. Distillate fuel production decreased, averaging 5.2 million b/d. US crude oil imports averaged 5.6 million b/d, up 351,000 b/d from the previous week. Over the last 4 weeks, crude oil imports averaged about 5.4 million b/d, 11.4% less than the same 4-week period last year. Total motor gasoline imports averaged 423,000 b/d. Distillate fuel imports averaged 87,000 b/d.

Marubeni closes deal to acquire Barnett-focused EagleRidge Energy

Marubeni Corp. has closed a deal to acquire EagleRidge Energy II LLC, a natural gas operator based in Dallas, Tex., advancing its effort to expand energy operations and natural gas assets in North America. EagleRidge, now a wholly owned subsidiary of Marubeni, has focused its operations on the Barnett shale in North Texas. The company operates over 3,500 wells and produces 300 MMcfed over 450,000 gross acres across 16 counties. The deal, which was announced in June, increases Marubeni’s production capacity in the Barnett shale to about 170 MMcfed. With the transaction closed, Marubeni said in a July 6 update, the EagleRidge management and operational team remains in place, with updates to executive leadership. Tom Ashton and Sam Miller will share the role of co-presidents. Hiroki Shima has been appointed chairman, and Michael Ronca transitions to vice-chairman.

Infoblox acquires Kentik, adding network observability to its DNS and DDI platform

Infoblox announced today that it has entered into a definitive agreement to acquire Kentik, combining Infoblox’s authoritative DNS, DHCP, and IP address management (IPAM) data with Kentik’s network observability platform. Financial terms were not disclosed. Kentik was founded in 2014, originally as CloudHelix before rebranding the following year, and has raised more than $100 million in venture funding to date. The platform provides real-time visibility into network traffic and ingests flow data, routing intelligence, and device telemetry across data centers, cloud environments, WANs, and the public internet. In recent years, the company has enhanced its platform with an AI advisor that helps to accelerate investigations. Infoblox has spent more than two decades managing the DNS, DHCP, and IPAM services enterprises rely on to stay connected. In 2024, it first launched its Universal DDI SaaS platform for managing DNS, DHCP, and IP addresses from a single place, expanding in 2025 to more providers. DDI refers to the trio of core network services in IP networks: DNS, which turns domain names into IP addresses; DHCP, which assigns IP addresses to resources; and IPAM, which manages the network’s IP address infrastructure.

Building the AI Optical Layer: Connectivity, Standards, and the Future of AI Infrastructure

As AI data centers push past the limits of traditional compute architecture, the industry’s attention is moving deeper into the physical layer. GPUs, accelerators, power systems and cooling platforms still dominate the headlines, but the network fabric that connects those systems is becoming just as critical. A wave of recent announcements points to the same conclusion: future growth will depend not only on more compute, but on faster, denser, more efficient and more scalable optical connectivity. A new multi-source agreement is bringing together major technology companies to standardize expanded beam optical connectivity for AI data centers. University of Arizona research is powering a new optical switching technology designed to reduce the energy consumed by data center networks. STL is planning to invest up to $100 million in U.S. manufacturing capacity to support AI data center and telecom customers with optical connectivity products. Those developments are now being reinforced by a broader series of moves across the optical ecosystem: Corning’s major AI infrastructure partnerships with NVIDIA and Amazon, GlobalFoundries’ push into co-packaged optics, Sivers’ laser-array collaboration with GlobalFoundries, Wiwynn’s co-packaged optics demonstration at Computex, Credo’s acquisition of DustPhotonics, and emerging near-packaged optical interconnect designs from LightSpeed Photonics. Taken together, these announcements highlight a maturing market around the optical layer of AI infrastructure. The value is not simply faster data movement. It is about reducing deployment complexity, lowering operating overhead, supporting higher-density clusters, improving energy efficiency and strengthening the domestic supply chain behind AI-ready networks. Let’s drill down into what these announcements mean. Standards for the AI Optical Layer The launch of a new coalition focused on expanded beam optical, or EBO, connectivity reflects a practical challenge facing AI deployments: as clusters grow larger and more bandwidth-intensive, physical connections become harder to deploy, maintain and scale. 3M announced that it has joined

Data Center Jobs: Engineering, Construction, Commissioning, Sales, Field Service and Facility Tech Jobs Available in Major Data Center Hotspots

Each month Data Center Frontier, in partnership with Pkaza, posts some of the hottest data center career opportunities in the market. Here’s a look at some of the latest data center jobs posted on the Data Center Frontier jobs board, powered by Pkaza Critical Facilities Recruiting. Looking for Data Center Candidates? Check out Pkaza’s Active Candidate / Featured Candidate Hotlist CFD Engineer – Data Center Mechanical DesignNew York, NY (remote)This position is also available as a remote role anywhere in the US, in addition to key markets such as Cedar Rapids, IA; Kansas City, MO or White Plains, NY. Our client is an engineering design and commissioning company that has a national footprint and specializes in MEP critical facilities design. They provide design, commissioning, consulting and management expertise in the critical facilities space. They have a mindset to provide reliability, energy efficiency, and sustainable design expertise when providing these consulting services for enterprise, colocation and hyperscale companies. This career-growth minded opportunity offers exciting projects with leading-edge technology and innovation as well as competitive salaries and benefits. Electrical Commissioning Engineer New Albany, OH (limited travel)Non-Traveling CxA positions available in: Indianapolis, IN; Cedar Rapids, IA and Austin, TX. Traveling CxA Roles: New York, NY; White Plain, NY; Morristown, NJ; Dallas, TX; Richmond, VA; Ashburn, VA; Montvale, NJ; Charlotte, NC; Atlanta, GA; Phoenix, AZ; Salt Lake City, UT; Kansas City, MO; Omaha, NE; Chesterton, IN or Chicago, IL. *** ALSO looking for a LEAD EE and ME CxA Agents and CxA PMs. *** Our client is an engineering design and commissioning company that has a national footprint and specializes in MEP critical facilities design. They provide design, commissioning, consulting and management expertise in the critical facilities space. They have a mindset to provide reliability, energy efficiency, sustainable design and LEED expertise when providing these consulting services

H5 Data Centers’ 325 Hudson: A Manhattan Carrier Hotel with SoHo DNA – DCF Tours

A Carrier Hotel Reimagined for Modern Colocation H5 formally announced its expansion into 325 Hudson, a 225,000 square-foot mixed-use building comprised of office, lab and data center uses, in 2021 through a partnership with real estate investment firm DivcoWest, describing its new location as a data center and carrier hotel in one of the world’s largest communications markets. At the time, H5 founder and CEO Josh Simms framed the move as an opportunity to expand an already-established interconnection ecosystem while supporting growing demand from cloud providers, content delivery networks, and communications carriers. That vision now appears fully realized inside the building. The facility today operates as both a traditional carrier hotel and a modern enterprise colocation environment. H5’s infrastructure footprint supports high-density deployments, A/B UPS power architecture, N+1 emergency generators, N+1 CRAC systems, and energy-efficient in-row cooling with cold aisle containment. The building also reflects the physical realities of Manhattan infrastructure engineering. Operators work within vertical constraints rather than sprawling horizontal campuses. Freight access, riser strategy, structured cabling pathways, and efficient floor utilization become critical operational variables. H5 highlighted several features tailored for those realities, including 13-foot slab-to-slab heights, 150 pounds-per-square-foot floor loading capability, secure loading access, and extensive pre-built conduit infrastructure.

AI Infrastructure Demands a New Operating System for Project Delivery

For much of the data center industry’s history, project management has largely been viewed as an execution discipline: a collection of schedules, milestones, spreadsheets, and status meetings designed to shepherd individual facilities from groundbreaking to commissioning. The AI era is rapidly rendering that model obsolete. As hyperscalers, developers, utilities, EPC firms, telecom providers, equipment suppliers, and local governments converge around increasingly complex AI campuses, the challenge is no longer simply delivering projects on time. Rather, it is orchestrating an infrastructure manufacturing process that stretches from land acquisition and permitting through construction, operations, and ultimately asset modernization years later. That changing reality was a central theme during Data Center Frontier’s conversation with Sitetracker at Fiber Connect 2026. The company’s perspective reflects a broader shift underway across digital infrastructure: project management is evolving into lifecycle management, where financial planning, regulatory coordination, supply chain visibility, and operational readiness become inseparable parts of the same platform. Complexity Begins Before Construction Much of the attention surrounding AI infrastructure focuses on GPU deployments, liquid cooling, and power availability. Yet Sitetracker argues that many of today’s greatest operational headaches begin much earlier in the development process. According to Reilly McClure, Sr. Product Marketing Manager – Digital Infrastructure with Sitetracker, operators are increasingly seeking help with land acquisition, parcel management, and site identification as AI infrastructure expands into new markets. “There are so many variables that we need to track,” he explained. “The demand and the growth and the build-out for where all that infrastructure is going is becoming increasingly complex. They’re finding it just cannot be done on a spreadsheet.” That observation resonates across the industry. Finding suitable sites now requires simultaneously evaluating power availability, transmission timelines, fiber access, permitting requirements, environmental studies, municipal approvals, and community considerations; all while competing developers race to secure the same

Gartner: Data center electricity consumption to grow 26% in 2026

AI-optimized servers are a relatively new phenomenon but they have rapidly gained uh traditional data centers in terms of power use. Gartner estimates AI-optimized server adoption will account for 31% of data center power consumption in 2026, and that by 2027 their power consumption will surpass that of conventional servers. “Surging demand for compute-intensive AI workloads is driving unprecedented data center power growth, while AI capacity is now constrained by power availability, making data center power security the new battle ground for scaling and protecting margins in the global AI race,” said Wang in a statement. Wang said of the 565TWh consumed this year, the U.S. will account for about 204TWh, or 36% of the total amount consumed. And of the 204TWh consumed this year, dedicated AI data centers will consume 68TWh, or one-third of the total. So in just five years, AI data centers have gone from zero to of the total power consumption in the US.

Microsoft will invest $80B in AI data centers in fiscal 2025

And Microsoft isn’t the only one that is ramping up its investments into AI-enabled data centers. Rival cloud service providers are all investing in either upgrading or opening new data centers to capture a larger chunk of business from developers and users of large language models (LLMs). In a report published in October 2024, Bloomberg Intelligence estimated that demand for generative AI would push Microsoft, AWS, Google, Oracle, Meta, and Apple would between them devote $200 billion to capex in 2025, up from $110 billion in 2023. Microsoft is one of the biggest spenders, followed closely by Google and AWS, Bloomberg Intelligence said. Its estimate of Microsoft’s capital spending on AI, at $62.4 billion for calendar 2025, is lower than Smith’s claim that the company will invest $80 billion in the fiscal year to June 30, 2025. Both figures, though, are way higher than Microsoft’s 2020 capital expenditure of “just” $17.6 billion. The majority of the increased spending is tied to cloud services and the expansion of AI infrastructure needed to provide compute capacity for OpenAI workloads. Separately, last October Amazon CEO Andy Jassy said his company planned total capex spend of $75 billion in 2024 and even more in 2025, with much of it going to AWS, its cloud computing division.

John Deere unveils more autonomous farm machines to address skill labor shortage

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More Self-driving tractors might be the path to self-driving cars. John Deere has revealed a new line of autonomous machines and tech across agriculture, construction and commercial landscaping. The Moline, Illinois-based John Deere has been in business for 187 years, yet it’s been a regular as a non-tech company showing off technology at the big tech trade show in Las Vegas and is back at CES 2025 with more autonomous tractors and other vehicles. This is not something we usually cover, but John Deere has a lot of data that is interesting in the big picture of tech. The message from the company is that there aren’t enough skilled farm laborers to do the work that its customers need. It’s been a challenge for most of the last two decades, said Jahmy Hindman, CTO at John Deere, in a briefing. Much of the tech will come this fall and after that. He noted that the average farmer in the U.S. is over 58 and works 12 to 18 hours a day to grow food for us. And he said the American Farm Bureau Federation estimates there are roughly 2.4 million farm jobs that need to be filled annually; and the agricultural work force continues to shrink. (This is my hint to the anti-immigration crowd). John Deere’s autonomous 9RX Tractor. Farmers can oversee it using an app. While each of these industries experiences their own set of challenges, a commonality across all is skilled labor availability. In construction, about 80% percent of contractors struggle to find skilled labor. And in commercial landscaping, 86% of landscaping business owners can’t find labor to fill open positions, he said. “They have to figure out how to do

2025 playbook for enterprise AI success, from agents to evals

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More 2025 is poised to be a pivotal year for enterprise AI. The past year has seen rapid innovation, and this year will see the same. This has made it more critical than ever to revisit your AI strategy to stay competitive and create value for your customers. From scaling AI agents to optimizing costs, here are the five critical areas enterprises should prioritize for their AI strategy this year. 1. Agents: the next generation of automation AI agents are no longer theoretical. In 2025, they’re indispensable tools for enterprises looking to streamline operations and enhance customer interactions. Unlike traditional software, agents powered by large language models (LLMs) can make nuanced decisions, navigate complex multi-step tasks, and integrate seamlessly with tools and APIs. At the start of 2024, agents were not ready for prime time, making frustrating mistakes like hallucinating URLs. They started getting better as frontier large language models themselves improved. “Let me put it this way,” said Sam Witteveen, cofounder of Red Dragon, a company that develops agents for companies, and that recently reviewed the 48 agents it built last year. “Interestingly, the ones that we built at the start of the year, a lot of those worked way better at the end of the year just because the models got better.” Witteveen shared this in the video podcast we filmed to discuss these five big trends in detail. Models are getting better and hallucinating less, and they’re also being trained to do agentic tasks. Another feature that the model providers are researching is a way to use the LLM as a judge, and as models get cheaper (something we’ll cover below), companies can use three or more models to

OpenAI’s red teaming innovations define new essentials for security leaders in the AI era

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More OpenAI has taken a more aggressive approach to red teaming than its AI competitors, demonstrating its security teams’ advanced capabilities in two areas: multi-step reinforcement and external red teaming. OpenAI recently released two papers that set a new competitive standard for improving the quality, reliability and safety of AI models in these two techniques and more. The first paper, “OpenAI’s Approach to External Red Teaming for AI Models and Systems,” reports that specialized teams outside the company have proven effective in uncovering vulnerabilities that might otherwise have made it into a released model because in-house testing techniques may have missed them. In the second paper, “Diverse and Effective Red Teaming with Auto-Generated Rewards and Multi-Step Reinforcement Learning,” OpenAI introduces an automated framework that relies on iterative reinforcement learning to generate a broad spectrum of novel, wide-ranging attacks. Going all-in on red teaming pays practical, competitive dividends It’s encouraging to see competitive intensity in red teaming growing among AI companies. When Anthropic released its AI red team guidelines in June of last year, it joined AI providers including Google, Microsoft, Nvidia, OpenAI, and even the U.S.’s National Institute of Standards and Technology (NIST), which all had released red teaming frameworks. Investing heavily in red teaming yields tangible benefits for security leaders in any organization. OpenAI’s paper on external red teaming provides a detailed analysis of how the company strives to create specialized external teams that include cybersecurity and subject matter experts. The goal is to see if knowledgeable external teams can defeat models’ security perimeters and find gaps in their security, biases and controls that prompt-based testing couldn’t find. What makes OpenAI’s recent papers noteworthy is how well they define using human-in-the-middle

Stay Ahead, Stay ONMINE