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.

Amazon layoffs expected to disproportionately hit AWS and tech talent

Jassy explained: “If you grow as fast as we did for several years, the size of businesses, the number of people, the number of locations, the types of businesses you’re in, you end up with a lot more people than what you had before, and you end up with a

Rust 1.93 updates bundled musl library to boost networking

The Rust team has unveiled Rust 1.93, the latest version of the programming language designed to create fast and safe system-level software. This release improves operations involving the DNS resolver for the musl implementation of the C standard library. Linux binaries are expected to be more reliable for networking as

Intel nabs Qualcomm veteran to lead GPU initiative

Intel has struggled for more than two decades to develop a successful GPU/accelerated computing strategy, going all the way back to the aughts and the ill-fated Larrabee effort. Its most recent efforts centered around Ponte Vecchio and Gaudi chips, neither of which have gained any traction. Still, CEO Lip-Bu Tan

New Relic extends observability into ChatGPT-hosted apps

New Relic’s cloud-based observability platform monitors applications and services in real time to provide insights into software, hardware, and cloud performance. The new capability extends the platform’s browser agent into the GPT iframe environment. It captures standard telemetry data, including latency and connectivity of an application within the GPT iframe.

SLB Predicts Worst Is Behind Global Oil Market

SLB, the world’s largest oilfield-services provider, raised its dividend and posted fourth-quarter earnings that beat estimates as activity in the Middle East and other key regions accelerated and its data-center business rapidly expanded. The worst may be behind the global oil market, Chief Executive Officer Olivier Le Peuch said in a statement, predicting a gradual ramp-up in drilling activity in major regions including OPEC countries after a supply glut sent crude prices tumbling last year. Deriving the bulk of its revenue from overseas markets, Houston-based SLB is often regarded as a bellwether for the global oil industry and its financial health. Shares rose by as much as 4.8% to $51.67, briefly hitting the highest price since April 2024 before paring gains. “As we move into 2026, we believe that the headwinds we experienced in key regions in 2025 are behind us,” Le Peuch said. “In particular, we expect rig activity in the Middle East to increase compared to today’s level, and our footprint in the region puts us in a strong position to benefit from this recovery.” The data-center business, which grew 121% from a year earlier, helped to shield the company from lower oil prices and geopolitical uncertainty, he added. SLB has also increased its focus on production and recovery services, which help drillers to boost efficiency and extract more crude at lower cost. SLB has been expanding into oilfield tech and other ancillary business lines to offset muted growth in traditional drilling and US shale activity. SLB posted adjusted fourth-quarter earnings of 78 cents a share, surpassing analysts’ estimates of 74 cents. The company increased its quarterly divided 3.5% to 29.5 cents a share. The company’s global footprint positions it to benefit from US government efforts to revive Venezuelan oil production, Bloomberg Intelligence analyst Scott Levine wrote in

France Boards Oil Tanker Linked to Russia Shadow Fleet

France’s navy boarded an oil tanker coming from Russia into the Mediterranean Sea, as part of a global crackdown on shadow fleet ships used to export sanctioned crude. The operation was carried out on the high seas, with the support of several of France’s allies, French President Emmanuel Macron said on X. The vessel – the Grinch – is subject to international sanctions and suspected of flying a false flag, he said. The operation comes amid a step-up in pressure on the shadow fleet of aging tankers globally. The US has been seizing ships tied to Venezuela’s oil exports – one of which sought the shelter of the Russian flag – while European nations have long talked about tougher measures against aging ships sailing through their waters. “We are determined to uphold international law and to ensure the effective enforcement of sanctions,” Macron said. “The activities of the ‘shadow fleet’ contribute to financing the war of aggression against Ukraine.” A judicial investigation has been opened and the vessel has been diverted, Macron said. UN rules allow checks to be carried out on ships suspected of carrying false flags, according to a statement from the country’s administration for maritime affairs in the Mediterranean. The French navy said the tanker, which came from Murmansk on Russia’s Arctic coast, was boarded in the Alboran Sea, south of Spain, and taken to a mooring. The tanker was loaded with cargo at the time, vessel-tracking data compiled by Bloomberg show. While not giving a destination, it was sailing in the direction of the Suez Canal, a common waypoint for tankers taking Russian barrels to Asia. It disappeared from the industry’s digital tracking system on Wednesday, not long after passing Gibraltar. The Equasis international shipping database does not provide contact details for the manager of the Grinch. A clampdown on the shadow

Natural Gas Prices Across the USA Surge

Natural gas prices for near-term delivery at regional trading hubs across the US jumped as the market braced for a historic winter storm that’s poised to send temperatures plummeting and boost demand for the heating fuel. So-called cash prices for gas at the benchmark Henry Hub in Louisiana to be delivered over the weekend surged early Friday to $18.80 per million British thermal units, according to traders. That compares with $8.42 on Thursday. Spot prices at the SoCal Citygate hub in California traded as high as $8 per million Btu as gas volumes delivered via pipeline from the Permian Basin in West Texas to the West Coast have likely been reduced, traders said. That’s up from $4.42 on Thursday. This week’s surge has been driven by forecasts for below-normal temperatures across most of the country, threatening to boost gas consumption and drain inventories. The freeze — particularly in the southern gas-producing states — has raised concerns about water icing in pipelines, potentially disrupting output starting this weekend. US natural gas futures for February delivery, meanwhile, rose for a fourth straight day. They were up 6.3% to $5.362 per million Btu as of 9:22 a.m. in New York, heading for their biggest weekly gain in records going back to 1990. The shift in US weather forecasts came days after hedge funds turned more bearish on gas at the end of last week, leaving the market poised for a rally as traders rushed to close out those wagers. Gas prices briefly climbed above $5.50 per million Btu on Thursday, a level that a Citigroup Inc. analysis on Thursday showed would wipe out all shorts. WHAT DO YOU THINK? Generated by readers, the comments included herein do not reflect the views and opinions of Rigzone. All comments are subject to editorial review. Off-topic, inappropriate or

USA Crude Oil Stocks Increase Week on Week

U.S. commercial crude oil inventories, excluding those in the Strategic Petroleum Reserve (SPR), increased by 3.6 million barrels from the week ending January 9 to the week ending January 16, the U.S. Energy Information Administration (EIA) highlighted in its latest weekly petroleum status report. Crude oil stocks, not including the SPR, stood at 426.0 million barrels on January 16, 422.4 million barrels on January 9, and 411.7 million barrels on January 17, 2025, the EIA report, which was released on January 22 and included data for the week ending January 16, showed. Crude oil in the SPR stood at 414.5 million barrels on January 16, 413.7 million barrels on January 9, and 394.6 million barrels on January 17, 2025, the report revealed. Total petroleum stocks – including crude oil, total motor gasoline, fuel ethanol, kerosene type jet fuel, distillate fuel oil, residual fuel oil, propane/propylene, and other oils – stood at 1.722 billion barrels on January 16, the report highlighted. Total petroleum stocks were up 8.3 million barrels week on week and up 100.3 million barrels year on year, the report pointed out. “At 426.0 million barrels, U.S. crude oil inventories are about two percent below the five year average for this time of year,” the EIA said in its latest weekly petroleum status report. “Total motor gasoline inventories increased by 6.0 million barrels from last week and are about five percent above the five year average for this time of year. Both finished gasoline and blending components inventories increased last week,” it added. “Distillate fuel inventories increased by 3.3 million barrels last week and are about one percent below the five year average for this time of year. Propane/propylene inventories decreased 2.1 million barrels from last week and are about 39 percent above the five year average for this

Alaska LNG Secures Preliminary Deals with Suppliers, Offtakers

Glenfarne Group LLC said Thursday it had signed several preliminary agreements to source natural gas for and sell output from Alaska LNG, designed to also supply the domestic market, as well as conditionally awarded build contracts. Alaska LNG holds an Energy Department permit to export 20 million metric tons per annum (MMtpa) of LNG, or 2.55 billion cubic feet a day of natural gas equivalent according to Alaska LNG, to both FTA and non-FTA countries. The project secured the authorization November 2014 for the portion for countries with a free trade agreement (FTA) with the United States and August 2020 for the non-FTA portion. On December 11, 2025 the Federal Permitting Improvement Steering Council announced the completion of permit renewal for the project, which would process gas from the North Slope for both the domestic and overseas markets, following a review of environmental opinions. On Thursday Glenfarne said it has executed “gas sales precedent agreements” with Exxon Mobil Corp and Hilcorp Energy Co for the supply of gas from phase 1 of Alaska LNG. The Houston, Texas-based developer did not disclose any prospective contract volume. Glenfarne has a previous gas sales precedent agreement with Pantheon Resources PLC. “These agreements, which include pricing, contract length and other fundamental commercial terms, are a monumental step in achieving the decades-long objective of bringing the benefits of Alaska’s incredible North Slope reserves to Alaskans and to global markets”, said Adam Prestidge, Glenfarne president for Alaska LNG. Thursday’s statement also said Glenfarne had signed a letter of intent with Alaskan utility ENSTAR Natural Gas Co for a 30-year supply of LNG from the project. The volume under negotiation was not disclosed. Earlier this month it announced a letter of intent with Donlin Gold LLC of Novagold Resources Inc for a power plant for the Donlin gold

Texas Governor Issues Disaster Declaration for 134 Counties

A release posted on the website of the Office of the Texas Governor on Thursday announced that Texas Governor Greg Abbott has issued a disaster declaration for 134 counties “to ensure that every necessary resource is made available to Texans”. A letter of transmittal, which accompanied the release, noted that Abbott issued “a proclamation declaring a disaster due to severe winter weather in a number of Texas counties”. “I, Greg Abbott, Governor of the State of Texas, do hereby certify that severe winter weather poses an imminent threat of widespread and severe property damage, injury, and loss of life due to prolonged freezing temperatures, heavy snow, icy conditions, and freezing rain in several counties,” Abbott stated in the original proclamation, which was attached to the letter. In a statement sent to Rigzone late Thursday by the AccuWeather team, AccuWeather said “a massive winter storm is forecast to unleash extreme freezing rain, damaging ice and heavy snow impacts across much of the southern, central, and eastern United States beginning Friday, followed by repeated waves of bitter cold”. “More than 200 million people could be affected, with impacts expected to cascade nationwide through widespread power outages, travel shutdowns, and business operations grinding to a halt,” the statement warned. In that AccuWeather statement, the company’s Chief Meteorologist Jonathan Porter said, “this winter storm will shut it all down”. “This storm will have a variety of dangerous and life-threatening hazards. The combination of freezing rain, ice and heavy snow, followed by a deep freeze, increases the risk of widespread travel shutdowns, extended business and commerce disruptions, and interruptions to daily life,” he warned. The AccuWeather statement noted that significant to severe freezing rain and ice impacts are forecast to begin in Texas on Friday, then extend across the South into the Carolinas over the weekend. In a “winter weather outlook” notice to oil and gas and pipeline operators posted on its website on January 20, the Texas Railroad

Reports of SATA’s demise are overblown, but the technology is aging fast

The SATA 1.0 interface made its debut in 2003. It was developed by a consortium consisting of Intel, Dell, and storage vendors like Seagate and Maxtor. It quickly advanced to SATA III in 2009, but there never was a SATA IV. There was just nibbling around the edges with incremental updates as momentum and emphasis shifted to PCI Express and NVMe. So is there any life to be had in the venerable SATA interface? Surprisingly, yes, say the analysts. “At a high level, yes, SATA for consumer is pretty much a dead end, although if you’re storing TB of photos and videos, it is still the least expensive option,” said Bob O’Donnell, president and chief analyst with TECHnalysis Research. Similarly for enterprise, for massive storage demands, the 20 and 30 TB SATA drives from companies like Seagate and WD are apparently still in wide use in cloud data centers for things like cold storage. “In fact, both of those companies are seeing recording revenues based, in part, on the demand for these huge, high-capacity low-cost drives,” he said. “SATA doesn’t make much sense anymore. It underperforms NVMe significantly,” said Rob Enderle, principal analyst with The Enderle Group. “It really doesn’t make much sense to continue make it given Samsung allegedly makes three to four times more margin on NVMe.” And like O’Donnell, Enderle sees continued life for SATA-based high-capacity hard drives. “There will likely be legacy makers doing SATA for some time. IT doesn’t flip technology quickly and SATA drives do wear out, so there will likely be those producing legacy SATA products for some time,” he said.

DCN becoming the new WAN for AI-era applications

“DCN is increasingly treated as an end-to-end operating model that standardizes connectivity, security policy enforcement, and telemetry across users, the middle mile, and cloud/application edges,” Sanchez said. Dell’Oro defines DCN as platforms and services that deliver consistent connectivity, policy enforcement, and telemetry from users, across the WAN, to distributed cloud and application edges spanning branch sites, data centers and public clouds. The category is gaining relevance as hybrid architectures and AI-era traffic patterns increase the operational penalty for fragmented control planes. DCN buyers are moving beyond isolated upgrades and are prioritizing architectures that reduce operational seams across connectivity, security and telemetry so that incident response and change control can follow a single thread, according to Dell’Oro’s research. What makes DCN distinct is that it links user-to-application experience with where policy and visibility are enforced. This matters as application delivery paths become more dynamic and workloads shift between on-premises data centers, public cloud, and edge locations. The architectural requirement is eliminating handoffs between networking and security teams rather than optimizing individual network segments. Where DCN is growing the fastest Cloud/application edge is the fastest-growing DCN pillar. This segment deploys policy enforcement and telemetry collection points adjacent to workloads rather than backhauling traffic to centralized security stacks. “Multi-cloud remains a reality, but it is no longer the durable driver by itself,” Sanchez said. “Cloud/application edge is accelerating because enterprises are trying to make application paths predictable and secure across hybrid environments, and that requires pushing application-aware steering, policy enforcement, and unified telemetry closer to workloads.”

Edged US Builds Waterless, High-Density AI Data Center Campuses at Scale

Edged US is targeting a narrow but increasingly valuable lane of the hyperscale AI infrastructure market: high-density compute delivered at speed, paired with a sustainability posture centered on waterless, closed-loop cooling and a portfolio-wide design PUE target of roughly 1.15. Two recent announcements illustrate the model. In Aurora, Illinois, Edged is developing a 72-MW facility purpose-built for AI training and inference, with liquid-to-chip cooling designed to support rack densities exceeding 200 kW. In Irving, Texas, a 24-MW campus expansion combines air-cooled densities above 120 kW per rack with liquid-to-chip capability reaching 400 kW. Taken together, the projects point to a consistent strategy: standardized, multi-building campuses in major markets; a vertically integrated technical stack with cooling at its core; and an operating model built around repeatable designs, modular systems, and readiness for rapidly escalating AI densities. A Campus-First Platform Strategy Edged US’s platform strategy is built around campus-scale expansion rather than one-off facilities. The company positions itself as a gigawatt-scale, AI-ready portfolio expanding across major U.S. metros through repeatable design targets and multi-building campuses: an emphasis that is deliberate and increasingly consequential. In Chicago/Aurora, Edged is developing a multi-building campus with an initial facility already online and a second 72-MW building under construction. Dallas/Irving follows the same playbook: the first facility opened in January 2025, with a second 24-MW building approved unanimously by the city. Taken together with developments in Atlanta, Chicago, Columbus, Dallas, Des Moines, Kansas City, and Phoenix, the footprint reflects a portfolio-first mindset rather than a collection of bespoke sites. This focus on campus-based expansion matters because the AI factory era increasingly rewards developers that can execute three things at once: Lock down power and land at scale. Standardize delivery across markets. Operate efficiently while staying aligned with community and regulatory expectations. Edged is explicitly selling the second

CBRE’s 2026 Data Center Outlook: Demand Surges as Delivery Becomes the Constraint

The U.S. data center market is entering 2026 with fundamentals that remain unmatched across commercial real estate, but the nature of the dominant constraint has shifted. Demand is no longer gated by capital, connectivity, or even land. It is gated by the ability to deliver very large blocks of power, on aggressive timelines, at a predictable cost. According to the CBRE 2026 U.S. Real Estate Market Outlook as overseen by Gordon Dolven and Pat Lynch, the sector is on track to post another record year for leasing activity, even as vacancy remains at historic lows and pricing reaches all-time highs. What has changed is the scale at which demand now presents itself, and the difficulty of meeting it. Large-Block Leasing Rewrites the Economics AI-driven workloads are reshaping leasing dynamics in ways that break from prior hyperscale norms. Where 10-MW-plus deployments once commanded pricing concessions, CBRE now observes the opposite behavior: large, contiguous blocks of capacity are commanding premiums. Neocloud providers, GPU-as-a-service platforms and AI startups, many backed by aggressive capital deployment strategies, are actively competing for full-building and campus-scale capacity. For operators, this is altering development and merchandising strategies. Rather than subdividing shells for flexibility, owners increasingly face a strategic choice: hold buildings intact to preserve optionality for single-tenant, high-density users who are willing to pay for scale. In effect, scale itself has become the scarce asset. Behind-the-Meter Power Moves to the Foreground For data centers, power availability meaning not just access, but certainty of delivery, is now the defining variable in the market. CBRE notes accelerating adoption of behind-the-meter strategies as operators seek to bypass increasingly constrained utility timelines. On-site generation using natural gas, solar, wind, and battery storage is gaining traction, particularly in deregulated electricity markets where operators have more latitude to structure BYOP (bring your own power) solutions.

Blue Origin targets enterprise networks with a multi-terabit satellite connectivity plan

“It’s ideal for remote, sparse, or sensitive regions,” said Manish Rawat, analyst at TechInsights. “Key use cases include cloud-to-cloud links, data center replication, government, defense, and disaster recovery workloads. It supports rapid or temporary deployments and prioritizes fewer customers with high capacity, strict SLAs, and deep carrier integration.” Adoption, however, is expected to largely depend on the sector. For governments and organizations operating highly critical or sensitive infrastructure, where reliability and security outweigh cost considerations, this could be attractive as a redundancy option. “Banks, national security agencies, and other mission-critical operators may consider it as an alternate routing path,” Jain said. “For most enterprises, however, it is unlikely to replace terrestrial connectivity and would instead function as a supplementary layer.” Real-world performance Although satellite connectivity offers potential advantages, analysts note that questions remain around real-world performance. “TeraWave’s 6 Tbps refers to total constellation capacity, not per-user throughput, achieved via multiple optical inter-satellite links and ground gateways,” Rawat said. “Optical crosslinks provide high aggregate bandwidth but not a single terabit-class pipe. Performance lies between fiber and GEO satellites, with lower intercontinental latency than GEO but higher than fiber.” Operational factors could also affect network stability. Jitter is generally low, but handovers, rerouting, and weather conditions can introduce intermittent performance spikes. Packet loss is expected to remain modest but episodic, Rawat added.

CyrusOne Hones AI-Era Data Center Strategy for Power, Pace, and Reliability

In the second half of 2025, CyrusOne was racing to secure buildable power and faster time-to-market capacity for AI-era customers. At the same time, its reputation for mission-critical reliability took a very public hit when a disruption at a CyrusOne facility helped knock CME trading offline. The incident forced the company into an unusually open conversation about redundancy, cooling systems, and operational discipline: systems that are meant to disappear in normal operation, and dominate the story when they malfunction. From Projects to a Playbook Which projects, missteps, and strategic moves from 2025 are now shaping how CyrusOne enters 2026? Nowhere is that view clearer than in Texas. There, CyrusOne has been leaning hard into a “power + land + interconnect” model: treating deliverable power and grid position as part of the product, not just a prerequisite. If you map the company’s announcements since late July, Texas reveals the playbook. Secure power, secure substations and grid position, then build multi-phase campuses designed to scale quickly as demand materializes. The Calpine “Powered Land” Deal: From 190 MW to 400 MW in Three Months On July 30, 2025, CyrusOne and Calpine announced a 190-MW agreement tied to a hyperscale campus (DFW10) adjacent to Calpine’s Thad Hill Energy Center in Bosque County, Texas. The structure bundled power, grid connection, and land into a single development package, with CyrusOne saying the site was already under construction and targeting operation by Q4 2026. Just three months later, on November 3–4, the partners announced a second phase, adding 210 MW and taking the campus to 400 MW. The update emphasized coordination to support grid reliability during scarcity; such curtailment and operational-coordination concepts are becoming table stakes for ERCOT-scale megaprojects. Together, the two announcements show CyrusOne placing a large bet on an emerging model: power-ready campuses, or “powered

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