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.

AI agents are reshaping the threat landscape, shifting IT skills requirements

The AI Workforce Consortium is focused on studying the impact of AI on the workforce. Members include Accenture, Cisco, Cornerstone, Eightfold AI, Google, IBM, Indeed, Intel, Microsoft, Pearson, and SAP, in partnership with global advisors such as Digitaleurope and the European Vocational Training Association. The consortium’s report found that between

Palo Alto Networks and NTT Data target $1B in joint AI security sales with new managed offerings

“Backed by joint investments, more than 2,000 certified experts, as well as dedicated forward deployed engineers, the alliance will deliver a seamless approach to streamline deployments and speed client outcomes. Through direct engineering collaboration, NTT Data will gain early access to new platform features — further accelerating the delivery of

Network architecture pay climbs amid AI shift

Network architects today must make decisions that involve multiple technologies. From topology to WAN and SD-WAN to segmentation and security, the scope of networking skills continues to evolve. Those networking decisions become more complicated as enterprises incorporate AI workloads, edge computing, Wi-Fi 7, private 5G, and more. AI and automation

Golar contracts CIMC Raffles for fourth FLNG

Golar LNG Ltd. last week executed an engineering, procurement, and construction (EPC) contract with Yantai CIMC Raffles Offshore Ltd. (CIMC Raffles) for its fourth floating LNG (FLNG) and second MKII design FLNG vessel with an annual liquefaction capacity of 3.5 million tonnes/year (tpy). Golar’s fourth FLNG is expected to be

Energy Department Announces $500 Million Award to Revitalize American Steelmaking

WASHINGTON—The U.S. Department of Energy (DOE) today announced a $500 million award to support a $1 billion investment at Cleveland-Cliffs’ Middletown Works facility in Middletown, Ohio. Vice President JD Vance and U.S. Energy Secretary Chris Wright visited Middletown Works today to highlight the Trump Administration’s commitment to American steelworkers and the resurgence of American manufacturing. The investment will modernize American steelmaking, protect 2,300 American jobs, and strengthen the domestic steel supply chain. The project advances President Trump’s commitment to put American workers first, bring investment back to American communities, and strengthen the industries critical to America’s economic and national security. Cleveland-Cliffs determined that the business case for the original project scope no longer made sense given customers’ unwillingness to pay a “green premium” for steel. Working with DOE, Cleveland-Cliffs identified a viable alternative that will upgrade and improve the efficiency of the existing coal-fired blast furnace while also capturing and commercializing co-product blast furnace gas (BFG). “President Trump is rebuilding America’s industrial base,” said Secretary Wright. “This investment puts American workers and American manufacturing first. It will modernize one of our nation’s critical steelmaking facilities, protect thousands of jobs, and strengthen our domestic steel production—keeping Ohio at the heart of American manufacturing and strengthening our national security.” The investment will modernize critical steelmaking operations at Middletown Works by rebuilding and upgrading the plant’s main coal-fired ironmaking furnace, deploying AI to optimize furnace operations and improve energy efficiency, and building an on-site facility to convert steel mill process gases into electricity. Follow-on investments will turn industrial byproducts into materials for concrete used in regional infrastructure. “This landmark investment at Middletown Works will secure a reliable domestic supply of high-purity steel while protecting thousands of quality jobs in Ohio,” said Assistant Secretary of Energy Audrey Robertson. “DOE is proud to partner with Cleveland-Cliffs to reduce America’s dependence on foreign products

Energy Secretary Keeps Critical Generation Available in Mid-Atlantic

WASHINGTON—U.S. Secretary of Energy Chris Wright today issued an emergency order to address critical grid reliability issues facing the Mid-Atlantic region of the United States. The emergency order directs PJM Interconnection L.L.C. (PJM), in coordination with Constellation Energy Corporation, to ensure Units 3 and 4 of the Eddystone Generating Station in Pennsylvania remain available to operate and to employ economic dispatch to minimize costs for the American people. The units were originally slated to shut down on May 31, 2025. “The energy sources that perform when you need them most are the most valuable,” Secretary Wright said. “During recent Mid-Atlantic heat waves, coal, natural gas, and nuclear kept the lights and air conditioners on. President Trump and the Energy Department are committed to keeping critical generation available when demand is highest, reducing the risk of blackouts and ensuring Americans have affordable, reliable, and secure power—regardless of whether the wind is blowing or the sun is shining.” As outlined in DOE’s Resource Adequacy Report, power outages could increase by 100 times in 2030 if the U.S. continues to take reliable power offline. This order is in effect beginning on August 23, 2026, through November 20, 2026. ###

Energy Department Announces $500 Million to Secure America’s Critical Mineral and Battery Supply Chains

WASHINGTON—The U.S. Department of Energy’s (DOE) Office of Critical Minerals and Energy Innovation (CMEI) today announced $500 million for seven selected projects to expand critical mineral and material processing, battery manufacturing, and recycling capacity in the United States. In accordance with President Trump’s Executive Order, Unleashing American Energy, the selected projects advance the President’s agenda to strengthen America’s domestic critical minerals and materials supply chains, reduce reliance on foreign sources, bolster national security, and advance American energy dominance. “For too long, America has depended on foreign actors for critical materials essential to modern life that underpin our economy, energy security, and national security,” said U.S. Secretary of Energy Chris Wright. “President Trump is reversing that dependence by securing our critical supply chains, unleashing American industry, and bringing critical materials production and processing back to the United States.” “DOE is taking decisive action to secure the critical supply chains necessary to power our nation,” said Assistant Secretary of Energy Audrey Robertson. “These projects underscore DOE’s commitment to driving innovation, reducing reliance on foreign sources, and promoting American energy dominance.” This is the third round of funding from DOE’s Battery Materials Processing and Battery Manufacturing and Recycling programs, which support battery materials processing, recycling, and manufacturing projects. These include demonstration projects, construction of commercial-scale facilities, and retrofitting or retooling existing facilities. Critical minerals and materials are essential to American industry, energy production, and national security. Expanding domestic capacity will help ensure the resources America needs are processed, manufactured, and recycled in the United States. Information on the selected projects is available here and here.

bp lets Shah Deniz compression automation contract

bp has let a contract to Emerson to deliver automation technologies for the Shah Deniz Compression project offshore Azerbaijan. Emerson will provide integrated control and safety systems aimed at enhancing production, safety, and reliability on the new offshore compression platform. The contract includes systems to provide process control, safety shutdown, fire and gas detection, and power management. Together, these systems deliver real-time visibility and remote control of critical operations, Emerson said. The $2.9 billion Shah Deniz Compression project, which includes an electrically powered, normally unattended offshore production platform, is a next stage development of the Caspian Sea Shah Deniz field. Designed to access low-pressure gas reserves and maximize overall recovery, the platform will be equipped with four 11 Mw compressors and serve as the central compression hub for gas from the Shah Deniz Alpha and Bravo platforms. The platform will operate remotely from bp’s onshore Sangachal terminal 55 km south of Baku. The project is expected to enable about 50 billion cu m of additional gas and about 25 million bbl of condensate production and export. Construction is scheduled to be completed in 2029, with first gas compression expected from the Shah Deniz Alpha platform in 2029 and from the Shah Deniz Bravo platform in 2030. The agreement follows a previous automation contract bp signed with Emerson for the Azeri Central East and Shah Deniz Stage 2 developments. bp is operator at Shah Deniz (29.99%) with partners Lukoil (19.99%), TPAO (19%), Cenub Qaz Dehlizi (16.02%), NICO (10%), and MVM (5%).

Federal court voids Texas GulfLink license over agency’s ‘serious procedural errors’

The ruling voids the license, halting all construction or progress. Sentinel Midstream declined comment on the ruling and would not answer questions about the status of construction. GulfLink, sited about 30 miles offshore Freeport, Tex., is designed to export up to 1 million b/d via Very Large Crude Carriers (VLCCs) to the government of Japan and Freeport Commodities. The project involves a 44-mile, 42-in. OD pipeline and was scheduled to begin operations around 2028. The estimated $2.1 billion investment was funded as part of a broader trade agreement between the US and Japan. The legal battle stems from a specific rule in the Deepwater Port Act of 1974 that dictates that the federal government can only permit one crude oil deepwater port, including any supporting infrastructure, within a single designated “application area.” Because the competing SPOT project’s pipeline route physically overlaps and intersects GulfLink’s lines, the plaintiff—Citizens for Clean Air & Clean Water in Brazoria County (Better Brazoria), represented by Earthjustice—successfully argued that MARAD violated the “one port” rule when issuing GulfLink’s license in February. The three-judge panel found that MARAD “improperly drew” the map designing the project’s official boundaries to exclude the pipelines and approved two overlapping projects in the same zone instead of only licensing one. The court wrote that the scope of the error made vacatur, not the less serious remand without vacatur, the appropriate remedy. Vacatur deems the license invalid and is used when the court finds “serious procedural errors” that cannot be easily explained or fixed with minor changes. Remand without vacatur sends the decision back to the agency for corrections but leaves the current license in place in the meantime. SPOT project status The $2.5-3-billion SPOT project, developed by Enterprise Products Partners in partnership with Enbridge Inc., also lies about 30 miles from Freeport. Designed to handle VLCCs,

IEA: Emergency reserve withdrawals slow

The International Energy Agency (IEA) member countries continued to release emergency oil stocks in July, but the pace of withdrawals slowed sharply as crude supply availability improved in parts of the Asia Pacific and the market faced increasing product tightness. IEA countries released 26 million bbl of emergency stocks in July, bringing cumulative releases to 300 million bbl since the agency announced a coordinated 400-million bbl action on Mar. 11. Government stock draws averaged 750,000 b/d in July, down from 1.5 million b/d in June and 2.5 million b/d in May. The slowdown was particularly pronounced among IEA members in Asia Oceania, which released 4 million bbl from emergency stocks in July, compared with 8 million bbl in June and 44 million bbl in May. The decline reflected improved crude oil supply availability in Japan and Korea. The US also reduced the pace of emergency stock releases. Withdrawals from the Strategic Petroleum Reserve totaled 17 million bbl in July, roughly half the volume released in June. More than 100 million bbl of the emergency stocks committed under the IEA’s 400-million bbl coordinated action has yet to reach the market. The timing of the remaining releases will depend on market developments and broader oil supply security considerations in coming months, according to the agency. Most of the remaining emergency stocks consist of crude oil, however, limiting their ability to ease increasingly tight oil product markets, IEA said. At the same time, global observed oil inventories fell sharply in July amid severely constrained shipping through the Strait of Hormuz. Stocks declined by 69 million bbl, equivalent to 2.2 million b/d, with oil on water accounting for more than 90% of the decline. Oil on water fell by 63 million bbl, or about 2 million b/d, reflecting higher arrivals and lower exports amid

PJM’s New Data Center Power Equation

PJM Interconnection has now filed one of the most consequential proposed changes yet in the relationship between data centers and the electric grid. Rather than simply treating a new hyperscale or AI facility like any other customer whose demand will be backed through regional capacity procurement, PJM is proposing a framework under which the largest new loads would need to be supported by new capacity, have their needs covered through the Reliability Backstop Procurement, or face potential curtailment when the regional power system is short of supply. The approach has been developing since PJM launched its Critical Issue Fast Path process for large loads in 2025, but it became substantially more concrete in late July and August 2026. PJM filed its proposed Reliability Backstop Procurement with FERC on July 31 and began accepting applications that day for its FERC-approved Expedited Interconnection Track. On Aug. 13, PJM filed its proposed Interim Resource Adequacy Service, or IRAS, along with the Large Load Registry that would support it. The immediate numbers explain the urgency. PJM’s July 2026 capacity auction for the 2028/2029 delivery year procured 138,318 MW of unforced capacity through the centralized auction. Even after including Fixed Resource Requirement resources, however, PJM came up 6,831 MW short of its reliability requirement. The auction cleared at the FERC-approved $325/MW-day price cap. It was the second consecutive auction in which the PJM region failed to procure its full reliability requirement, something that had not happened before these two auctions. That gap is occurring while demand continues to accelerate. PJM’s 2026 long-term forecast projects summer peak demand growing at an average 3.6% annually over the next decade, compared with just 0.3% in the comparable forecast issued in 2021. Summer peak demand is projected to rise by nearly 66 GW over 10 years. Data centers are

Zayo, NVIDIA Build the Long-Haul Backbone for Distributed AI

The data center industry’s increasingly power-first approach to site selection has created a follow-on question: Once the megawatts are found, is there enough network infrastructure to make the site useful at AI scale? Zayo and NVIDIA are putting real infrastructure behind that question. Zayo said it is working with NVIDIA to expand network capacity supporting AI factories across North America, including an 8,000-route-mile program targeting some of the fastest-growing AI corridors in the United States. The project encompasses six new long-haul routes along with overbuilds of existing network across 10 high-demand corridors. The announcement arrives as AI data center development moves beyond the largest established hubs toward markets where power and land may be more readily available, but fiber capacity cannot necessarily be taken for granted. That geography is increasingly important. NVIDIA has separately developed “scale-across” networking technology designed to allow AI infrastructure distributed among different buildings — or even data centers separated by hundreds of kilometers — to operate as a more unified computing environment. Put together, the developments suggest that networking is becoming inseparable from the AI factory buildout itself. Power may determine where the next generation of AI infrastructure can be built. Fiber will increasingly determine how effectively those sites can participate in the larger AI ecosystem. Fiber Follows the Power Zayo CEO Steve Smith said AI demand is changing both where network infrastructure is needed and how aggressively capacity must be deployed ahead of development. “AI is fundamentally reshaping where and how network infrastructure needs to be built across the U.S.,” Smith said. The company’s 8,000-mile program is more nuanced than that top-line number might suggest. Zayo disclosed in April that the expansion includes approximately 3,000 route miles across six new long-haul routes, plus more than 5,000 route miles of overbuilds across 10 existing corridors. Zayo

Southern’s 17 GW Pipeline Puts AI Power Demand Into Utility Math

The headline number from Southern Company’s latest earnings report is hard to miss: electricity use by data centers across the utility’s system increased 55% in the second quarter compared with a year earlier. But the more consequential numbers may be the ones sitting behind it. Southern now has more than 1.2 GW of operating data center load, up by more than 500 MW from a year ago. At the same time, its electric utilities have signed contracts and large-load agreements totaling more than 17 GW by the mid-2030s, with another 8 GW in late-stage development and a prospective pipeline of large industrial and data center projects exceeding 75 GW. That leaves an enormous gap between the data center megawatts consuming electricity today and the load Southern has contractually positioned itself to serve during the next decade. For the data center industry, that gap may be the most important part of Southern’s second-quarter story. It offers a look at how utilities are beginning to convert the AI infrastructure boom from forecasts and campus announcements into contracts, generation procurement, transmission investment and eventually energized capacity. From Contracts to Megawatts Southern added roughly 6 GW of contracted large load during the quarter alone. Alabama Power signed three projects representing about 3 GW, while Georgia Power reached a 25-year agreement to serve OpenAI’s planned project in Effingham County near Savannah. That facility is expected to require approximately 3.2 GW and begin taking electric service in phases in 2028. The numbers nevertheless require an important distinction. Seventeen gigawatts contracted does not mean 17 GW will suddenly appear on Southern’s grid. Large data center campuses ramp gradually, often over several years, and Southern executives acknowledged that actual customer ramp schedules do not always match the assumptions made when projects are first approved. CEO Chris Womack said

PORTS-Pike Takes Shape as an 8-GW AI Infrastructure Model

Back on March 31, 2026, we discussed we discussed SoftBank and SB Energy’s plans to redevelop the former Portsmouth Gaseous Diffusion Plant site near Piketon as a 10-GW artificial intelligence data center campus supported by almost an equal amount of new power generation. At the time, the plan called for as much as 10 GW of new generation, including 9.2 GW of natural gas capacity, along with approximately $4.2 billion of high-voltage transmission infrastructure developed with AEP Ohio. An initial 800-MW data center phase was targeted for service in 2028. The March story was notable because Pike County appeared to offer a preview of a new model for building hyperscale infrastructure: develop the generation, transmission and data center simultaneously rather than wait for an increasingly congested regional grid to deliver multiple gigawatts of capacity. Not to mention the reuse of a brownfield site with the encouragement of the federal government. Since then, almost every important part of the project has moved forward, and on August 17, the most consequential missing pieces fell into place. NVIDIA announced that it will become the exclusive AI compute infrastructure provider for the PORTS-Pike Technology Campus. OpenAI will be the data center customer, signing a 20-year lease with SB Energy for approximately 8 GW of IT capacity. NVIDIA will invest another $1.5 billion in SB Energy and provide credit support for the land, power and shell infrastructure behind an initial 4.25 GW of IT load, with an option covering approximately another 3.75 GW. The Securities and Exchange Commission filing accompanying the announcement makes the financial commitment even more significant. NVIDIA disclosed that its aggregate payment obligation associated with its initial commitment is capped at $105 billion. That is not a conventional capital commitment to spend $105 billion building the campus, nor is it simply a

Nvidia scales back financing guarantee for OpenAI data center

Nvidia is scaling back a proposed financial guarantee tied to a massive OpenAI data center project in Ohio, reducing its initial commitment from as much as $250 billion to less than $120 billion, according to report in the Wall Street Journal. Earlier this month, Nvidia announced partnerships with major financial firms including Apollo Global Management, BlackRock, Blackstone, Brookfield Asset Management, Goldman Sachs and KKR, aimed at mobilizing more than $500 billion in capital for AI computing infrastructure. The change represents a significant restructuring of Nvidia’s role in financing the planned facility, which is being developed by SB Energy, a subsidiary of SoftBank. Under the revised arrangement, Nvidia would guarantee financing for the project’s first phase, representing roughly 5 gigawatts of capacity, or half of the total proposed capacity. Financing for the remaining capacity would be considered separately at a later stage.

Texas Tightens Oversight of Data Center Development

Texas has spent the past decade building one of the most data center-friendly policy environments in the United States. But the state’s political posture is tightening. The emerging message from Austin is that continued data center growth will face greater scrutiny over grid costs, water use, tax incentives and community impacts. What is interesting about this policy conversation is that the Texas Legislature is not in regular session. The 89th regular session ended June 2, 2025, and the 90th Legislature does not convene until January 12, 2027. What has occurred instead is a concentrated period of interim committee work, gubernatorial recommendations, implementation of Senate Bill 6, calls for a special session, and regulatory action by the Public Utility Commission of Texas and the Electric Reliability Council of Texas. Together, those efforts are creating the framework for a broader legislative debate in 2027 while already affecting projects seeking ERCOT interconnection, infrastructure costs and site-selection decisions. Abbott Sets Out a New Policy Framework The policy shift accelerated June 10, when Gov. Greg Abbott directed the PUCT to require data centers to fully fund the electric infrastructure needed to serve their operations and directed PUCT and ERCOT to identify additional actions available under existing authority. Separately, Abbott pledged to work with lawmakers in 2027 on legislation requiring data centers to add electric capacity, use water-efficient cooling systems, report electricity and water use, phase out outdated tax incentives and adopt additional protections for neighboring communities. The most consequential shift began June 10, when Gov. Greg Abbott sent state electricity regulators a sweeping list of data center policy priorities. Abbott called for future legislation requiring new facilities to add generation to the Texas grid, pay the full cost of their interconnection and related infrastructure, use closed-loop or similarly water-efficient cooling systems, and file annual reports

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