Definition
- Deduplication – reduce orders to distinct
(customer_id, day)pairs so multiple orders on the same day count as one day. - LAG window function –
LAG(d) OVER (PARTITION BY customer_id ORDER BY d)returns the previous order day for each row within the same customer. - Consecutive-day test – keep rows where the current day minus the previous day equals 1 (PostgreSQL:
d - prev_d = 1; MySQL:DATEDIFF(d, prev_d) = 1). - NULL handling – the first row per customer has a NULL previous day;
NULL = 1evaluates to NULL (not true), so it is naturally excluded. - Gaps-and-islands extension – for three-or-more consecutive days, subtract a dense row number from the date to form islands, then group and count.
National Bank of India wants to find loyal customers who placed an order two days in a row. Priya takes each customer's list of order days, removes repeats so each day appears once, sorts them, and walks down the list asking "is this day exactly one after the day above it?" If yes even once, that customer qualifies for the streak loyalty perk. The "day above it" is LAG, and "exactly one day after" is the date difference equalling one.
Understanding the Concepts
What is the problem?
Find customers who placed orders on at least two consecutive calendar days. A customer who ordered on January 1 and January 2 qualifies. A customer who ordered on January 1 and January 3 does not, because there is a gap on January 2.
Why do you need to deduplicate first?
A customer might place several orders on the same day. Without deduplication, the LAG function would compare the same day to itself and produce a difference of zero, not one. The deduplication ensures each day appears once per customer.
-- Stage 1: Deduplicate to distinct (customer_id, day)
WITH days AS (
SELECT DISTINCT customer_id, CAST(order_date AS DATE) AS d
FROM orders
)
customer_id | d
------------+-----------
C001 | 2024-01-01
C001 | 2024-01-02
C001 | 2024-01-05
C002 | 2024-01-01
C002 | 2024-01-03
How does LAG bring the previous day onto each row?
LAG(d) OVER (PARTITION BY customer_id ORDER BY d) returns, for each row, the day value from the row immediately before it within the same customer, in day order.
-- Stage 2: LAG to get previous order day
, with_prev AS (
SELECT customer_id, d,
LAG(d) OVER (PARTITION BY customer_id ORDER BY d) AS prev_d
FROM days
)
customer_id | d | prev_d ------------+------------+----------- C001 | 2024-01-01 | NULL ← first row, no predecessor C001 | 2024-01-02 | 2024-01-01 ← 1 day after prev C001 | 2024-01-05 | 2024-01-02 ← 3 days after prev (gap) C002 | 2024-01-01 | NULL C002 | 2024-01-03 | 2024-01-01 ← 2 days after prev (gap)
How do you filter for consecutive days?
Keep rows where the current day minus the previous day equals one. Then select distinct customers.
-- Stage 3: Filter for consecutive days
SELECT DISTINCT customer_id
FROM with_prev
WHERE d - prev_d = 1; -- PostgreSQL: date subtraction yields integer days
-- MySQL: WHERE DATEDIFF(d, prev_d) = 1
customer_id
-----------
C001 ← ordered on Jan 1 and Jan 2 (consecutive)
← C002 NOT returned (Jan 1 and Jan 3 have a gap)
What is the complete query?
-- PostgreSQL version
WITH days AS (
SELECT DISTINCT customer_id, CAST(order_date AS DATE) AS d
FROM orders
),
with_prev AS (
SELECT customer_id, d,
LAG(d) OVER (PARTITION BY customer_id ORDER BY d) AS prev_d
FROM days
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE d - prev_d = 1;
-- MySQL version
WITH days AS (
SELECT DISTINCT customer_id, CAST(order_date AS DATE) AS d
FROM orders
),
with_prev AS (
SELECT customer_id, d,
LAG(d) OVER (PARTITION BY customer_id ORDER BY d) AS prev_d
FROM days
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE DATEDIFF(d, prev_d) = 1;
Why LAG instead of a self-join?
You could self-join the days table on d = prev.d + 1, and that works, but LAG is cleaner, reads top to bottom, and is usually more efficient because it is a single ordered pass rather than a join.
-- Self-join alternative (works but less clean)
SELECT DISTINCT d1.customer_id
FROM days d1
JOIN days d2
ON d1.customer_id = d2.customer_id
AND d1.d = d2.d + INTERVAL '1 day';
How do you extend this to three or more consecutive days?
For three consecutive days, the classic gaps-and-islands trick subtracts a dense row number from the date to form islands. Consecutive dates produce the same island identifier.
WITH days AS (
SELECT DISTINCT customer_id, CAST(order_date AS DATE) AS d
FROM orders
),
islands AS (
SELECT customer_id, d,
d - (ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY d
))::int AS island_id
FROM days
)
SELECT customer_id, island_id,
MIN(d) AS streak_start,
MAX(d) AS streak_end,
COUNT(*) AS streak_length
FROM islands
GROUP BY customer_id, island_id
HAVING COUNT(*) >= 3;
customer_id | streak_start | streak_end | streak_length
------------+--------------+------------+--------------
C001 | 2024-01-01 | 2024-01-03 | 3
What about NULL handling?
The first row of each customer has a NULL previous day because nothing precedes it. NULL - prev or DATEDIFF with NULL yields NULL, and NULL = 1 evaluates to NOT TRUE (not just false, but unknown). The WHERE clause naturally excludes these rows. No special handling is needed.
What about time zones?
"A day" is defined in some time zone. For National Bank of India, a day means a day in IST (Asia/Kolkata). If order timestamps are stored in UTC, cast in the reporting zone before taking the date.
-- Convert UTC to IST before extracting the date
SELECT DISTINCT customer_id,
CAST(order_date AT TIME ZONE 'Asia/Kolkata' AS DATE) AS d
FROM orders;
How do you implement this in Java?
package com.bank.service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class LoyaltyStreakService {
private final Connection connection;
public LoyaltyStreakService(Connection connection) {
this.connection = connection;
}
public List<String> getConsecutiveDayCustomers() throws SQLException {
String sql = """
WITH days AS (
SELECT DISTINCT customer_id, CAST(order_date AS DATE) AS d
FROM orders
),
with_prev AS (
SELECT customer_id, d,
LAG(d) OVER (PARTITION BY customer_id ORDER BY d) AS prev_d
FROM days
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE d - prev_d = 1
""";
List<String> loyalCustomers = new ArrayList<>();
try (Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
loyalCustomers.add(rs.getString("customer_id"));
}
}
return loyalCustomers;
}
}
What about performance?
An index on (customer_id, order_date) supports the partitioned ordering. The DISTINCT in the CTE and the window function both benefit from this index.
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);
What are common interview follow-up questions?
- "How do you find the longest consecutive streak per customer?" Use the gaps-and-islands approach, group by island, and take the max count.
- "What if weekends should not count as gaps?" Filter to business days before comparing, or adjust the date arithmetic.
- "How do you find customers who ordered every day for a whole week?" Use islands with
HAVING COUNT(*) >= 7.
Visual Overview
Customer C001's order days (after dedup):
Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
┌────┐ ┌────┐ ┌────┐
│ d │ │ d │ │ d │
└────┘ └────┘ └────┘
│ │ │
│ LAG │ LAG │ LAG
▼ ▼ ▼
prev_d prev_d = Jan 1 prev_d = Jan 2
= NULL d - prev = 1 ✓ d - prev = 3 ✗
Result: C001 qualifies (at least one pair with diff = 1)
Gaps-and-Islands (for streaks of 3+):
d row_num d - row_num = island_id
Jan 1 1 Jan 1 - 1 = Dec 31 → island A
Jan 2 2 Jan 2 - 2 = Dec 31 → island A ← same island!
Jan 5 3 Jan 5 - 3 = Jan 2 → island B
Island A: 2 consecutive days (Jan 1, Jan 2)
Island B: 1 day (Jan 5)
Key Takeaway
To find customers who ordered on at least two consecutive days, the approach has three clean stages. First, reduce orders to distinct customer-and-day pairs, because several orders on the same day should count as one day. Second, within each customer ordered by day, bring the previous order day onto the current row with LAG(d) OVER (PARTITION BY customer_id ORDER BY d). Third, keep rows where today minus the previous day equals one, then select distinct customers.
In the National Bank of India streak loyalty perk, marketing wanted customers who transacted on consecutive days. Rajesh ordered on January 1 and 2, so he qualified, while Priya ordered on January 1 and 3, a gap, so she did not. Crucially, a naive count of orders per customer would have wrongly flagged Priya just for having multiple orders. The LAG approach compares adjacent days, which is exactly the right test.
The senior points are that the first row of each customer has a NULL previous day that the equals-one comparison naturally excludes, that LAG is preferred over a self-join because it is a single ordered pass and reads cleanly, that for three-or-more consecutive days you use the gaps-and-islands trick of subtracting a row number from the date to form islands, and that "a day" is time-zone dependent so you cast in the bank's reporting zone first. An index on (customer_id, order_date) supports the partitioned ordering.
Remember one sentence: distinct customer-day, LAG the previous day within each customer, and keep where the difference is one.
---