Skip to content

SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Recipes for questions about days and order of events: login streaks and gaps, sessions, cohorts and retention, funnels, rolling windows, spikes, and year over year.

  • Lesson 49 of 169
  • 24 min read
  • Depth
  • Free to read · No account needed
On this page
  1. Why interviewers ask this
  2. The idea in plain words
  3. Pause and predict
  4. Worked example
  5. Trade-offs
  6. Common misconceptions
  7. How to say it in the interview
  8. Check your understanding
  9. Answers
  10. Summary card: SQL patterns for time
  11. Sources

Before you start

This lesson uses ideas from these earlier lessons. Read them first if any idea is new to you.

01Why interviewers ask this

The hardest SQL questions are about time. "The longest streak of daily logins." "Group clicks into sessions." "Of the customers who first ordered in March, how many came back in April?" "Of the people who viewed, how many paid?" Product teams ask these every day. That is how a business learns if people stay.

A weak answer counts rows and does not see that the question is about order and gaps. A strong answer names the pattern and uses a window function to look at the neighbour row. Then it tests the edges: the first row, a missing day, and two events at the same moment.

At SDE-2 level you should do streaks and a simple cohort. At Senior level you should also do sessions, ordered funnels, rolling windows and year over year, and explain the traps.

02The idea in plain words

All these questions look at rows in time order and compare each row with its neighbours. The tools come from lesson 47: LAG, ROW_NUMBER, running sums and frames. The recipes below put them together. For each one: how to recognise it, the recipe, the trap.

1. Streaks and gaps (islands)

Recognise it: "days in a row", "the longest streak", "weeks in a row", "periods of absence", "missing numbers".

Recipe for a streak: for one user, number the days with ROW_NUMBER(). Subtract that number, as days, from the date. Inside a run of days in a row, the date goes up by 1 and the number goes up by 1. So the result stays the same. A gap changes it. Each distinct result is one island (one run). Group by it and count.

sql
WITH numbered AS (
  SELECT user_id, day, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS rn FROM logins
)
SELECT user_id, day, rn, date(day, '-' || rn || ' days') AS island FROM numbered WHERE user_id = 1 ORDER BY day;
text
user_id  day         rn  island
-------  ----------  --  ----------
      1  2026-03-01   1  2026-02-28
      1  2026-03-02   2  2026-02-28
      1  2026-03-03   3  2026-02-28
      1  2026-03-05   4  2026-03-01
      1  2026-03-06   5  2026-03-01
      1  2026-03-07   6  2026-03-01
      1  2026-03-08   7  2026-03-01
      1  2026-03-09   8  2026-03-01
User 1 logged in on 1, 2 and 3 March, did not log in on 4 March, and logged in again on 5, 6, 7, 8 and 9 March.
A missing day ends one island and starts the next.
User 1 logged in on 1, 2 and 3 March, did not log in on 4 March, and logged in again on 5, 6, 7, 8 and 9 March. The first run of 3 days is island 1. The missing day starts island 2, a run of 5 days, which is the longest streak.

Days 1, 2 and 3 March share one island key, and days 5 to 9 March share another. Now group by the key. The longest streak is the biggest count.

sql
WITH numbered AS (
  SELECT user_id, day, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS rn FROM logins
),
islands AS (
  SELECT user_id, day, date(day, '-' || rn || ' days') AS island FROM numbered
),
runs AS (
  SELECT user_id, MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days FROM islands GROUP BY user_id, island
)
SELECT user_id, MAX(days) AS longest_streak FROM runs GROUP BY user_id ORDER BY user_id;
text
user_id  longest_streak
-------  --------------
      1               5
      2               2
      3               4

Recipe for gaps and absence: with LAG(day), find rows where the day is more than 1 after the previous one. The days away are the difference minus 1. The same works for missing ids: a number more than 1 above the previous one.

sql
WITH seen AS (
  SELECT user_id, day, LAG(day) OVER (PARTITION BY user_id ORDER BY day) AS previous_day FROM logins
)
SELECT user_id, previous_day AS last_seen, day AS back_on, CAST(julianday(day) - julianday(previous_day) AS INTEGER) - 1 AS days_away
FROM seen WHERE julianday(day) - julianday(previous_day) > 1 ORDER BY user_id, day;
text
user_id  last_seen   back_on     days_away
-------  ----------  ----------  ---------
      1  2026-03-03  2026-03-05          1
      2  2026-03-01  2026-03-03          1

Trap: repeated days. If a user can log in twice on one day, remove repeats first with SELECT DISTINCT user_id, day. Otherwise the row number runs ahead of the date and breaks the run. For weeks, see the next recipe.

Weeks in a row. Turn each date into the Monday of its week with date(day, '-6 days', 'weekday 1') (lesson 43), and say that weeks start on Monday. Give each Monday a week number: CAST(julianday(week_start) AS INTEGER) / 7. This is the number of whole weeks since a fixed day, so the next Monday has the next number. Then use the island method on the week numbers. Remove repeated weeks first with SELECT DISTINCT.

sql
WITH weeks AS (
  SELECT DISTINCT user_id, date(day, '-6 days', 'weekday 1') AS week_start FROM logins
),
numbered AS (
  SELECT user_id, week_start, CAST(julianday(week_start) AS INTEGER) / 7 AS week_no,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY week_start) AS rn
  FROM weeks
)
SELECT user_id, week_start, week_no, rn, week_no - rn AS island FROM numbered ORDER BY user_id, week_start;
text
user_id  week_start  week_no  rn  island
-------  ----------  -------  --  ------
      1  2026-02-23   351584   1  351583
      1  2026-03-02   351585   2  351583
      1  2026-03-09   351586   3  351583
      2  2026-02-23   351584   1  351583
      2  2026-03-02   351585   2  351583
      3  2026-03-02   351585   1  351584

Look at user 1. Sunday 1 March belongs to the week that starts on Monday 23 February. So user 1 was active in three weeks, and the three rows share one island number. Now group by it and keep the runs of 3 or more weeks:

sql
WITH weeks AS (
  SELECT DISTINCT user_id, date(day, '-6 days', 'weekday 1') AS week_start FROM logins
),
numbered AS (
  SELECT user_id, CAST(julianday(week_start) AS INTEGER) / 7 AS week_no, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY week_start) AS rn FROM weeks
)
SELECT user_id, COUNT(*) AS weeks_in_a_row FROM numbered GROUP BY user_id, week_no - rn HAVING COUNT(*) >= 3;
text
user_id  weeks_in_a_row
-------  --------------
      1               3

Only user 1 has three weeks in a row. User 2 has two weeks (the week of 23 February and the week of 2 March), and user 3 has one.

2. Sessions

Recognise it: "group the events into sessions", "a new session after 30 minutes without an event", "merge rows that repeat".

Recipe: (1) LAG(at) gives the time of the previous event of the user. (2) A flag is 1 when there is no previous event or the gap is more than 30 minutes (1,800 seconds), and 0 otherwise. (3) A running sum of the flag gives the session number. (4) Group by user and session number. The same flag and running sum splits any list into runs, for example a price that repeats on many rows.

Count the gap in whole seconds, with strftime('%s', at) (lesson 43). Do not use julianday for this. It gives decimals, and a gap of exactly 30 minutes can come out a little above or below 30. We add a small event table. User 1 has ordinary events. Users 2 and 3 have gaps of exactly 30 minutes, to test the edge.

sql
CREATE TABLE page_events (user_id INTEGER, at TEXT);
INSERT INTO page_events VALUES
  (1, '2026-03-10 10:00:00'), (1, '2026-03-10 10:10:00'), (1, '2026-03-10 10:25:00'), (1, '2026-03-10 11:10:00'),
  (1, '2026-03-10 11:20:00'), (1, '2026-03-10 13:00:00'), (1, '2026-03-10 13:05:00'),
  (2, '2026-03-10 10:00:00'), (2, '2026-03-10 10:30:00'), (2, '2026-03-10 11:00:01'), (2, '2026-03-10 11:30:01'),
  (3, '2026-03-10 11:10:00'), (3, '2026-03-10 11:40:00');

First, why the decimal method is wrong. Users 2 and 3 each have one gap of exactly 30 minutes. The decimal method does not give the same answer for them:

sql
SELECT user_id, at,
  CAST(strftime('%s', at) AS INTEGER) - LAG(CAST(strftime('%s', at) AS INTEGER)) OVER (PARTITION BY user_id ORDER BY at) AS seconds_gap,
  ROUND((julianday(at) - LAG(julianday(at)) OVER (PARTITION BY user_id ORDER BY at)) * 24 * 60, 9) AS decimal_minutes
FROM page_events WHERE user_id IN (2, 3) ORDER BY user_id, at;
text
user_id  at                   seconds_gap  decimal_minutes
-------  -------------------  -----------  ---------------
      2  2026-03-10 10:00:00         NULL             NULL
      2  2026-03-10 10:30:00         1800     30.000000224
      2  2026-03-10 11:00:01         1801       30.0166668
      2  2026-03-10 11:30:01         1800     29.999999553
      3  2026-03-10 11:10:00         NULL             NULL
      3  2026-03-10 11:40:00         1800     29.999999553

Three gaps are exactly 30 minutes. The decimal method gives a number a little above 30 for the first one (user 2, 10:00 to 10:30). For the other two it gives a number a little below 30. A test > 30 would start a new session for the first and not for the others. The exact seconds are 1,800 in all three cases. Now the recipe with seconds. A gap of exactly 1,800 seconds does not start a new session, because the rule says "more than".

sql
WITH gaps AS (
  SELECT user_id, at,
    CAST(strftime('%s', at) AS INTEGER) - LAG(CAST(strftime('%s', at) AS INTEGER)) OVER (PARTITION BY user_id ORDER BY at) AS seconds_gap
  FROM page_events
),
flags AS (
  SELECT user_id, at, CASE WHEN seconds_gap IS NULL OR seconds_gap > 1800 THEN 1 ELSE 0 END AS new_session FROM gaps
),
numbered AS (
  SELECT user_id, at, SUM(new_session) OVER (PARTITION BY user_id ORDER BY at) AS session_no FROM flags
)
SELECT user_id, session_no, MIN(at) AS starts, MAX(at) AS ends, COUNT(*) AS events FROM numbered GROUP BY user_id, session_no ORDER BY user_id, session_no;
text
user_id  session_no  starts               ends                 events
-------  ----------  -------------------  -------------------  ------
      1           1  2026-03-10 10:00:00  2026-03-10 10:25:00       3
      1           2  2026-03-10 11:10:00  2026-03-10 11:20:00       2
      1           3  2026-03-10 13:00:00  2026-03-10 13:05:00       2
      2           1  2026-03-10 10:00:00  2026-03-10 10:30:00       2
      2           2  2026-03-10 11:00:01  2026-03-10 11:30:01       2
      3           1  2026-03-10 11:10:00  2026-03-10 11:40:00       2
User 1 has 7 events on 10 March, drawn on a time line from 10:00 to 13:05 with the distances to scale.
Gaps of more than 30 minutes split the events of user 1 into three sessions.
User 1 has 7 events on 10 March, drawn on a time line from 10:00 to 13:05 with the distances to scale. The events at 10:00, 10:10 and 10:25 form session 1. After a gap of 45 minutes, the events at 11:10 and 11:20 form session 2. After a gap of 100 minutes, the events at 13:00 and 13:05 form session 3. A gap of more than 30 minutes starts a new session.

User 2 has two sessions: the gap of 1,800 seconds stays inside a session, and the gap of 1,801 seconds starts a new one. User 3 has one session.

Trap: "more than 30" against "30 or more". The question must say which one, and the test > 1800 or >= 1800 follows from it.

3. Cohorts and retention

Recognise it: "customers who first ordered in March", "how many came back", "new against returning".

Recipe: (1) Find the first month of each customer with MIN. That is the cohort. (2) List each customer with each month they were active. (3) The month index is the number of months between the active month and the first month. (4) For each cohort and month index, count different customers, and divide by the size of the cohort.

sql
WITH activity AS (
  SELECT DISTINCT customer_id, strftime('%Y-%m', ordered_at) AS month FROM orders WHERE status = 'delivered' AND customer_id IS NOT NULL
),
cohorts AS (
  SELECT customer_id, MIN(month) AS cohort FROM activity GROUP BY customer_id
),
indexed AS (
  SELECT a.customer_id, c.cohort,
    (CAST(substr(a.month, 1, 4) AS INTEGER) * 12 + CAST(substr(a.month, 6, 2) AS INTEGER))
    - (CAST(substr(c.cohort, 1, 4) AS INTEGER) * 12 + CAST(substr(c.cohort, 6, 2) AS INTEGER)) AS month_index
  FROM activity AS a JOIN cohorts AS c ON c.customer_id = a.customer_id
)
SELECT cohort, month_index, COUNT(DISTINCT customer_id) AS customers,
  ROUND(100.0 * COUNT(DISTINCT customer_id) / FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (PARTITION BY cohort ORDER BY month_index), 1) AS percent_of_cohort
FROM indexed GROUP BY cohort, month_index ORDER BY cohort, month_index;
text
cohort   month_index  customers  percent_of_cohort
-------  -----------  ---------  -----------------
2026-03            0          5              100.0
2026-03            1          4               80.0
2026-03            2          1               20.0
2026-04            0          1              100.0
2026-04            1          1              100.0
2026-05            0          1              100.0

The share uses the size of the cohort, which is the count at month index 0 (FIRST_VALUE of the count). New against returning revenue uses the same first month. An order is new if its month equals the first month of the customer, and returning if the month is later. Use CASE on that test and SUM the amounts.

sql
WITH firsts AS (
  SELECT customer_id, MIN(strftime('%Y-%m', ordered_at)) AS first_month
  FROM orders WHERE status = 'delivered' AND customer_id IS NOT NULL GROUP BY customer_id
)
SELECT strftime('%Y-%m', o.ordered_at) AS month,
  SUM(CASE WHEN strftime('%Y-%m', o.ordered_at) = f.first_month THEN o.amount ELSE 0 END) AS new_revenue,
  SUM(CASE WHEN strftime('%Y-%m', o.ordered_at) > f.first_month THEN o.amount ELSE 0 END) AS returning_revenue
FROM orders AS o JOIN firsts AS f ON f.customer_id = o.customer_id
WHERE o.status = 'delivered' GROUP BY strftime('%Y-%m', o.ordered_at) ORDER BY month;
text
month    new_revenue  returning_revenue
-------  -----------  -----------------
2026-03         3990                  0
2026-04          340               1570
2026-05          260                640

Trap: a common wrong first try counts orders, not different customers, so a customer who orders twice in one month is counted twice. The first month also depends on your rule. Do cancelled orders count? Does the guest order count? Say it. Month numbers need care across a new year: use year x 12 + month, not the month alone.

4. Funnels

Recognise it: "how many viewed, then added to cart, then paid", "conversion from step to step", "per device".

Recipe: a user reaches a step only if it happens after the earlier step. Find the first time of step 1 for each user. Join to step 2 events later than that and take the first. Do the same for step 3. Count the users at each step, and divide each count by the one before it. We add a small event table.

sql
CREATE TABLE funnel_events (user_id INTEGER, device TEXT, step TEXT, at TEXT);
INSERT INTO funnel_events VALUES
  (1, 'phone', 'view', '10:00'), (1, 'phone', 'cart', '10:05'), (1, 'phone', 'pay', '10:10'),
  (2, 'phone', 'view', '10:00'), (2, 'phone', 'cart', '10:30'),
  (3, 'laptop', 'view', '11:00'), (3, 'laptop', 'pay', '11:05'),
  (4, 'laptop', 'cart', '09:00'), (4, 'laptop', 'view', '10:00'),
  (5, 'phone', 'view', '12:00'),
  (6, 'laptop', 'view', '12:00'), (6, 'laptop', 'cart', '12:10'), (6, 'laptop', 'pay', '12:20'),
  (7, 'laptop', 'view', '13:00'), (7, 'laptop', 'cart', '13:02');
sql
WITH v AS (
  SELECT user_id, device, MIN(at) AS t FROM funnel_events WHERE step = 'view' GROUP BY user_id, device
),
c AS (
  SELECT v.user_id, MIN(e.at) AS t FROM v JOIN funnel_events AS e ON e.user_id = v.user_id AND e.step = 'cart' AND e.at > v.t GROUP BY v.user_id
),
p AS (
  SELECT c.user_id, MIN(e.at) AS t FROM c JOIN funnel_events AS e ON e.user_id = c.user_id AND e.step = 'pay' AND e.at > c.t GROUP BY c.user_id
)
SELECT v.device, COUNT(*) AS viewed, COUNT(c.user_id) AS carted, COUNT(p.user_id) AS paid,
  ROUND(100.0 * COUNT(c.user_id) / COUNT(*), 1) AS view_to_cart,
  ROUND(100.0 * COUNT(p.user_id) / NULLIF(COUNT(c.user_id), 0), 1) AS cart_to_pay
FROM v LEFT JOIN c ON c.user_id = v.user_id LEFT JOIN p ON p.user_id = v.user_id
GROUP BY v.device ORDER BY v.device;
text
device  viewed  carted  paid  view_to_cart  cart_to_pay
------  ------  ------  ----  ------------  -----------
laptop       4       2     1          50.0         50.0
phone        3       2     1          66.7         50.0

User 3 paid without a cart, and user 4 added to the cart before the first view. Neither is counted past the first step. Trap: counting each event type alone (a cart count without the order) over-counts. Use COUNT(c.user_id), not COUNT(*), after a left join (lesson 45).

5. Rolling windows, and spikes

Recognise it: "in the last 7 days", "the 7-day active users", "a spike against the days before".

Recipe: for counts of different users you cannot use COUNT(DISTINCT) as a window, so join a calendar to the events with a date range: l.day BETWEEN date(d.day, '-6 days') AND d.day. For sums and averages with missing dates, use a RANGE frame on a number of days (lesson 47). To compare a day with the days before it, leave out the current row: RANGE BETWEEN 7 PRECEDING AND 1 PRECEDING.

sql
WITH RECURSIVE days(day) AS (
  SELECT '2026-03-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-03-09'
)
SELECT d.day, COUNT(DISTINCT l.user_id) AS active_last_3_days
FROM days AS d LEFT JOIN logins AS l ON l.day BETWEEN date(d.day, '-2 days') AND d.day
GROUP BY d.day ORDER BY d.day;
text
day         active_last_3_days
----------  ------------------
2026-03-01                   2
2026-03-02                   3
2026-03-03                   3
2026-03-04                   3
2026-03-05                   3
2026-03-06                   3
2026-03-07                   2
2026-03-08                   1
2026-03-09                   1

We used 3 days to keep the table small; the pattern for 7 days uses -6 days. A spike against earlier days:

sql
WITH daily AS (
  SELECT date(ordered_at) AS day, SUM(amount) AS revenue FROM orders WHERE status = 'delivered' GROUP BY date(ordered_at)
),
compared AS (
  SELECT day, revenue,
    AVG(revenue) OVER (ORDER BY CAST(julianday(day) AS INTEGER) RANGE BETWEEN 7 PRECEDING AND 1 PRECEDING) AS earlier_average
  FROM daily
)
SELECT day, revenue, ROUND(earlier_average, 1) AS earlier_average FROM compared WHERE revenue > 1.5 * earlier_average ORDER BY day;
text
day         revenue  earlier_average
----------  -------  ---------------
2026-03-18      640            303.3

The first day has no earlier days, so its average is NULL and its test is unknown (lesson 43). It can never show as a spike. Say if that is what you want.

Calendar fill. A day with no rows is missing from the data, so a rolling sum skips it. Join a calendar of days (lesson 46) to the data and use COALESCE(SUM(x), 0) for the empty days.

sql
WITH RECURSIVE days(day) AS (
  SELECT '2026-03-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-03-07'
)
SELECT d.day, COALESCE(SUM(o.amount), 0) AS revenue
FROM days AS d LEFT JOIN orders AS o ON date(o.ordered_at) = d.day AND o.status = 'delivered'
GROUP BY d.day ORDER BY d.day;
text
day         revenue
----------  -------
2026-03-01        0
2026-03-02      440
2026-03-03      200
2026-03-04        0
2026-03-05      360
2026-03-06        0
2026-03-07      440

Trap: "last 7 rows" is not "last 7 days". Say which one is asked.

6. Year over year, and the LAG trap

Recognise it: "compared with the same month last year", "growth since last year".

Recipe: join the table to itself on the same month one year earlier. LAG(x, 12) works only if every month is present. If a month is missing, it picks the wrong row. We add a monthly table with March 2025 missing.

sql
CREATE TABLE monthly (month TEXT PRIMARY KEY, revenue INTEGER);
INSERT INTO monthly VALUES
  ('2025-01', 100), ('2025-02', 110), ('2025-04', 130), ('2025-05', 140), ('2025-06', 150), ('2025-07', 160), ('2025-08', 170),
  ('2025-09', 180), ('2025-10', 190), ('2025-11', 200), ('2025-12', 210),
  ('2026-01', 120), ('2026-02', 125), ('2026-03', 140), ('2026-04', 150);
sql
SELECT m.month, m.revenue,
  LAG(m.revenue, 12) OVER (ORDER BY m.month) AS lag_12_rows,
  (SELECT p.revenue FROM monthly AS p WHERE p.month = strftime('%Y-%m', m.month || '-01', '-1 year')) AS same_month_last_year
FROM monthly AS m ORDER BY m.month DESC LIMIT 4;
text
month    revenue  lag_12_rows  same_month_last_year
-------  -------  -----------  --------------------
2026-04      150          130                   130
2026-03      140          110                  NULL
2026-02      125          100                   110
2026-01      120         NULL                   100

Only the last column is right. In lag_12_rows, January is NULL, February shows the value of January, and March shows the value of February. April is right only by chance. The percent growth is 100.0 * (now - before) / before, with NULLIF on before.

In Postgres and MySQL

  • Date maths is different. Postgres: day - rn works on dates, and day - INTERVAL '1 month' goes back a month. MySQL: DATE_SUB(day, INTERVAL rn DAY).
  • Postgres 11 or newer and MySQL 8 let RANGE use an interval on a date column. In SQLite, order by a number of days.
  • Postgres can do a calendar with generate_series(start, end, '1 day').
  • COUNT(DISTINCT x) inside OVER is not allowed in any of the three.

Want to check your understanding? Try explaining this idea in your own words. You can continue reading first, or ask the AI tutor a question. Sign up free to ask the tutor

03Pause and predict

Pause. Guess first.

User 2 logged in on 1 March, 3 March and 4 March. What is the longest streak? What do the days 3 March and 4 March share that 1 March does not?

Write your guess before you read on. The answer is in the worked example.

04Worked example

Part 1: the longest login streak of each user.

First try (wrong). Count the days of each user.

sql
SELECT user_id, COUNT(*) AS longest_streak FROM logins GROUP BY user_id ORDER BY user_id;
text
user_id  longest_streak
-------  --------------
      1               8
      2               3
      3               4

This counts all logins, with gaps. User 1 shows 8, but the days are 1 to 3 March and 5 to 9 March. The longest run is 5.

Fix. The islands query above gives the streak of each user. This answers the predict box. For user 2, the days 3 and 4 March have the same island key (day minus row number), and 1 March has another. So the longest streak is 2.

Part 2: the cohort of March. The cohort query above shows it. Five customers first ordered in March: customers 1 to 5. In April (month index 1), four of them ordered again. In May (index 2), one did.

Check by hand. Delivered orders in April: order 15 (customer 2), 16 (customer 1), 17 (customer 6), 18 (customer 3) and 20 (customer 4). The March customers among them are 2, 1, 3 and 4: four people, and customer 5 did not order in April. Customer 6 first ordered in April, so customer 6 is in the April cohort.

In the cohort table this is: 5 customers at index 0, 4 at index 1 (80 percent), and 1 at index 2 (20 percent).

05Trade-offs

  • It solves: the time questions of a product team, with plain SQL and no extra tool.
  • It costs: long queries with many steps, easy to get wrong at the edges. Self-joins and window functions on millions of events are slow unless the events have an index on user and time (lesson 36).
  • I would change it when: the same report runs every day on a huge event table. Then I would build a small daily table (one row per user per day) and run these queries on it. It costs extra work to keep that table correct.
  • Name each CTE after its meaning: islands, sessions, cohorts.

06Common misconceptions

"A streak is the number of days a user logged in." That is the count of all logins. A streak is a run of days in a row. Use the island method.

"LAG(x, 12) gives the same month last year." It gives the row 12 rows back. If a month is missing, it is the wrong month. Join on the month one year earlier.

"A funnel is the count of each event." A funnel counts users who reached each step after the earlier step. A user who paid without a cart view is not counted.

07How to say it in the interview

Say it like this

I name the pattern, such as islands or cohorts, and compare each row with its neighbour. Then I test the edges: the first row, a missing day, and events at the same time.

08Check your understanding

  1. In the island method, what stays the same for all days of one streak? What changes after a gap?
  2. Events of a user come at 10:00, 10:20, 10:55 and 11:30. A new session starts after more than 30 minutes. How many sessions are there, and which events start them?
  3. A teammate counts a funnel as COUNT(*) of view events, cart events and pay events. Why is the result wrong? What do you do?

09Answers

Show the answers
  1. The result of the date minus the row number stays the same. After a gap, the date jumps by more than 1 and the row number by 1, so the result changes.
  2. The gaps are 20, 35 and 35 minutes, which are 1,200, 2,100 and 2,100 seconds. The first event always starts a session. 10:55 and 11:30 each follow a gap of more than 1,800 seconds. So there are 3 sessions, started by 10:00, 10:55 and 11:30. Check with the session query:
sql
WITH e(user_id, at) AS (VALUES (1, '2026-03-10 10:00:00'), (1, '2026-03-10 10:20:00'), (1, '2026-03-10 10:55:00'), (1, '2026-03-10 11:30:00')),
gaps AS (SELECT user_id, at, CAST(strftime('%s', at) AS INTEGER) - LAG(CAST(strftime('%s', at) AS INTEGER)) OVER (PARTITION BY user_id ORDER BY at) AS seconds_gap FROM e)
SELECT at, seconds_gap, CASE WHEN seconds_gap IS NULL OR seconds_gap > 1800 THEN 1 ELSE 0 END AS starts_session FROM gaps ORDER BY at;
text
at                   seconds_gap  starts_session
-------------------  -----------  --------------
2026-03-10 10:00:00         NULL               1
2026-03-10 10:20:00         1200               0
2026-03-10 10:55:00         2100               1
2026-03-10 11:30:00         2100               1
  1. It counts events, not users, and it ignores the order. A user can view many times, and can pay without a cart. For each user find the first view, then the first cart after it, then the first pay after the cart. Count users at each step.

10Summary card: SQL patterns for time

One-page summary

  • Streak (islands): date - ROW_NUMBER() is the same inside a run. Group by it and count. Remove repeated days first.
  • Gaps and absence: LAG(day); where the difference is more than 1, days away = difference - 1. Same for missing ids.
  • Sessions: LAG gap, flag 1 if no previous event or gap above the limit, running SUM of the flag is the session number.
  • Cohort: first month with MIN; month index = months between; count different customers; divide by the cohort size. New against returning: month equals first month or not.
  • Funnel: each step must come after the step before it; count users, not events; divide by the previous step.
  • Rolling window: join a calendar with a date range for different users; a RANGE frame in days for sums; 1 PRECEDING to leave out today.
  • Year over year: join on the same month last year. LAG(x, 12) breaks when a month is missing.
  • Trap: rows are not days, and the edge rule must be said.

11Sources

Try what you just learned

Put this idea into practice with a short exercise or a relevant tool. Free to try. No sign-up needed.

More practice. This needs a free account.

Continue learning with the lessons that build on this one.

Have a question about this lesson?

Ask the AI tutor to explain a difficult idea, walk through an example or check your understanding. It uses the lesson to help you work through the topic.

Sign up free to ask the tutor

A free account includes 10 AI tutor questions a day. The lesson remains free to read without an account.

All lessons in Module 7: SQL for interviews

Prefer another topic? Browse all 169 lessons.