SQL Date and Time — Applied Merges, Workdays, Streaks

QUESTION 6

Merging intervals — Collapse overlapping periods into one

Window functionRunning maximumInterval mergingGap detection

Background ▼

To merge overlapping periods, order them by start and compare each start against "the largest end reached so far". If the current start goes past it, that is the beginning of a new period.

SELECT MAX(end_col) OVER (
         PARTITION BY key_col ORDER BY start_col, end_col
         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM table_name;
-- the end reached by rows strictly before this one

Compare against the running maximum, not the previous row: looking only at the end of the immediately preceding period misses the case where a short period sits entirely inside a long one. The comparison must be against the largest end so far.

Problem ▼

The maintenance windows in the maintenance table are half-open intervals [starts_on, ends_on). Per service, collapse windows that overlap or run back to back into a single period. When the end of one window equals the start of the next, treat them as continuous and merge them. Return service, starts_on, ends_on, windows (how many windows were merged), sorted by service then starts_on ascending.

Tables used

▸ maintenance

window_id	service	starts_on	ends_on
1	api	2026-05-01	2026-05-04
2	api	2026-05-03	2026-05-06
3	api	2026-05-06	2026-05-08
4	api	2026-05-12	2026-05-14
5	web	2026-05-02	2026-05-05
6	web	2026-05-09	2026-05-10

Expected Output ▼

service	starts_on	ends_on	windows
api	2026-05-01	2026-05-08	3
api	2026-05-12	2026-05-14	1
web	2026-05-02	2026-05-05	1
web	2026-05-09	2026-05-10	1

Model Answer ▼

WITH ordered AS (
  SELECT
    service, starts_on, ends_on,
    MAX(ends_on) OVER (PARTITION BY service ORDER BY starts_on, ends_on
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end  -- end reached so far
  FROM maintenance
), marked AS (
  SELECT
    service, starts_on, ends_on,
    CASE WHEN prev_max_end IS NULL OR starts_on > prev_max_end
         THEN 1 ELSE 0 END AS is_new   -- a gap starts a new period
  FROM ordered
), grouped AS (
  SELECT
    service, starts_on, ends_on,
    SUM(is_new) OVER (PARTITION BY service ORDER BY starts_on, ends_on) AS grp  -- running total is the group id
  FROM marked
)
SELECT
  service,
  MIN(starts_on) AS starts_on,
  MAX(ends_on)   AS ends_on,
  COUNT(*)       AS windows
FROM     grouped
GROUP BY service, grp
ORDER BY service, starts_on;

/*
  Execution order (logical evaluation order):
  1. FROM maintenance             → Read 6 rows
  2. MAX(ends_on) OVER …          → Largest end among earlier rows
  3. CASE flags the boundaries    → 1 where a gap exists
  4. SUM(is_new) OVER running     → Fix the merged-period id
  5. GROUP BY service, grp        → Ends and count per group
  6. ORDER BY service, starts_on  → Ascending by service and start
  */

Explanation (table transitions & key points) ▼

WITH ordered AS (
  SELECT service, starts_on, ends_on,
         MAX(ends_on) OVER (PARTITION BY service ORDER BY starts_on, ends_on
                               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
  FROM maintenance
), marked AS (
  SELECT service, starts_on, ends_on,
         CASE WHEN prev_max_end IS NULL OR starts_on > prev_max_end
              THEN 1 ELSE 0 END AS is_new
  FROM ordered
), grouped AS (
  SELECT service, starts_on, ends_on,
         SUM(is_new) OVER (PARTITION BY service ORDER BY starts_on, ends_on) AS grp
  FROM marked
)
SELECT service,
       MIN(starts_on) AS starts_on,
       MAX(ends_on)   AS ends_on,
       COUNT(*)       AS windows
FROM     grouped
GROUP BY service, grp
ORDER BY service, starts_on;

LEGEND ▼

Rows read / loaded

① FROM maintenance ▼

FROM maintenanceRead all 6 rows of maintenance. The api service has overlapping, touching and separated windows; web has two separated ones.

1 / 6

window_id	service	starts_on	ends_on
1	api	2026-05-01	2026-05-04
2	api	2026-05-03	2026-05-06
3	api	2026-05-06	2026-05-08
4	api	2026-05-12	2026-05-14
5	web	2026-05-02	2026-05-05
6	web	2026-05-09	2026-05-10

All 6 rows read

LEARNING POINTS

Merging is really boundary detection: flagging "is there a gap" and turning the running total into a group id is the same two-step used for sessionization. Only the test changes, from "the time gap reaches a threshold" to "the start goes past the end reached so far".

Why the frame is required: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING excludes the current row's own end from the comparison. The default frame includes the current row, which makes the value always at least the current end, so no boundary is ever found.

Touching intervals are a policy choice: here starts_on > prev_max_end merges windows that only touch. Use >= instead if touching windows should stay separate.

ANTI-PATTERNS

Comparing with LAG against the previous end: looking only at the immediately preceding row falsely reports a boundary right after a short window nested inside a long one. The comparison must use the running maximum.

Omitting PARTITION BY: ordering everything together lets an api end date influence a web decision. Merging must always be closed over the unit being merged.

Field Notes

Building the denominator for uptime: when summing downtime, adding overlapping maintenance windows as-is double counts. Merge the intervals first so the real outage time is a single period, then divide that total by the reporting window. PostgreSQL can do this merge in one call with the range aggregate range_agg, which reads better than the window version; the window version is still worth knowing because it ports to other databases unchanged.

QUESTION 7

Working days — Count days excluding weekends and holidays

generate_seriesLATERALWorking daysNOT EXISTS

Background ▼

Counting working days means expanding a period one day at a time and counting the days that qualify. Because the expansion depends on each left-hand row, put generate_series inside a CROSS JOIN LATERAL subquery.

SELECT t.id, x.days
FROM   table_name t
CROSS JOIN LATERAL (
  SELECT COUNT(*) AS days
  FROM   generate_series(t.start_col, t.end_col, INTERVAL '1 day') AS d(day)
  WHERE  EXTRACT(ISODOW FROM d.day) <= 5) x;   -- 1=Mon … 5=Fri

ISODOW suits weekday tests: it runs 1=Monday to 7=Sunday, so <= 5 alone selects weekdays. With DOW (0=Sunday to 6=Saturday) you have to list the weekend as NOT IN (0, 6).

Problem ▼

For each task in tasks, compute the number of working days from the start date through the due date. A working day is Monday through Friday that does not appear in the holidays table, and both the start date and the due date are included. Return task_id, start_on, due_on, business_days, sorted by task_id ascending.

Tables used

▸ tasks

task_id	start_on	due_on
1	2026-05-01	2026-05-07
2	2026-05-04	2026-05-08
3	2026-05-08	2026-05-08

▸ holidays

holiday_on	name
2026-05-04	constitution day
2026-05-06	substitute holiday

Expected Output ▼

task_id	start_on	due_on	business_days
1	2026-05-01	2026-05-07	3
2	2026-05-04	2026-05-08	3
3	2026-05-08	2026-05-08	1

Model Answer ▼

SELECT
  t.task_id,
  t.start_on,
  t.due_on,
  b.business_days
FROM   tasks t
CROSS JOIN LATERAL (
  SELECT COUNT(*) AS business_days
  FROM   generate_series(t.start_on, t.due_on, INTERVAL '1 day') AS d(day)   -- expand the period by day
  WHERE  EXTRACT(ISODOW FROM d.day) <= 5                              -- keep weekdays
    AND  NOT EXISTS (
           SELECT 1 FROM holidays h
           WHERE  h.holiday_on = d.day::date)) b                      -- drop holidays
ORDER BY t.task_id;

/*
  Execution order (logical evaluation order):
  1. FROM tasks                   → Read 3 rows
  2. LATERAL generate_series      → Expand days per task
  3. WHERE ISODOW <= 5            → Remove weekends
  4. NOT EXISTS holidays          → Remove holidays
  5. COUNT(*)                     → Count the remaining days
  6. ORDER BY t.task_id           → Ascending by task id
  */

Explanation (table transitions & key points) ▼

SELECT
  t.task_id,
  t.start_on,
  t.due_on,
  b.business_days
FROM   tasks t
CROSS JOIN LATERAL (
  SELECT COUNT(*) AS business_days
  FROM   generate_series(t.start_on, t.due_on, INTERVAL '1 day') AS d(day)
  WHERE  EXTRACT(ISODOW FROM d.day) <= 5
    AND  NOT EXISTS (
           SELECT 1 FROM holidays h
           WHERE  h.holiday_on = d.day::date)) b
ORDER BY t.task_id;

LEGEND ▼

Rows read / loaded

① FROM tasks ▼

FROM tasks tRead all 3 rows of tasks. The longest period is seven days, and all of them fall across the early-May holidays.

1 / 6

task_id	start_on	due_on
1	2026-05-01	2026-05-07
2	2026-05-04	2026-05-08
3	2026-05-08	2026-05-08

All 3 rows read

LEARNING POINTS

Working days are counted, not subtracted: deducting weekends from calendar days needs a case for every possible starting weekday. Expanding day by day and filtering keeps new rules to one more line in WHERE.

LATERAL makes the per-row expansion possible: generate_series(t.start_on, t.due_on, …) reads values from the left-hand row, and that reference is only legal under LATERAL.

Holidays live in data: weekdays follow from the date, but public holidays are decided year by year. They cannot be expressed as a formula, so keep them in a table. NOT EXISTS is the safe standard form for "not listed".

ANTI-PATTERNS

Approximating with 5/7 of the calendar days: prorating by weeks slips by a day around holidays and at the edges of a period. In contracts and delivery dates, one day can change whether a penalty applies.

Listing holidays as literals in NOT IN: holidays written into a query are guaranteed to go stale next year, and the same list spreads across several queries. Have everyone read one holiday table.

Field Notes

Working days differ by organisation: on the same date the head office may be closed while the plant runs and an overseas site works normally. Give the holiday table a calendar identifier, as in (calendar_id, holiday_on), so sites and departments can be switched without rewriting queries when a new site appears. If you often need "N working days from here", the standard answer is a calendar table that numbers working days sequentially, so the question becomes a difference and an addition on that number.

QUESTION 8

Cohort analysis — Line up retention by months since the first order

MIN + DATE_TRUNCMonths elapsedCohortEXTRACT

Background ▼

The distance between two months is the difference in years times twelve plus the difference in months. Subtracting dates gives days, which do not line up because months have different lengths.

SELECT (EXTRACT(YEAR FROM date_col) - EXTRACT(YEAR FROM base_col)) * 12
     + (EXTRACT(MONTH FROM date_col) - EXTRACT(MONTH FROM base_col)) AS month_no
FROM table_name;
-- 2025-04 and 2025-06 → 2; 2025-12 and 2026-02 → 2

The baseline differs per row: in cohort analysis the baseline month is each customer's own first month. Because that comes from an aggregate but is needed per row, compute it in a CTE and join it back.

Problem ▼

Using the orders table, treat each customer's first order month as their cohort and count, for every number of months since the cohort month, how many customers placed an order. The first month is 0. Return cohort_month, month_no, customers, sorted by cohort_month then month_no ascending. Express cohort_month as the first day of the month.

Tables used

▸ orders

order_id	customer_id	ordered_on
1	101	2026-01-10
2	101	2026-02-05
3	101	2026-03-20
4	102	2026-01-25
5	102	2026-03-02
6	103	2026-02-14
7	103	2026-03-01

Expected Output ▼

cohort_month	month_no	customers
2026-01-01	0	2
2026-01-01	1	1
2026-01-01	2	2
2026-02-01	0	1
2026-02-01	1	1

Model Answer ▼

WITH first_month AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(ordered_on))::date AS cohort_month   -- first month per customer
  FROM     orders
  GROUP BY customer_id
)
SELECT
  f.cohort_month,
  (EXTRACT(YEAR FROM o.ordered_on) - EXTRACT(YEAR FROM f.cohort_month)) * 12
  + (EXTRACT(MONTH FROM o.ordered_on) - EXTRACT(MONTH FROM f.cohort_month)) AS month_no,  -- months elapsed
  COUNT(DISTINCT o.customer_id) AS customers   -- several orders in a month still count once
FROM     orders o
JOIN     first_month f ON f.customer_id = o.customer_id
GROUP BY f.cohort_month, 2
ORDER BY f.cohort_month, month_no;

/*
  Execution order (logical evaluation order):
  1. first_month: MIN(ordered_on) → Fix each customer's first month
  2. FROM orders JOIN first_month → Attach the cohort to every order
  3. SELECT months elapsed        → Year difference × 12 + month difference
  4. GROUP BY cohort_month, month_no → COUNT(DISTINCT customer_id)
  5. ORDER BY cohort_month, month_no → Ascending by cohort and offset
  */

Explanation (table transitions & key points) ▼

WITH first_month AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(ordered_on))::date AS cohort_month
  FROM     orders
  GROUP BY customer_id
)
SELECT f.cohort_month,
       (EXTRACT(YEAR FROM o.ordered_on) - EXTRACT(YEAR FROM f.cohort_month)) * 12
       + (EXTRACT(MONTH FROM o.ordered_on) - EXTRACT(MONTH FROM f.cohort_month)) AS month_no,
       COUNT(DISTINCT o.customer_id) AS customers
FROM     orders o
JOIN     first_month f ON f.customer_id = o.customer_id
GROUP BY f.cohort_month, 2
ORDER BY f.cohort_month, month_no;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM ordersRead all 7 rows of orders. Three customers appear, each with a different first month.

1 / 6

order_id	customer_id	ordered_on
1	101	2026-01-10
2	101	2026-02-05
3	101	2026-03-20
4	102	2026-01-25
5	102	2026-03-02
6	103	2026-02-14
7	103	2026-03-01

All 7 rows read

LEARNING POINTS

Month distance is years × 12 plus months: subtracting dates gives days, which do not align because months differ in length. Extracting year and month yields the calendar month count. You can also write it with AGE as EXTRACT(YEAR ...)*12 + EXTRACT(MONTH ...).

Build the baseline, then bring it back: the first month per customer only exists as an aggregate, yet the offset needs it per row. Computing it in a CTE and joining it back is the basic shape of cohort analysis.

Count people, not orders: COUNT(*) counts orders, COUNT(DISTINCT customer_id) counts returning customers. Retention needs the latter; confusing them makes months with heavy users spike.

ANTI-PATTERNS

Deriving the offset from days: (ordered_on - cohort_month) / 30 drifts with the varying length of months and can push an end-of-month order into the previous bucket. Count months in months.

Leaving empty months as missing rows: a month offset with no orders produces no row here. To lay the result out as a table, generate the offsets with generate_series and LEFT JOIN zeros into the gaps.

Field Notes

What "retained" means: this question defines it as "placed an order that month", but a subscription business would say "had an active contract", and an app would say "opened the app". Different definitions produce different retention curves from the same data, so always publish the definition alongside the report. Recent cohorts also have a short observation window, which leaves the higher offsets blank. Hide the unobserved cells rather than letting a reader interpret them as zero.

QUESTION 9

Longest streak — Subtract a row number to build islands of consecutive days

ROW_NUMBERIsland groupingConsecutive daysDISTINCT ON

Background ▼

Subtracting a sequential number from consecutive dates gives the same value throughout a run. When a date is skipped the difference changes, so the value itself identifies an "island" of consecutive days.

SELECT date_col - (ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY date_col))::int AS grp
FROM   table_name;
-- 07-11, 07-12, 07-13 → same grp / 07-16 → a different grp

Subtracting an integer from a date: a DATE minus an integer moves back that many days. ROW_NUMBER() returns bigint, so cast it with ::int before subtracting.

Problem ▼

From the logins table, find each user's longest run of consecutive login days together with the first and last day of that run. If several runs tie for the longest, take the one that starts earliest. Return user_id, started_on, ended_on, streak_days, sorted by user_id ascending.

Tables used

▸ logins

user_id	login_on
101	2026-02-01
101	2026-02-02
101	2026-02-03
101	2026-02-06
101	2026-02-07
102	2026-02-01
102	2026-02-03
102	2026-02-04
103	2026-02-05

Expected Output ▼

user_id	started_on	ended_on	streak_days
101	2026-02-01	2026-02-03	3
102	2026-02-03	2026-02-04	2
103	2026-02-05	2026-02-05	1

Model Answer ▼

WITH islands AS (
  SELECT
    user_id, login_on,
    login_on - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_on))::int AS grp  -- equal while consecutive
  FROM logins
), runs AS (
  SELECT
    user_id,
    MIN(login_on) AS started_on,
    MAX(login_on) AS ended_on,
    COUNT(*)      AS streak_days
  FROM     islands
  GROUP BY user_id, grp   -- one row per island
)
SELECT DISTINCT ON (user_id)
  user_id, started_on, ended_on, streak_days
FROM     runs
ORDER BY user_id, streak_days DESC, started_on;   -- first row per user = longest

/*
  Execution order (logical evaluation order):
  1. FROM logins                  → Read 9 rows
  2. ROW_NUMBER per user          → Subtract from login_on for an island key
  3. GROUP BY user_id, grp        → Start, end and length per island
  4. ORDER BY user_id, streak_days DESC, started_on → Longest first
  5. DISTINCT ON (user_id)        → Keep only the first row per user
  */

Explanation (table transitions & key points) ▼

WITH islands AS (
  SELECT user_id, login_on,
         login_on - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_on))::int AS grp
  FROM logins
), runs AS (
  SELECT user_id,
         MIN(login_on) AS started_on,
         MAX(login_on) AS ended_on,
         COUNT(*)      AS streak_days
  FROM     islands
  GROUP BY user_id, grp
)
SELECT DISTINCT ON (user_id)
  user_id, started_on, ended_on, streak_days
FROM     runs
ORDER BY user_id, streak_days DESC, started_on;

LEGEND ▼

Rows read / loaded

① FROM logins ▼

FROM loginsRead all 9 rows of logins. User 101 has five days recorded, user 102 three and user 103 one.

1 / 5

user_id	login_on
101	2026-02-01
101	2026-02-02
101	2026-02-03
101	2026-02-06
101	2026-02-07
102	2026-02-01
102	2026-02-03
102	2026-02-04
103	2026-02-05

All 9 rows read

LEARNING POINTS

Subtracting the row number flattens a run: dates and row numbers both advance by one, so the difference is constant inside a run and jumps at a gap. This is the classic gaps-and-islands technique.

DISTINCT ON comes with ORDER BY: PostgreSQL's DISTINCT ON (column) keeps the first row per value of that column according to ORDER BY. The order must start with the DISTINCT ON column, and the following columns decide which row survives.

Spell out the tie-break: streak_days DESC alone leaves it undefined which of two equally long islands is returned. Adding started_on makes the result unique.

ANTI-PATTERNS

Leaving duplicate days in place: two logins on the same day shift the row numbers and break the islands. This technique assumes the input is unique per user and date, so collapse it with SELECT DISTINCT user_id, login_on first when needed.

Walking the dates in application code: fetching every login and counting runs in a loop grows the transfer volume with the number of users. Building islands is what SQL is good at.

Field Notes

A streak means something different once "today" is involved: the longest streak is a historical record, but the "current streak" shown in games and apps refers only to the island that still reaches today or yesterday. From the same island calculation, selecting islands with ended_on >= reference date - 1 gives the run in progress. If you display it, define which time zone decides the reference date as well. An ambiguous day boundary is the usual source of complaints that a streak "broke" while the user was still playing.

QUESTION 10

Hourly buckets — Prorate stays that cross the hour boundary

generate_seriesGREATEST / LEASTHourly rollupEPOCH

Background ▼

To assign intervals that cross hour boundaries, build a spine of buckets and measure how much each interval overlaps each bucket. GREATEST and LEAST give the overlap, and EXTRACT(EPOCH FROM ...) turns it into seconds.

SELECT EXTRACT(EPOCH FROM (
         LEAST(end_col, b.bucket + INTERVAL '1 hour')
       - GREATEST(start_col, b.bucket))) / 60 AS minutes
FROM table_name, generate_series(...) AS b(bucket);

EPOCH is in seconds: EXTRACT(EPOCH FROM INTERVAL '1 hour') is 3600. Divide by 60 for minutes and by 3600 for hours.

Problem ▼

Prorate the stays in the sessions table into one-hour buckets for 09:00, 10:00 and 11:00 and sum them. Each bucket is the half-open interval [start, start + 1 hour), and sessions that do not overlap a bucket are not counted in it. Return bucket, minutes (total minutes as an integer), sorted by bucket ascending.

Tables used

▸ sessions

session_id	started_at	ended_at
1	2026-10-01 09:10:00	2026-10-01 09:50:00
2	2026-10-01 09:40:00	2026-10-01 11:10:00
3	2026-10-01 11:30:00	2026-10-01 11:45:00

Expected Output ▼

bucket	minutes
2026-10-01 09:00:00	60
2026-10-01 10:00:00	60
2026-10-01 11:00:00	25

Model Answer ▼

SELECT
  b.bucket::timestamp AS bucket,
  SUM(EXTRACT(EPOCH FROM (
        LEAST(s.ended_at, b.bucket + INTERVAL '1 hour')
      - GREATEST(s.started_at, b.bucket))) / 60)::int AS minutes   -- overlap in minutes
FROM   generate_series(
         TIMESTAMP '2026-10-01 09:00:00',
         TIMESTAMP '2026-10-01 11:00:00',
         INTERVAL '1 hour') AS b(bucket)          -- spine of buckets (3 rows)
JOIN   sessions s
  ON   s.started_at < b.bucket + INTERVAL '1 hour'          -- only intervals that overlap
 AND   s.ended_at   > b.bucket
GROUP BY b.bucket
ORDER BY bucket;

/*
  Execution order (logical evaluation order):
  1. generate_series(...)         → Generate 3 buckets
  2. JOIN sessions                → Match sessions overlapping a bucket
  3. SELECT LEAST / GREATEST      → Fix both ends of the overlap
  4. EXTRACT(EPOCH …) / 60        → Convert the overlap to minutes
  5. GROUP BY b.bucket            → Sum per bucket
  6. ORDER BY bucket              → Ascending by bucket
  */

Explanation (table transitions & key points) ▼

SELECT
  b.bucket::timestamp AS bucket,
  SUM(EXTRACT(EPOCH FROM (
        LEAST(s.ended_at, b.bucket + INTERVAL '1 hour')
      - GREATEST(s.started_at, b.bucket))) / 60)::int AS minutes
FROM   generate_series(
         TIMESTAMP '2026-10-01 09:00:00',
         TIMESTAMP '2026-10-01 11:00:00',
         INTERVAL '1 hour') AS b(bucket)
JOIN   sessions s
  ON   s.started_at < b.bucket + INTERVAL '1 hour'
 AND   s.ended_at   > b.bucket
GROUP BY b.bucket
ORDER BY bucket;

LEGEND ▼

Rows read / loaded

① generate_series builds the buckets ▼

generate_series(TIMESTAMP '2026-10-01 09:00:00', TIMESTAMP '2026-10-01 11:00:00', INTERVAL '1 hour')Create three rows for 09:00, 10:00 and 11:00. Regardless of how many sessions exist, this spine caps the number of result rows.

1 / 6

b.bucket	Bucket range
2026-10-01 09:00:00	09:00 – 10:00
2026-10-01 10:00:00	10:00 – 11:00
2026-10-01 11:00:00	11:00 – 12:00

3 rows generated

LEARNING POINTS

Prorating is measuring an overlap: assigning to buckets is nothing more than measuring the intersection of an interval with a bucket. Bucketing by start time alone would charge a 90-minute session entirely to the 9 o'clock hour and leave the 10 o'clock hour empty.

Build the buckets first: generating them with generate_series keeps the structure identical when the grain changes to 15 minutes or a day. Rounding the session side with DATE_TRUNC('hour', ...) cannot express crossings at all.

The JOIN condition is the overlap test: started_at < bucket end AND ended_at > bucket start is the pair of inequalities for overlapping intervals. Only pairs satisfying it have any overlap to measure.

ANTI-PATTERNS

Charging everything to the starting hour: it aggregates faster, but the error grows with the length of the session. For utilisation or congestion metrics it can point to the opposite conclusion.

Putting the bucket spine on the right of the JOIN: starting from sessions removes any hour with no overlapping session. To show empty hours as 0, keep the spine on the left and use LEFT JOIN.

Field Notes

Total time and concurrency are different metrics: minutes per hour answer "how much was it used", while capacity planning is driven by the peak number of simultaneous users. For concurrency, build an event stream of +1 at each start and -1 at each end, accumulate in time order and take the maximum. The same raw data feeds proration into the denominator of capacity planning and the concurrency peak into the sizing decision. Confirm which number is being asked for before writing the query, and you avoid building it twice.