Get ONMINE

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.

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.

Shape
Shape
Stay Ahead

Explore More Insights

More Articles

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.

Shape

Energy Department Approves Export Expansion at Corpus Christi LNG

CORPUS CHRISTI, TX—U.S. Secretary of Energy Chris Wright today signed an export authorization for a 12% expansion in exports at Cheniere Energy’s Corpus Christi liquefied natural gas (LNG) Terminal.  Today’s authorization allows additional exports of up to 0.47 billion cubic feet per day (Bcf/d) of U.S. natural gas as LNG to non-Free Trade Agreement (non-FTA) countries from Trains 8 and 9 of the Corpus Christi Stage 3 Project, known as the Midscale Trains 8 & 9 Project. With today’s order, Corpus Christi LNG is now authorized to export a total of 4.45 Bcf/d, making it the second largest LNG export project in the U.S. Secretary Wright announced the export expansion approval during a visit to the terminal. He also highlighted the United States’ leadership in LNG exports and the recent 10-year anniversary of the first cargo of U.S. LNG from the lower-48 states. “In the last ten years, American innovation and President Trump’s leadership transformed the United States into the world’s largest exporter of LNG,” said Secretary Wright. “This order helps further strengthen America’s LNG export capacity, delivering peace abroad and prosperity for Americans at home. I could not be prouder to be here today in Corpus Christi, standing alongside the American workers responsible for unleashing American energy dominance.”  “Every action we’re taking is focused on providing more reliable and secure energy to the world,” said Kyle Haustveit, Assistant Secretary of the Hydrocarbons and Geothermal Energy Office. “Our commitment to strengthening global partnerships through LNG exports helps to ensure a stable energy future and drive economic prosperity.”  Corpus Christi LNG has been operating as an export terminal since 2018, and Cheniere Energy announced a positive final investment decision of Trains 8 and 9 in June 2025. Today, the United States is the world’s largest producer of natural gas and LNG exporter.  Since the Trump Administration ended the previous administration’s LNG export approval ban to non-FTA countries, the Department has approved more than 18.2 Bcf/d

Read More »

Energy Department Announces $171.5 Million To Expand U.S. Geothermal Energy

Support for Field-Scale Tests and Exploration Drilling Can Help Advance Affordable, Reliable, Secure Geothermal Energy for American Homes and Businesses WASHINGTON—The U.S. Department of Energy (DOE) today announced a funding opportunity of $171.5 million to support next-generation geothermal field-scale tests for both electricity generation and exploration drilling to support characterization and potential confirmation of promising geothermal prospects. The activities enabled by this opportunity will help deliver on President Trump’s Executive Order, Unleashing American Energy by advancing geothermal technology, innovation, and exploration, in turn supporting the potential for geothermal energy to provide affordable, reliable, around-the-clock domestic electricity to Americans nationwide. “Work under this opportunity will directly support our commitments to advance energy addition, reduce energy costs for American families and businesses, and unleash American energy dominance and innovation,” said DOE Assistant Secretary of the Hydrocarbons and Geothermal Energy Office Kyle Haustveit. “Thanks to President Trump’s America First Energy Agenda, these demonstrations and drilling activities will help us realize the enormous potential of geothermal to spur domestic manufacturing, enable data center growth, and provide affordable, reliable, and secure energy solutions nationwide.” The funding opportunity includes six topics with varied levels of funding and awards anticipated. For the first round of applications, two of the six topics will be open, seeking field tests for enhanced geothermal systems and drilling for next-generation and hydrothermal resource characterization / confirmation. Although the United States leads the world in geothermal electricity capacity with about four gigawatts, DOE analysis shows the potential for at least 300 gigawatts of reliable, flexible geothermal power on the U.S. grid by 2050. Projects under this opportunity are expected to help derisk geothermal development approaches and locations nationwide, which can encourage private investment, spur industry growth, and help realize the country’s geothermal potential. Letters of Intent for the opportunity are due March 27, 2026, and full applications are due April 30, 2026.

Read More »

Energy Department Announces Largest Loan in Department History, Delivering Over $7 Billion in Electricity Cost Savings for Georgia and Alabama Customers

WASHINGTON—U.S. Secretary of Energy Chris Wright today announced the Department of Energy’s (DOE) Office of Energy Dominance Financing (EDF) has closed a historic $26.5 billion loan package to deliver over $7 billion in electricity cost savings to millions of customers in Georgia and Alabama. In accordance with President Trump’s Executive Order, Unleashing American Energy, this unprecedented loan package will support two wholly owned subsidiaries of Southern Company. Funded under President Trump’s Working Families Tax Cut, the investment will lower American energy costs, create thousands of jobs, and increase grid reliability in Georgia and Alabama. “Thanks to President Trump and the Working Families Tax Cut, the Energy Department is lowering energy costs and ensuring the American people have access to affordable, reliable, and secure energy for decades to come,” said Secretary Wright. “The President has been clear: America must reverse the energy subtraction agenda of past administrations and add more reliable power generation to our electrical grid. These loans will not only lower energy costs but also create thousands of jobs and increase grid reliability for the people of Georgia and Alabama.” The two loans will build or upgrade over 16 gigawatts (GW) of firm reliable power to the electrical grid. This includes 5 GW of new gas generation, 6 GW in nuclear improved through upgrades license renewals, hydropower modernization, battery energy storage systems and over 1,300 miles of transmission and grid enhancement projects. These loans represent the largest government investment aimed at directly lowering consumer energy costs and increasing grid reliability. Once all funds are received through the program, the loans are estimated to reduce Southern Company’s interest expenses by over $300 million per year, helping expedite lower electricity costs for customers. Southern Company is among the first utilities working with the DOE and the Trump Administration to restore American

Read More »

Energy Secretary Keeps Critical Generation Online in Mid-Atlantic

Emergency order keeps critical generation online and addresses critical grid reliability issues facing the Mid-Atlantic region of the United States WASHINGTON—U.S. Secretary of Energy Chris Wright issued an emergency order to address critical grid reliability issues facing the Mid-Atlantic region of the United States. The emergency order directs PJM Interconnection, L.L.C. (PJM), in coordination with Constellation Energy Corporation, to ensure Units 3 and 4 of the Eddystone Generating Station in Pennsylvania remain available for operation and to employ economic dispatch to minimize costs for the American people. The units were originally slated to shut down on May 31, 2025. “The energy sources that perform when you need them most are inherently the most valuable—that’s why natural gas and oil were valuable during recent winter storms,” Secretary Wright said. “Hundreds of American lives have likely been saved because of President Trump’s actions keeping critical generation online, including this Pennsylvania generating station which ran during Winter Storm Fern. This emergency order will mitigate the risk of blackouts and maintain affordable, reliable, and secure electricity access across the region.” The Eddystone Units were integral in stabilizing the grid during Winter Storm Fern. Between January 26-29, the units ran for over 124 hours cumulatively, providing critical generation in the midst of the energy emergency. As outlined in DOE’s Resource Adequacy Report, power outages could increase by 100 times in 2030 if the U.S. continues to take reliable power offline. Furthermore, NERC’s 2025 Long-Term Reliability Assessment warns, “The continuing shift in the resource mix toward weather-dependent resources and less fuel diversity increases risks of supply shortfalls during winter months.” Secretary Wright ordered that the two Eddystone Generating Station units remain online past their planned retirement date in a May 30, 2025 emergency order. Subsequent orders were issued on August 28, 2025 and November 26, 2025. Keeping these units operational

Read More »

Insights: Venezuela – new legal frameworks vs. the inertia of history

@import url(‘https://fonts.googleapis.com/css2?family=Inter:[email protected]&display=swap’); a { color: var(–color-primary-main); } .ebm-page__main h1, .ebm-page__main h2, .ebm-page__main h3, .ebm-page__main h4, .ebm-page__main h5, .ebm-page__main h6 { font-family: Inter; } body { line-height: 150%; letter-spacing: 0.025em; font-family: Inter; } button, .ebm-button-wrapper { font-family: Inter; } .label-style { text-transform: uppercase; color: var(–color-grey); font-weight: 600; font-size: 0.75rem; } .caption-style { font-size: 0.75rem; opacity: .6; } #onetrust-pc-sdk [id*=btn-handler], #onetrust-pc-sdk [class*=btn-handler] { background-color: #c19a06 !important; border-color: #c19a06 !important; } #onetrust-policy a, #onetrust-pc-sdk a, #ot-pc-content a { color: #c19a06 !important; } #onetrust-consent-sdk #onetrust-pc-sdk .ot-active-menu { border-color: #c19a06 !important; } #onetrust-consent-sdk #onetrust-accept-btn-handler, #onetrust-banner-sdk #onetrust-reject-all-handler, #onetrust-consent-sdk #onetrust-pc-btn-handler.cookie-setting-link { background-color: #c19a06 !important; border-color: #c19a06 !important; } #onetrust-consent-sdk .onetrust-pc-btn-handler { color: #c19a06 !important; border-color: #c19a06 !important; } In this Insights episode of the Oil & Gas Journal ReEnterprised podcast, Head of Content Chris Smith updates the evolving situation in Venezuela as the industry attempts to navigate the best path forward while the two governments continue to hammer out the details. The discussion centers on the new legal frameworks being established in both countries within the context of fraught relations stretching back for decades. Want to hear more? Listen in on a January episode highlighting industry’s initial take following the removal of Nicholas Maduro from power. References Politico podcast Monaldi Substack Baker webinar Washington, Caracas open Venezuela to allow more oil sales 

Read More »

Eni makes Calao South discovery offshore Ivory Coast

@import url(‘https://fonts.googleapis.com/css2?family=Inter:[email protected]&display=swap’); a { color: var(–color-primary-main); } .ebm-page__main h1, .ebm-page__main h2, .ebm-page__main h3, .ebm-page__main h4, .ebm-page__main h5, .ebm-page__main h6 { font-family: Inter; } body { line-height: 150%; letter-spacing: 0.025em; font-family: Inter; } button, .ebm-button-wrapper { font-family: Inter; } .label-style { text-transform: uppercase; color: var(–color-grey); font-weight: 600; font-size: 0.75rem; } .caption-style { font-size: 0.75rem; opacity: .6; } #onetrust-pc-sdk [id*=btn-handler], #onetrust-pc-sdk [class*=btn-handler] { background-color: #c19a06 !important; border-color: #c19a06 !important; } #onetrust-policy a, #onetrust-pc-sdk a, #ot-pc-content a { color: #c19a06 !important; } #onetrust-consent-sdk #onetrust-pc-sdk .ot-active-menu { border-color: #c19a06 !important; } #onetrust-consent-sdk #onetrust-accept-btn-handler, #onetrust-banner-sdk #onetrust-reject-all-handler, #onetrust-consent-sdk #onetrust-pc-btn-handler.cookie-setting-link { background-color: #c19a06 !important; border-color: #c19a06 !important; } #onetrust-consent-sdk .onetrust-pc-btn-handler { color: #c19a06 !important; border-color: #c19a06 !important; } Eni SPA discovered gas and condensate in the Murene South-1X exploration well in Block CI-501, Ivory Coast. The well is the first exploration in the block and was drilled by the Saipem Santorini drilling ship about 8 km southwest of the Murene-1X discovery well in adjacent CI-205 block. The well was drilled to about 5,000 m TD in 2,200 m of water. Extensive data acquisition confirmed a main hydrocarbon bearing interval in high-quality Cenomanian sands with a gross thickness of about 50 m with excellent petrophysical properties, the operator said. Murene South-1X will undergo a full conventional drill stem test (DST) to assess the production capacity of this discovery, named Calao South. Calao South confirms the potential of the Calao channel complex that also includes the Calao discovery. It is the second largest discovery in the country after Baleine, with estimated volumes of up to 5.0 tcf of gas and 450 million bbl of condensate (about 1.4 billion bbl of oil). Eni is operator of Block CI-501 (90%) with partner Petroci Holding (10%).

Read More »

Enterprise Spotlight: Data Center Modernization

The demands for, and challenges of, deploying AI applications has ratcheted up the urgency to bring data centers into the AI age. It’s a strategic imperative and success requires partners across the infrastructure spectrum, from servers and storage to high-performance computing, networking, software, and security. IT leaders, intensely focused on data center modernization, need strategies, roadmaps, and products that will get them there. Download the March 2026 issue of the Enterprise Spotlight from the editors of CIO, Computerworld, CSO, InfoWorld, and Network World and learn how data center modernization is taking shape in 2026.

Read More »

Why do data centers need so much water?

Another legacy cooling technology in data centers is what’s called a cooling tower. A cooling tower sits outside of the main building, and the water cascades down these towers like a waterfall. However, the tower is open to the atmosphere to let natural cooling in. The churn of the water dissipates the heat, but there is significant evaporation in the process. “It evaporates a lot. I mean, we’re talking many, many Olympic swimming pools worth of water on a daily basis in some of these data centers,” said Green. “Some of the hyperscalers I work with are still using open cooling tower solutions, even today.” There were other reasons for using evaporation. For starters, evaporation equipment takes up a lot less space the chilled water equipment. Secondly is the price. Chilled water-cooling costs about 10% to 15% more than equivalent evaporation technology. But that is changing, Green notes, as more and more societal pressure, economic pressure around water consumption continues to move to the forefront, data centers are being forced to adapt. “We’re in a market now where we can use air cooled chillers that don’t evaporate water like a water-cooled chiller does, and have a very, very similar level of overall system efficiency,” he said. We are also seeing the advent of closed loop technology, where liquid is pumped into a system to absorb heat and then pumped out to be cooled and recirculated, much like a car radiator. Gamers have been on the forefront of liquid cooling and closed loop with all-in-one coolers for gaming PCs becoming standard issue now.

Read More »

Netskope targets AI-driven network bottlenecks with AI Fast Path

AI Fast Path focuses on optimizing traffic flows between enterprise users, the Netskope cloud, and major AI providers. Netskope says more than 90% of its 120 NewEdge data centers can now connect to leading AI applications in less than five milliseconds from the Netskope cloud, an effort aimed at minimizing added delay as traffic is inspected for data loss prevention (DLP), threat protection, and policy enforcement. “Customers realized that if they don’t adopt these AI apps, they’re probably going to be extinct in a few years. At the same time, we can’t afford to compromise on security,” Arandjelovic says. “So, with NewEdge and the AI Fast Path, we’ve created a super-optimized path where there is literally barely a bump in the wire. At the same time, they are not compromising security, because you’re passing through our cloud and getting all the benefits of our data protection and threat protection.” As a set of capabilities within NewEdge, AI Fast Path enables better performance and efficiency for AI applications. According to Netskope, AI Fast Path provides enterprises with:

Read More »

AMD strikes massive AI chip deal with Meta

The funding is also unique. Instead of a cash purchase, AMD has reportedly given Meta warrants to buy up to 160 million shares at $0.01 each. Stock warrants are financial instruments that give you the right (but not the obligation) to buy a company’s stock at a fixed price before a certain expiration date, according to the vendors. With 1.6 billion shares outstanding, Meta is poised to acquire 10% of AMD. But perhaps not. These shares vest only as Meta buys more computing capacity. The final tranche vests only if AMD’s stock price hits $600, according to a recent 8K filing. AMD shares are currently valued at just over $200 as of this writing. The deal is identical to the one AMD struck with OpenAI last October. That deal was also for 6 GW worth of GPUs and included a warrant for up to 160 million AMD common stock shares structured to payout once certain targets were met. Meta is not playing favorites. Last week it announced that it will also deploy standalone Nvidia Grace CPUs in its production data centers, citing greatly improved performance-per-watt. That doesn’t come as a surprise to Gaurav Gupta, vice president analyst at Gartner, who says we are compute constrained and Hyperscalers or frontier model companies will use a multisource approach to get access to compute.  “No one wants to be stuck with a single vendor. Diversify and then different workloads have different compute needs.,” he said.

Read More »

Nvidia lines up partners to boost security for industrial operations

Akamai extends its micro-segmentation and zero-trust security platform Guardicore to run on Nvidia BlueField GPUs The integration offloads user-configurable security processes from the host system to the Nvidia BlueField DPU and enables zero-trust segmentation without requiring software agents on fragile or legacy systems, according to Akamai. Organizations can implement this hardware-isolated, “agentless” security approach to help align with regulatory requirements and lower their risk profile for cyber insurance. “It delivers deep, out-of-band visibility across systems, networks, and applications without disrupting operations. Security policies can be enforced in real time and are capable of creating a strong protective boundary around critical operational systems. The result is trusted insight into operational activity and improved overall cyber resilience,” according to Akamai. Forescout works with Nvidia to bring zero-trust technology to OT networks Forescout applies network segmentation to contain lateral movement and enforce zero-trust controls. The technology would be further integrated into partnership work already being done by the two companies. By running Forescout’s on-premises sensor directly on the Nvidia BlueField, part of Nvidia Cybersecurity AI platform, customers can offload intensive computing tasks, such as deep packet inspections. This speeds up data processing, enhances asset intelligence, and improves real-time monitoring, providing security teams with the insights needed to stay ahead of emerging threats, according to Forescout. Palo Alto to demo Prisma AIRS AI Runtime Security on Nvidia BlueField DPU Palo Alto Networks recently partnered with Nvidia to run its Prisma AI-powered Radio Security(AIRs) package on the Nvidia BlueField DPU and will show off the technology at the conference. The technology is part of the Nvidia Enterprise AI Factory validated design and can offer real-time security protection for industrial network settings. “Prisma AIRS AI Runtime Security delivers deep visibility into industrial traffic and continuous monitoring for abnormal behavior. By running these security services on Nvidia BlueField, inspection

Read More »

Raising the temp on liquid cooling

IBM isn’t the only one. “We’ve been doing liquid cooling since 2012 on our supercomputers,” says Scott Tease, vice president and general manager of AI and high-performance computing at Lenovo’s infrastructure solutions group. “And we’ve been improving it ever since—we’re now on the sixth generation of that technology.” And the liquid Lenovo uses in its Neptune liquid cooling solution is warm water. Or, more precisely, hot water: 45 degrees Celsius. And when the water leaves the servers, it’s even hotter, Tease says. “I don’t have to chill that water, even if I’m in a hot climate,” he says. Even at high temperatures, the water still provides enough cooling to the chips that it has real value. “Generally, a data center will use evaporation to chill water down,” Tease adds. “Since we don’t have to chill the water, we don’t have to use evaporation. That’s huge amounts of savings on the water. For us, it’s almost like a perfect solution. It delivers the highest performance possible, the highest density possible, the lowest power consumption. So, it’s the most sustainable solution possible.” So, how is the water cooled down? It gets piped up to the roof, Tease says, where there are giant radiators with massive amounts of surface area. The heat radiates away, and then all the water flows right back to the servers again. Though not always. The hot water can also be used to, say, heat campus or community swimming pools. “We have data centers in the Nordics who are giving the heat to the local communities’ water systems,” Tease says.

Read More »

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.

Read More »

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

Read More »

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

Read More »

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

Read More »
More Articles