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.

Cisco strengthens integrated IT/OT network and security controls

Another significant move that will help IT/OT integration is the planned integration of the management console for Cisco’s Catalyst and Meraki networks. That combination will allow IT and OT teams to see the same dashboard for industrial OT and IT enterprise/campus networks. Cyber Vision will feeds into the dashboard along

AI spending to reach $1.5 trillion this year, Gartner says

AI features: often sold, not selected In market segments such as AI PCs, on the other hand, it is very clear that AI is largely sold along with the product and not specifically selected, according to the Gartner analyst. “When people go into a store today to buy a new

MOL’s Tiszaújváros steam cracker processes first circular feedstock

MOL Group has completed its first certified production trial using circular feedstock at subsidiary MOL Petrochemicals Co. Ltd. complex in Tiszaújváros, Hungary, advancing the company’s strategic push toward circular economy integration in petrochemical production. Confirmed completed as of Sept. 15, the pilot marked MOL Group’s first use of post-consumer plastic

Network jobs watch: Hiring, skills and certification trends

Desire for higher compensation Improve career prospects Want more interesting work “A robust and engaged tech workforce is essential to keeping enterprises operating at the highest level,” said Julia Kanouse, Chief Membership Officer at ISACA, in a statement. “In better understanding IT professionals’ motivations and pain points, including how these

WTI Falls on Stockpile, Fed Moves

Oil eased after a three-session advance as traders assessed fresh US stockpile data and a Federal Reserve interest-rate cut. West Texas Intermediate fell 0.7% to settle above $64 a barrel after the Federal Reserve lowered its benchmark interest rate by a quarter percentage point and penciled in two more reductions this year. Although lower rates typically boost energy demand, investors focused on policymakers’ warnings of mounting labor market weakness. Traders had also mostly priced in a 25 basis-point cut ahead of the decision, leading some to unwind hedges against a bigger-than-expected reduction. The dollar strengthened, making commodities priced in the currency less attractive. “There is a somewhat counterintuitive reaction to the Fed’s cut, but the dovish pivot cements their shift to protect the labor side of their mandate,” said Frank Monkam, head of macro trading at Buffalo Bayou Commodities. The shift suggests “an admission that growth risks to the economy are becoming more apparent and concerning.” The Fed move compounded an earlier slide as traders discounted the most recent US stockpile data, which showed crude inventories fell 9.29 million barrels amid a sizable increase in exports. However, the adjustment factor ballooned and distillate inventories rose to the highest since January, adding a bearish tilt to the report. “Traders like to see domestic demand pulling the inventories,” as opposed to exports, said Dennis Kissler, senior vice president for trading at BOK Financial Securities. The distillate buildup also stunted a rally following Ukraine’s attack on the Saratov refinery in its latest strike on Russian energy facilities — which have helped cut the OPEC+ member’s production to its lowest post-pandemic level, according to Goldman Sachs Group Inc. Still, the strikes haven’t been enough to push oil out of the $5 band it has been in for most of the past month-and-a-half, buffeted between

XRG Walks Away From $19B Santos Takeover

Abu Dhabi National Oil Co. dropped its planned $19 billion takeover of Australian natural gas producer Santos Ltd., walking away from an ambitious effort to expand overseas after failing to agree on key terms. A “combination of factors” discouraged the company’s XRG unit from making a final bid, it said Wednesday. The decision was strictly commercial and reflected disagreement over issues including valuation and tax, people familiar with the matter said, asking not to be identified discussing private information. It’s a notable retreat for XRG, the Adnoc spinoff launched to great fanfare last year and tasked with deploying Abu Dhabi’s billions into international dealmaking. The firm has been looking to build a global portfolio, particularly in chemicals and liquefied natural gas, and nixing the Santos transaction may slow an M&A drive aimed at diversifying the Middle Eastern emirate away from crude. The company made its indicative offer in June with a consortium that included Abu Dhabi Development Holding Co. and Carlyle Group Inc. The board of Santos, Australia’s second-largest fossil-fuel producer, recommended the $5.76-a-share proposal, which represented a 28% premium to the stock price at the time. But although the shares surged that day, they have remained well below the offer price, potentially indicating investors were skeptical the consortium could land the deal. Santos extended an exclusivity period for a second time last month, saying the group had sought more time to complete due diligence and obtain approvals. “The market will ask questions about Santos’ valuation after this,” Saul Kavonic, an energy analyst at MST Marquee, said by email. Investors may be wary about “any skeletons that may be lurking there, all the more so because XRG was a less price-sensitive buyer than most, yet still couldn’t make it work.” Santos’ American depositary receipts slumped as much as 9.5% to $4.69 on Wednesday. Covestro Hurdles Following agreements for

Slovakia and Hungary Resist Trump Bid to Halt Russian Energy

Slovakia and Hungary signaled they would resist pressure from US President Donald Trump to cut Russian oil and gas imports until the European Union member states find sufficient alternative supplies. “Before we can fully commit, we need to have the right conditions in place — otherwise we risk seriously damaging our industry and economy,” Slovak Economy Minister Denisa Sakova told reporters in Bratislava on Wednesday. The minister said sufficient infrastructure must first be in place to support alternative routes. The comments amount to a pushback against fresh pressure from Trump for all EU states to end Russian energy imports, a move that would hit Slovakia and Hungary. Hungarian Cabinet Minister Gergely Gulyas reiterated that his country would rebuff EU initiatives that threatened the security of its energy supplies. Sakova said she made clear Slovakia’s position during talks with US Energy Secretary Chris Wright in Vienna this week. She said the Trump official expressed understanding, while acknowledging that the US must boost energy projects in Europe. Trump said over the weekend that he’s prepared to move ahead with “major” sanctions on Russian oil if European nations do the same. The government in Bratislava is prepared to shut its Russian energy links if it has sufficient infrastructure to transport volumes, Sakova said. “As long as we have an alternative route, and the transmission capacity is sufficient, Slovakia has no problem diversifying,” the minister said. A complete cutoff of Russian supplies would pose a risk, she said, because Slovakia is located at the very end of alternative supply routes coming from the West. Slovakia and Hungary, landlocked nations bordering Ukraine, have historically depended on Russian oil and gas. After Russia’s full-scale invasion of Ukraine in 2022, both launched several diversification initiatives. Slovakia imports around third of its oil from non-Russian sources via the Adria pipeline

Slovakia Resists Pressure to Quickly Halt Russian Energy

Energy-related US CO2 emissions down 20% since 2005: EIA

Listen to the article 2 min This audio is auto-generated. Please let us know if you have feedback. Per capita carbon dioxide emissions from energy consumption fell in every state from 2005 to 2023, primarily due to less coal being burned, the U.S. Energy Information Administration said in a Monday report. In total, CO2 emissions fell by 20% in those years. The U.S. population increased by 14% during that period, so per capita, emissions fell by 30%, according to EIA. “Increased electricity generation from natural gas, which releases about half as many CO2 emissions per unit of energy when combusted as coal, and from non-CO2-emitting wind and solar generation offset the decrease in coal generation,” EIA said. Emissions decreased in every state, falling the most in Maryland and the District of Columbia, which saw per capita drops of 49% and 48%, respectively. Emissions fell the least in Idaho, where they dropped by 3%, and Mississippi, where they dropped by 1%. Optional Caption Courtesy of Energy Information Administration “In 2023, Maryland had the lowest per capita CO2 emissions of any state, at 7.8 metric tons of CO2 (mtCO2), which is the second lowest in recorded data beginning in 1960,” EIA said. “The District of Columbia has lower per capita CO2 emissions than any state and tied its record low of 3.6 mtCO2 in 2023.” EIA forecasts a 1% increase in total U.S. emissions from energy consumption this year, “in part because of more recent increased fossil fuel consumption for crude oil production and electricity generation growth.” In 2023, the transportation sector was responsible for the largest share of emissions from energy consumption across 28 states, EIA said. In 2005, the electric power sector had “accounted for the largest share of emissions in 31 states, while the transportation sector made up the

Chord Announces ‘Strategic Acquisition of Williston Basin Assets’

Chord Energy Corporation announced a “strategic acquisition of Williston Basin assets” in a statement posted on its website recently. In the statement, Chord said a wholly owned subsidiary of the company has entered into a definitive agreement to acquire assets in the Williston Basin from XTO Energy Inc. and affiliates for a total cash consideration of $550 million, subject to customary purchase price adjustments. The consideration is expected to be funded through a combination of cash on hand and borrowings, Chord noted in the statement, which highlighted that the effective date for the transaction is September 1, 2025, and that the deal is expected to close by year-end. Chord outlined in the statement that the deal includes 48,000 net acres in the Williston core, noting that “90 net 10,000 foot equivalent locations (72 net operated) extend Chord’s inventory life”. Pointing out “inventory quality” in the statement, Chord highlighted that “low average NYMEX WTI breakeven economics ($40s) compete at the front-end of Chord’s program and lower the weighted-average breakeven of Chord’s portfolio”. The company outlined that the deal is “expected to be accretive to all key metrics including cash flow, free cash flow and NAV in both near and long-term”. “We are excited to announce the acquisition of these high-quality assets,” Danny Brown, Chord Energy’s President and Chief Executive Officer, said in the statement. “The acquired assets are in one of the best areas of the Williston Basin and have significant overlap with Chord’s existing footprint, setting the stage for long-lateral development. The assets have a low average NYMEX WTI breakeven and are immediately competitive for capital,” he added. “We expect that the transaction will create significant accretion for shareholders across all key metrics, while maintaining pro forma leverage below the peer group and supporting sustainable FCF generation and return of capital,” he continued.

Land and Expand: CleanArc Data Centers, Google, Duke Energy, Aligned’s ODATA, Fermi America

Land and Expand is a monthly feature at Data Center Frontier highlighting the latest data center development news, including new sites, land acquisitions and campus expansions. Here are some of the new and notable developments from hyperscale and colocation data center operators about which we’ve been reading lately. Caroline County, VA, Approves 650-Acre Data Center Campus from CleanArc Caroline County, Virginia, has approved redevelopment of the former Virginia Bazaar property in Ruther Glen into a 650-acre data center campus in partnership with CleanArc Data Centers Operating, LLC. On September 9, 2025, the Caroline County Board of Supervisors unanimously approved an economic development performance agreement with CleanArc to transform the long-vacant flea market site just off I-95. The agreement allows for the phased construction of three initial data center buildings, each measuring roughly 500,000 square feet, which CleanArc plans to lease to major operators. The project represents one of the county’s largest-ever private investments. While CleanArc has not released a final capital cost, county filings suggest the development could reach into the multi-billion-dollar range over its full buildout. Key provisions include: Local hiring: At least 50 permanent jobs at no less than 150% of the prevailing county wage. Revenue sharing: Caroline County will provide annual incentive grants equal to 25% of incremental tax revenue generated by the campus. Water stewardship: CleanArc is prohibited from using potable county water for data center cooling, requiring the developer to pursue alternative technologies such as non-potable sources, recycled water, or advanced liquid cooling systems. Local officials have emphasized the deal’s importance for diversifying the county’s tax base, while community observers will be watching closely to see which cooling strategies CleanArc adopts in order to comply with the water-use restrictions. Google to Build $10 Billion Data Center Campus in Arkansas Moses Tucker Partners, one of Arkansas’

Hyperion and Alice & Bob Call on HPC Centers to Prepare Now for Early Fault-Tolerant Quantum Computing

As the data center industry continues to chase greater performance for AI and scientific workloads, a new joint report from Hyperion Research and Alice & Bob is urging high performance computing (HPC) centers to take immediate steps toward integrating early fault-tolerant quantum computing (eFTQC) into their infrastructure. The report, “Seizing Quantum’s Edge: Why and How HPC Should Prepare for eFTQC,” paints a clear picture: the next five years will demand hybrid HPC-quantum workflows if institutions want to stay at the forefront of computational science. According to the analysis, up to half of current HPC workloads at U.S. government research labs—Los Alamos National Laboratory, the National Energy Research Scientific Computing Center, and Department of Energy leadership computing facilities among them—could benefit from the speedups and efficiency gains of eFTQC. “Quantum technologies are a pivotal opportunity for the HPC community, offering the potential to significantly accelerate a wide range of critical science and engineering applications in the near-term,” said Bob Sorensen, Senior VP and Chief Analyst for Quantum Computing at Hyperion Research. “However, these machines won’t be plug-and-play, so HPC centers should begin preparing for integration now, ensuring they can influence system design and gain early operational expertise.” The HPC Bottleneck: Why Quantum is Urgent The report underscores a familiar challenge for the HPC community: classical performance gains have slowed as transistor sizes approach physical limits and energy efficiency becomes increasingly difficult to scale. Meanwhile, the threshold for useful quantum applications is drawing nearer. Advances in qubit stability and error correction, particularly Alice & Bob’s cat qubit technology, have compressed the resource requirements for algorithms like Shor’s by an estimated factor of 1,000. Within the next five years, the report projects that quantum computers with 100–1,000 logical qubits and logical error rates between 10⁻⁶ and 10⁻¹⁰ will accelerate applications across materials science, quantum

Google Partners With Utilities to Ease AI Data Center Grid Strain

Transmission and Power Strategy These agreements build on Google’s growing set of strategies to manage electricity needs. In June of 2025, Google announced a deal with CTC Global to upgrade transmission lines with high-capacity composite conductors that increase throughput without requiring new towers. In July 2025, Google and Brookfield Asset Management unveiled a hydropower framework agreement worth up to $3 billion, designed to secure firm clean energy for data centers in PJM and Eastern markets. Alongside renewable deals, Google has signed nuclear supply agreements as well, most notably a landmark contract with Kairos Power for small modular reactor capacity. Each of these moves reflects Google’s effort to create more headroom on the grid while securing firm, carbon-free power. Workload Flexibility and Grid Innovation The demand-response strategy is uniquely suited to AI data centers because of workload diversity. Machine learning training runs can sometimes be paused or rescheduled, unlike latency-sensitive workloads. This flexibility allows Google to throttle certain compute-heavy processes in coordination with utilities. In practice, Google can preemptively pause or shift workloads when notified of peak events, ensuring critical services remain uninterrupted while still creating significant grid relief. Local Utility Impact For utilities like I&M and TVA, partnering with hyperscale customers has a dual benefit: stabilizing the grid while keeping large customers satisfied and growing within their service territories. It also signals to regulators and ratepayers that data centers, often criticized for their heavy energy footprint, can actively contribute to reliability. These agreements may help avoid contentious rate cases or delays in permitting new power plants. Policy, Interconnection Queues, and the Economics of Speed One of the biggest hurdles for data center development today is the long wait in interconnection queues. In regions like PJM Interconnection, developers often face waits of three to five years before new projects can connect

Generators, Gas, and Grid Strategy: Inside Generac’s Data Center Play

A Strategic Leap Generac’s entry represents a strategic leap. Long established as a leader in residential, commercial, and industrial generation—particularly in the sub-2 megawatt range—the company has now expanded into mission-critical applications with new products spanning 2.2 to 3.5 megawatts. Navarro said the timing was deliberate, citing market constraints that have slowed hyperscale and colocation growth. “The current OEMs serving this market are actually limiting the ability to produce and to grow the data center market,” he noted. “Having another player … with enough capacity to compensate those shortfalls has been received very, very well.” While Generac isn’t seeking to reinvent the wheel, it is intent on differentiation. Customers, Navarro explained, want a good quality product, uneventful deployment, and a responsive support network. On top of those essentials, Generac is leveraging its ongoing transformation from generator manufacturer to energy technology company, a shift accelerated by a series of acquisitions in areas like telemetry, monitoring, and energy management. “We’ve made several acquisitions to move away from being just a generator manufacturer to actually being an energy technology company,” Navarro said. “So we are entering this space of energy efficiency, energy management—monitoring, telemetrics, everything that improves the experience and improves the usage of those generators and the energy management at sites.” That foundation positions Generac to meet the newest challenge reshaping backup generation: the rise of AI-centric workloads. Natural Gas Interest—and the Race to Shorter Lead Times As the industry looks beyond diesel, customer interest in natural gas generation is rising. Navarro acknowledged the shift, but noted that diesel still retains an edge. “We’ve seen an increase on gas requests,” he said. “But the power density of diesel is more convenient than gas today.” That tradeoff, however, could narrow. Navarro pointed to innovations such as industrial storage paired with gas units, which

Executive Roundtable: Cooling, Costs, and Integration in the AI Data Center Era

Becky Wacker, Trane: As AI workloads increasingly dominate new data center builds, operators face significant challenges in managing thermal loads and water resources. These challenges include significantly higher heat density, large, aggregated load spikes, uneven distribution of cooling needs, and substantial water requirements if using traditional evaporative cooling methods. The most critical risks include overheating, inefficient cooling systems, and water scarcity. These issues can lead to reduced hardware lifespan, hardware throttling, sudden shutdowns, failure to meet PUE targets, higher operational costs, and limitations on where AI data centers can be built due to water constraints. At Trane, we are evolving our solutions to meet these challenges through advanced cooling technologies such as liquid cooling and immersion cooling, which offer higher efficiency and lower thermal resistance compared to traditional air-cooling methods. Flexibility and scalability are central to our design philosophy. We believe a total system solution is crucial, integrating components such as CDUs, Fan Walls, CRAHs, and Chillers to anticipate demand and respond effectively. In addition, we are developing smart monitoring and control systems that leverage AI to predict and manage thermal loads in real-time, ensuring optimal performance and preventing overheating through Building Management Systems and integration with DCIM platforms. Our water management solutions are also being enhanced to recycle and reuse water, minimizing consumption and addressing scarcity concerns.

Power shortages are the only thing slowing the data center market

Another major shortage – which should not be news to anyone – is power. Lynch said that it is the primary reason many data centers are moving out of the heavily congested areas, like Northern Virginia and Santa Clara, and into secondary markets. Power is more available in smaller markets than larger ones. “If our client needs multi-megawatt capacity in Silicon Valley, we’re being told by the utility providers that that capacity will not be available for up to 10 years from now,” so out of necessity, many have moved to secondary markets, such as Hillsborough, Oregon, Reno, Nevada, and Columbus, Ohio. The growth of hyperscalers as well as AI is driving up the power requirements of facilities further into the multi-megawatt range. The power industry moves at a very different pace than the IT world, much slower and more deliberate. Lynch said the lead time for equipment makes it difficult to predict when some large scale, ambitious data centers can be completed. A multi-megawatt facility may even require new transmission lines to be built out as well. This translates into longer build times for new data centers. CBRE found that the average data center now takes about three years to complete, up from 2 years just a short time ago. Intel, AMD, and Nvidia haven’t even laid out a road map for three years, but with new architectures coming every year, a data center risks being obsolete by the time it’s completed. However, what’s the alternative? To wait? Customers will never catch up at that rate, Lynch said. That is simply not a viable option, so development and construction must go on even with short supplies of everything from concrete and steel to servers and power transformers.

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