In this post we are going to cover dates, gaps and islands: days between dates, missing dates, login streaks, sessions and consecutive months. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where leadership asks how many January customers came back in February.
Date questions look simple, but most of them hide one of a few patterns: generate the dates you expect and LEFT JOIN, subtract a ROW_NUMBER from a date to find streaks, or use LAG with a running SUM to build sessions. Interviewers ask them because they show if you can turn a business question into those patterns, and if you notice the edge cases.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 8 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: For each customer, the business wants the first order date, the last order date, the days between them and the days since the last order. How do you write it?
- Question 2: A user is expected to log in every day, but some days are missing. How do you find the missing dates in a sequence?
- Question 3: Product wants each user's runs of consecutive login days. How do you solve this gaps and islands problem?
- Question 4: Gamification wants each user's longest login streak. How do you find it, and how do you handle two streaks of the same length?
- Question 5: Web analytics wants clicks grouped into sessions, where a new session starts after 30 minutes without activity. How do you sessionize events in SQL?
- Question 6: Marketing wants the customers who ordered in consecutive months, for example three months in a row. How do you find them?
- Scenario 7: Leadership asks: how many January customers came back in February? How do you calculate month-over-month retention, and what must you agree on first?
- The full SQL script
The short answer
Subtract dates (or DATEDIFF) for day differences, and generate a calendar and LEFT JOIN to find missing dates. For streaks, date minus ROW_NUMBER() is constant inside a run of consecutive days, so it identifies each island. LAG plus a running SUM of new-session flags builds sessions, and a month number (year × 12 + month) turns the same tricks into consecutive months and retention.
Every question below is one of these tricks.
Let's create some sample data
We use the TB Shop database: departments, employees, customers, products, orders, order items and payments, plus two extra tables for this part: logins (one row per login) and events (one row per click). The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with small changes.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS logins;
DROP TABLE IF EXISTS events;
-- Q2-Q4: app logins, June 1-10 2026 (Sara logs in twice on June 2)
CREATE TABLE logins (username VARCHAR(10), login_time TIMESTAMP);
INSERT INTO logins VALUES
('sara', '2026-06-01 08:10:00'), ('sara', '2026-06-02 08:05:00'), ('sara', '2026-06-02 19:40:00'),
('sara', '2026-06-03 09:00:00'), ('sara', '2026-06-05 08:30:00'), ('sara', '2026-06-06 08:15:00'),
('sara', '2026-06-07 21:00:00'), ('sara', '2026-06-08 07:55:00'), ('sara', '2026-06-10 08:20:00'),
('omar', '2026-06-01 12:00:00'), ('omar', '2026-06-03 12:30:00'), ('omar', '2026-06-04 13:10:00'),
('omar', '2026-06-05 12:45:00'), ('omar', '2026-06-06 18:20:00'), ('omar', '2026-06-07 12:05:00'),
('omar', '2026-06-09 12:15:00'),
('lina', '2026-06-04 20:30:00'), ('lina', '2026-06-05 22:10:00');
-- Q5: click events on one day (gaps of 55 and 45 minutes, and one gap of exactly 30)
CREATE TABLE events (username VARCHAR(10), event_time TIMESTAMP);
INSERT INTO events VALUES
('sara', '2026-06-05 09:00:00'), ('sara', '2026-06-05 09:10:00'), ('sara', '2026-06-05 09:25:00'),
('sara', '2026-06-05 10:20:00'), ('sara', '2026-06-05 10:35:00'), ('sara', '2026-06-05 11:05:00'),
('omar', '2026-06-05 09:05:00'), ('omar', '2026-06-05 09:50:00'), ('omar', '2026-06-05 10:00:00');Here are the orders. Notice that Sara Khan (customer 1) has a Cancelled order on 2026-02-14, and Chen Wu (customer 6) has no orders at all. Both matter later.
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered | 1250.00 |
| 1002 | 2 | 2026-01-18 | Delivered | 300.00 |
| 1003 | 4 | 2026-02-02 | Delivered | 650.00 |
| 1004 | 1 | 2026-02-14 | Cancelled | 50.00 |
| 1005 | 3 | 2026-02-20 | Delivered | 1200.00 |
| 1006 | 4 | 2026-03-03 | Shipped | 275.00 |
| 1007 | 5 | 2026-03-15 | Delivered | 400.00 |
| 1008 | 2 | 2026-04-01 | Delivered | 75.00 |
| 1009 | 4 | 2026-04-22 | Delivered | 250.00 |
| 1010 | 1 | 2026-05-09 | Shipped | 300.00 |
| 1011 | 3 | 2026-05-30 | Cancelled | 400.00 |
| 1012 | 7 | 2026-06-11 | Delivered | 50.00 |
And here are the logins. Sara logged in twice on 2026-06-02, and she has no login on June 4 or June 9.
| username | login_time |
|---|---|
| lina | 2026-06-04 20:30 |
| lina | 2026-06-05 22:10 |
| omar | 2026-06-01 12:00 |
| omar | 2026-06-03 12:30 |
| omar | 2026-06-04 13:10 |
| omar | 2026-06-05 12:45 |
| omar | 2026-06-06 18:20 |
| omar | 2026-06-07 12:05 |
| omar | 2026-06-09 12:15 |
| sara | 2026-06-01 08:10 |
| sara | 2026-06-02 19:40 |
| sara | 2026-06-02 08:05 |
| sara | 2026-06-03 09:00 |
| sara | 2026-06-05 08:30 |
| sara | 2026-06-06 08:15 |
| sara | 2026-06-07 21:00 |
| sara | 2026-06-08 07:55 |
| sara | 2026-06-10 08:20 |
Question 1: For each customer, the business wants the first order date, the last order date, the days between them and the days since the last order. How do you write it?
MIN and MAX of the order date, grouped by customer. In PostgreSQL, subtracting one date from another gives the number of days directly.
Let's calculate all four columns in one query. For the days since the last order, we use a fixed report date, 2026-06-30.
SELECT c.name,
MIN(o.order_date) AS first_order,
MAX(o.order_date) AS last_order,
MAX(o.order_date) - MIN(o.order_date) AS days_between,
CAST('2026-06-30' AS DATE) - MAX(o.order_date)
AS days_since_last
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;| name | first_order | last_order | days_between | days_since_last |
|---|---|---|---|---|
| Sara Khan | 2026-01-05 | 2026-05-09 | 124 | 52 |
| Omar Ali | 2026-01-18 | 2026-04-01 | 73 | 90 |
| Lina Park | 2026-02-20 | 2026-05-30 | 99 | 31 |
| Mei Chen | 2026-02-02 | 2026-04-22 | 79 | 69 |
| Ravi Patel | 2026-03-15 | 2026-03-15 | 0 | 107 |
| Sara K. | 2026-06-11 | 2026-06-11 | 0 | 19 |
Sara Khan ordered first on 2026-01-05 and last on 2026-05-09, so her days between is 124, and her last order was 52 days before the report date. Ravi Patel and Sara K. have a single order each, so their days between is 0.
Why a fixed date and not CURRENT_DATE? In production you would use CURRENT_DATE, but a fixed date keeps the result reproducible, so your test gives the same answer tomorrow.
In SQL Server, use DATEDIFF(day, MIN(order_date), MAX(order_date)). In MySQL, DATEDIFF(last, first).
Chen Wu is not in the list. He never ordered, and the inner join drops him. If the business wants every customer, use a LEFT JOIN, and his dates come back NULL.
Question 2: A user is expected to log in every day, but some days are missing. How do you find the missing dates in a sequence?
You cannot select rows that do not exist. So first we generate the dates we expect, then we LEFT JOIN the real data to them and keep the dates where nothing matched.
Here a recursive CTE builds a calendar from 2026-06-01 to 2026-06-10, and we ask which days Sara did not log in.
WITH RECURSIVE calendar (d) AS (
SELECT CAST('2026-06-01' AS DATE)
UNION ALL
SELECT CAST(d + INTERVAL '1' DAY AS DATE)
FROM calendar
WHERE d < CAST('2026-06-10' AS DATE)
)
SELECT c.d AS missing_date
FROM calendar c
LEFT JOIN logins l
ON CAST(l.login_time AS DATE) = c.d
AND l.username = 'sara'
WHERE l.username IS NULL
ORDER BY c.d;| missing_date |
|---|
| 2026-06-04 |
| 2026-06-09 |
Two days come back: 2026-06-04 and 2026-06-09. Those are exactly the gaps we saw in the logins table.
Notice where the user filter sits: l.username = 'sara' is in the ON clause. If you move it to the WHERE clause, the LEFT JOIN turns into an inner join, and every missing day disappears from the result.
In production you would join to the date dimension instead of building a calendar. PostgreSQL also has generate_series, and in SQL Server you drop the word RECURSIVE and use DATEADD(day, 1, d). The same pattern finds the days when a daily file never arrived.
Question 3: Product wants each user's runs of consecutive login days. How do you solve this gaps and islands problem?
This is the famous gaps and islands problem. The trick: number each user's days with ROW_NUMBER and subtract that number from the date. Inside a run of consecutive days, the date and the row number both go up by 1, so the difference stays the same.
First we reduce the logins to one row per user per day with DISTINCT, because Sara's two logins on June 2 would break the numbering. Let's look at Sara only.
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
)
SELECT username, login_date,
ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS rn,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
WHERE username = 'sara'
ORDER BY login_date;| username | login_date | rn | grp |
|---|---|---|---|
| sara | 2026-06-01 | 1 | 2026-05-31 |
| sara | 2026-06-02 | 2 | 2026-05-31 |
| sara | 2026-06-03 | 3 | 2026-05-31 |
| sara | 2026-06-05 | 4 | 2026-06-01 |
| sara | 2026-06-06 | 5 | 2026-06-01 |
| sara | 2026-06-07 | 6 | 2026-06-01 |
| sara | 2026-06-08 | 7 | 2026-06-01 |
| sara | 2026-06-10 | 8 | 2026-06-02 |
June 1, 2 and 3 all give a grp of 2026-05-31. June 5 to June 8 all give 2026-06-01. June 10 gives 2026-06-02. That constant value is the island's ID.
Now group by user and that value, and every group is one streak with its start, its end and its length.
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
), g AS (
SELECT username, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT username, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g
GROUP BY username, grp
ORDER BY username, streak_start;| username | streak_start | streak_end | days |
|---|---|---|---|
| lina | 2026-06-04 | 2026-06-05 | 2 |
| omar | 2026-06-01 | 2026-06-01 | 1 |
| omar | 2026-06-03 | 2026-06-07 | 5 |
| omar | 2026-06-09 | 2026-06-09 | 1 |
| sara | 2026-06-01 | 2026-06-03 | 3 |
| sara | 2026-06-05 | 2026-06-08 | 4 |
| sara | 2026-06-10 | 2026-06-10 | 1 |
Sara has three streaks: 3 days, 4 days and 1 day. Omar has three as well: 1, 5 and 1 days. Lina has one streak of 2 days.
In PostgreSQL, CAST the row number to INT before subtracting it from a date. In SQL Server, use DATEADD(day, -ROW_NUMBER() OVER (...), login_date). If you skip the DISTINCT, use DENSE_RANK and COUNT(DISTINCT login_date) instead.
Question 4: Gamification wants each user's longest login streak. How do you find it, and how do you handle two streaks of the same length?
We already have the islands from question 3. Put them in a CTE, rank each user's streaks by length with RANK, longest first, and keep rank 1. Chained CTEs keep every step readable and easy to test on its own.
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
), g AS (
SELECT username, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
), s AS (
SELECT username, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g
GROUP BY username, grp
), r AS (
SELECT username, streak_start, streak_end, days,
RANK() OVER (PARTITION BY username ORDER BY days DESC) AS rnk
FROM s
)
SELECT username, streak_start, streak_end, days
FROM r
WHERE rnk = 1
ORDER BY days DESC, username;| username | streak_start | streak_end | days |
|---|---|---|---|
| omar | 2026-06-03 | 2026-06-07 | 5 |
| sara | 2026-06-05 | 2026-06-08 | 4 |
| lina | 2026-06-04 | 2026-06-05 | 2 |
Omar's longest streak is 5 days, from 2026-06-03 to 2026-06-07. Sara's is 4 days, from 2026-06-05 to 2026-06-08, and Lina's is 2 days.
Why RANK and not ROW_NUMBER? If a user has two streaks of the same length, RANK keeps both, and the business decides which one to show. ROW_NUMBER would silently pick one of them for you.
A common follow-up is the current streak. Use the same islands, but keep only the island whose streak_end is today, or yesterday if the user has not logged in yet today.
Question 5: Web analytics wants clicks grouped into sessions, where a new session starts after 30 minutes without activity. How do you sessionize events in SQL?
This is how web analytics defines a session, and it takes three steps: LAG for the previous event, a flag for a new session, and a running SUM of the flags. Here are the events we start from.
| username | event_time |
|---|---|
| omar | 2026-06-05 09:05 |
| omar | 2026-06-05 09:50 |
| omar | 2026-06-05 10:00 |
| sara | 2026-06-05 10:20 |
| sara | 2026-06-05 09:00 |
| sara | 2026-06-05 11:05 |
| sara | 2026-06-05 10:35 |
| sara | 2026-06-05 09:10 |
| sara | 2026-06-05 09:25 |
- Step one: LAG gives every event the previous event time of the same user, and we compute the gap in minutes.
- Step two: flag a new session with 1 when there is no previous event or the gap is more than 30 minutes, otherwise 0.
- Step three: a running SUM of the flags is the session number.
WITH g AS (
SELECT username, event_time,
LAG(event_time) OVER (PARTITION BY username
ORDER BY event_time) AS prev_time
FROM events
), s AS (
SELECT username, event_time,
CAST(EXTRACT(EPOCH FROM (event_time - prev_time)) / 60
AS INT) AS gap_min,
CASE WHEN prev_time IS NULL
OR event_time > prev_time + INTERVAL '30' MINUTE
THEN 1 ELSE 0 END AS new_session
FROM g
)
SELECT username, CAST(event_time AS TIME) AS at_time,
gap_min, new_session,
SUM(new_session) OVER (PARTITION BY username
ORDER BY event_time
ROWS UNBOUNDED PRECEDING) AS session_no
FROM s
ORDER BY username, event_time;| username | at_time | gap_min | new_session | session_no |
|---|---|---|---|---|
| omar | 09:05:00 | NULL | 1 | 1 |
| omar | 09:50:00 | 45 | 1 | 2 |
| omar | 10:00:00 | 10 | 0 | 2 |
| sara | 09:00:00 | NULL | 1 | 1 |
| sara | 09:10:00 | 10 | 0 | 1 |
| sara | 09:25:00 | 15 | 0 | 1 |
| sara | 10:20:00 | 55 | 1 | 2 |
| sara | 10:35:00 | 15 | 0 | 2 |
| sara | 11:05:00 | 30 | 0 | 2 |
Every 1 starts a new session and every 0 continues the current one. Omar's 45-minute gap at 09:50 starts his session 2. Sara's 55-minute gap at 10:20 starts her session 2.
Now look at the boundary. Sara's last event at 11:05 came exactly 30 minutes after the one before, so it stays in session 2. Greater than, or greater than or equal: that is a business rule, so ask, don't guess.
From here, group by user and session_no to get each session's start, end and number of events. In SQL Server, compute the gap with DATEDIFF(minute, prev_time, event_time), and the check with DATEADD(minute, 30, prev_time).
Question 6: Marketing wants the customers who ordered in consecutive months, for example three months in a row. How do you find them?
It is the same islands trick, on months instead of days. First we turn each order date into a month number: year times 12, plus the month. That way December and the next January are consecutive numbers too.
Then we take the distinct customer months, subtract a ROW_NUMBER, and group by the result. Each group is a run of consecutive months, and HAVING COUNT(*) >= 2 keeps the runs of two months or more.
WITH m AS (
SELECT DISTINCT customer_id,
EXTRACT(YEAR FROM order_date) * 12
+ EXTRACT(MONTH FROM order_date) AS month_no,
EXTRACT(MONTH FROM order_date) AS mon
FROM orders
), g AS (
SELECT customer_id, month_no, mon,
month_no - ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY month_no) AS grp
FROM m
)
SELECT c.name, MIN(g.mon) AS from_month, MAX(g.mon) AS to_month,
COUNT(*) AS months_in_row
FROM g
JOIN customers c ON c.customer_id = g.customer_id
GROUP BY c.name, g.customer_id, g.grp
HAVING COUNT(*) >= 2
ORDER BY months_in_row DESC, c.name;| name | from_month | to_month | months_in_row |
|---|---|---|---|
| Mei Chen | 2 | 4 | 3 |
| Sara Khan | 1 | 2 | 2 |
Mei Chen ordered three months in a row, from month 2 to month 4. Sara Khan has a two-month run, months 1 and 2.
For three in a row, change the HAVING to COUNT(*) >= 3, and only Mei is left. Another way is LAG with an offset of 2: if the month two rows back is exactly 2 less, that is three months in a row.
Keep Sara's run in mind. Her February order is the Cancelled one, and that becomes important in the next scenario.
Scenario 7: Leadership asks: how many January customers came back in February? How do you calculate month-over-month retention, and what must you agree on first?
Before writing any SQL, pin down the definition. Here, a January customer came back if they placed a February order that was not cancelled.
Then LEFT JOIN January's customers to their February orders. We use half-open date ranges: on or after the first day of the month, and before the first day of the next month. That is correct for timestamps too, and it can use an index on order_date.
SELECT COUNT(DISTINCT j.customer_id) AS jan_customers,
COUNT(DISTINCT f.customer_id) AS any_feb_order,
COUNT(DISTINCT CASE WHEN f.status <> 'Cancelled'
THEN f.customer_id END) AS came_back_feb
FROM orders j
LEFT JOIN orders f
ON f.customer_id = j.customer_id
AND f.order_date >= '2026-02-01'
AND f.order_date < '2026-03-01'
WHERE j.order_date >= '2026-01-01'
AND j.order_date < '2026-02-01'
AND j.status <> 'Cancelled';| jan_customers | any_feb_order | came_back_feb |
|---|---|---|
| 2 | 1 | 0 |
There are 2 January customers. 1 of them has a February order: that is Sara, but her February order was cancelled. So came_back_feb is 0. The answer is zero percent, not fifty.
Then the follow-up: retention for every month. We turn dates into month numbers, drop cancelled orders first, and LEFT JOIN each customer month to the same customer in the next month.
WITH m AS (
SELECT DISTINCT customer_id,
EXTRACT(YEAR FROM order_date) * 12
+ EXTRACT(MONTH FROM order_date) AS month_no,
EXTRACT(MONTH FROM order_date) AS mon
FROM orders
WHERE status <> 'Cancelled'
)
SELECT cur.mon AS order_month,
COUNT(*) AS customers,
COUNT(nxt.customer_id) AS came_back_next,
CAST(100.0 * COUNT(nxt.customer_id) / COUNT(*)
AS DECIMAL(5,2)) AS retention_pct
FROM m cur
LEFT JOIN m nxt
ON nxt.customer_id = cur.customer_id
AND nxt.month_no = cur.month_no + 1
GROUP BY cur.month_no, cur.mon
ORDER BY cur.month_no;| order_month | customers | came_back_next | retention_pct |
|---|---|---|---|
| 1 | 2 | 0 | 0.00 |
| 2 | 2 | 1 | 50.00 |
| 3 | 2 | 1 | 50.00 |
| 4 | 2 | 0 | 0.00 |
| 5 | 1 | 0 | 0.00 |
| 6 | 1 | 0 | 0.00 |
January is 0.00 percent, February and March are 50.00, and April and May are 0.00. Now look at June: that 0.00 is not churn. July has no data yet, so June is an incomplete period. Exclude it, or label it on the report.
Agree the definition before you present, and print it on the report. Count every order, cancelled ones included, and Sara looks retained. The SQL is the easy part.
Key points to remember
- Date minus date (or DATEDIFF) for days between
- Generate a calendar and LEFT JOIN to find missing dates
- Date minus ROW_NUMBER() labels each island (streak)
- LAG + a running SUM of flags builds sessions
- Month number = year × 12 + month for consecutive months and retention
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #8 - Dates, gaps and islands
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q08; SET search_path TO sql_q08;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS logins;
DROP TABLE IF EXISTS events;
-- Q2-Q4: app logins, June 1-10 2026 (Sara logs in twice on June 2)
CREATE TABLE logins (username VARCHAR(10), login_time TIMESTAMP);
INSERT INTO logins VALUES
('sara', '2026-06-01 08:10:00'), ('sara', '2026-06-02 08:05:00'), ('sara', '2026-06-02 19:40:00'),
('sara', '2026-06-03 09:00:00'), ('sara', '2026-06-05 08:30:00'), ('sara', '2026-06-06 08:15:00'),
('sara', '2026-06-07 21:00:00'), ('sara', '2026-06-08 07:55:00'), ('sara', '2026-06-10 08:20:00'),
('omar', '2026-06-01 12:00:00'), ('omar', '2026-06-03 12:30:00'), ('omar', '2026-06-04 13:10:00'),
('omar', '2026-06-05 12:45:00'), ('omar', '2026-06-06 18:20:00'), ('omar', '2026-06-07 12:05:00'),
('omar', '2026-06-09 12:15:00'),
('lina', '2026-06-04 20:30:00'), ('lina', '2026-06-05 22:10:00');
-- Q5: click events on one day (gaps of 55 and 45 minutes, and one gap of exactly 30)
CREATE TABLE events (username VARCHAR(10), event_time TIMESTAMP);
INSERT INTO events VALUES
('sara', '2026-06-05 09:00:00'), ('sara', '2026-06-05 09:10:00'), ('sara', '2026-06-05 09:25:00'),
('sara', '2026-06-05 10:20:00'), ('sara', '2026-06-05 10:35:00'), ('sara', '2026-06-05 11:05:00'),
('omar', '2026-06-05 09:05:00'), ('omar', '2026-06-05 09:50:00'), ('omar', '2026-06-05 10:00:00');
-- Step 1: First order, last order, days between, and days since the last order
SELECT c.name,
MIN(o.order_date) AS first_order,
MAX(o.order_date) AS last_order,
MAX(o.order_date) - MIN(o.order_date) AS days_between,
CAST('2026-06-30' AS DATE) - MAX(o.order_date)
AS days_since_last
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
-- Step 2: Missing dates: generate a calendar, LEFT JOIN the data, keep what did not match
WITH RECURSIVE calendar (d) AS (
SELECT CAST('2026-06-01' AS DATE)
UNION ALL
SELECT CAST(d + INTERVAL '1' DAY AS DATE)
FROM calendar
WHERE d < CAST('2026-06-10' AS DATE)
)
SELECT c.d AS missing_date
FROM calendar c
LEFT JOIN logins l
ON CAST(l.login_time AS DATE) = c.d
AND l.username = 'sara'
WHERE l.username IS NULL
ORDER BY c.d;
-- Step 3: Gaps and islands: date minus ROW_NUMBER is constant inside a streak
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
)
SELECT username, login_date,
ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS rn,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
WHERE username = 'sara'
ORDER BY login_date;
-- Step 4: Each island is one streak: GROUP BY user and grp
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
), g AS (
SELECT username, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT username, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g
GROUP BY username, grp
ORDER BY username, streak_start;
-- Step 5: Longest streak per user: rank the islands by length
WITH d AS (
SELECT DISTINCT username,
CAST(login_time AS DATE) AS login_date
FROM logins
), g AS (
SELECT username, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY username
ORDER BY login_date) AS INT) AS grp
FROM d
), s AS (
SELECT username, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g
GROUP BY username, grp
), r AS (
SELECT username, streak_start, streak_end, days,
RANK() OVER (PARTITION BY username ORDER BY days DESC) AS rnk
FROM s
)
SELECT username, streak_start, streak_end, days
FROM r
WHERE rnk = 1
ORDER BY days DESC, username;
-- Step 6: Sessions: LAG, a new-session flag, and a running SUM of the flags
WITH g AS (
SELECT username, event_time,
LAG(event_time) OVER (PARTITION BY username
ORDER BY event_time) AS prev_time
FROM events
), s AS (
SELECT username, event_time,
CAST(EXTRACT(EPOCH FROM (event_time - prev_time)) / 60
AS INT) AS gap_min,
CASE WHEN prev_time IS NULL
OR event_time > prev_time + INTERVAL '30' MINUTE
THEN 1 ELSE 0 END AS new_session
FROM g
)
SELECT username, CAST(event_time AS TIME) AS at_time,
gap_min, new_session,
SUM(new_session) OVER (PARTITION BY username
ORDER BY event_time
ROWS UNBOUNDED PRECEDING) AS session_no
FROM s
ORDER BY username, event_time;
-- Step 7: Consecutive months: the same islands trick on a month number
WITH m AS (
SELECT DISTINCT customer_id,
EXTRACT(YEAR FROM order_date) * 12
+ EXTRACT(MONTH FROM order_date) AS month_no,
EXTRACT(MONTH FROM order_date) AS mon
FROM orders
), g AS (
SELECT customer_id, month_no, mon,
month_no - ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY month_no) AS grp
FROM m
)
SELECT c.name, MIN(g.mon) AS from_month, MAX(g.mon) AS to_month,
COUNT(*) AS months_in_row
FROM g
JOIN customers c ON c.customer_id = g.customer_id
GROUP BY c.name, g.customer_id, g.grp
HAVING COUNT(*) >= 2
ORDER BY months_in_row DESC, c.name;
-- Step 8: Scenario: how many January customers came back in February (cancelled orders do not count)?
SELECT COUNT(DISTINCT j.customer_id) AS jan_customers,
COUNT(DISTINCT f.customer_id) AS any_feb_order,
COUNT(DISTINCT CASE WHEN f.status <> 'Cancelled'
THEN f.customer_id END) AS came_back_feb
FROM orders j
LEFT JOIN orders f
ON f.customer_id = j.customer_id
AND f.order_date >= '2026-02-01'
AND f.order_date < '2026-03-01'
WHERE j.order_date >= '2026-01-01'
AND j.order_date < '2026-02-01'
AND j.status <> 'Cancelled';
-- Step 9: Month-over-month retention for every month
WITH m AS (
SELECT DISTINCT customer_id,
EXTRACT(YEAR FROM order_date) * 12
+ EXTRACT(MONTH FROM order_date) AS month_no,
EXTRACT(MONTH FROM order_date) AS mon
FROM orders
WHERE status <> 'Cancelled'
)
SELECT cur.mon AS order_month,
COUNT(*) AS customers,
COUNT(nxt.customer_id) AS came_back_next,
CAST(100.0 * COUNT(nxt.customer_id) / COUNT(*)
AS DECIMAL(5,2)) AS retention_pct
FROM m cur
LEFT JOIN m nxt
ON nxt.customer_id = cur.customer_id
AND nxt.month_no = cur.month_no + 1
GROUP BY cur.month_no, cur.mon
ORDER BY cur.month_no;
Part 9: Performance and Indexes
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.