LAG(col) is a window function that references the value from the preceding row. Separate users with PARTITION BY and sort session dates ascending with ORDER BY to obtain the previous session date.

LAG(session_date) OVER (
  PARTITION BY user_id       -- Never reference across users
  ORDER BY     session_date   -- Older sessions become preceding rows
) AS prev_session_date        -- The first row has no preceding row → NULL

From user_sessions, calculate the previous session date and revisit interval in days for every session of every user. Return user_id, session_date, prev_session_date, days_since_last, ordered by user_id and session_date ascending.

Expected output (user_id and session_date ascending):

WITH sessions_with_prev AS (
  -- Get the previous session date for each user with LAG
  SELECT
    user_id,
    session_date,
    LAG(session_date) OVER (
      PARTITION BY user_id
      ORDER BY     session_date
    ) AS prev_session_date   -- The first session has no preceding row → NULL
  FROM   user_sessions
)
SELECT
  user_id,
  session_date,
  prev_session_date,
  (session_date - prev_session_date) AS days_since_last  -- date - date = INTEGER (days)
FROM   sessions_with_prev
ORDER BY user_id, session_date;

/*
  Logical execution order:
  1. WITH sessions_with_prev / CTE definition → Define the CTE
  2. FROM user_sessions                     → Read rows
  3. LAG(session_date) OVER (...)           → Evaluate the window function (preserve rows)
  4. FROM sessions_with_prev                → Read rows
  5. SELECT                                 → Evaluate columns (days_since_last)
  6. ORDER BY user_id, session_date         → Sort and output
*/

GAP-AND-ISLAND detects consecutive rows (islands) and gaps. Subtracting the row-number offset within a partition from each row's date produces the same value for consecutive dates, which works as a group key.

-- Consecutive dates produce the same grp
login_date   rn  login_date - (rn-1)  grp
2024-01-01   1   01-01 - 0 days  =  2024-01-01  ← same island
2024-01-02   2   01-02 - 1 day   =  2024-01-01  ← same island
2024-01-03   3   01-03 - 2 days  =  2024-01-01  ← same island
2024-01-05   4   01-05 - 3 days  =  2024-01-02  ← new island (January 4 is missing)

From login_logs, calculate the start date, end date, and number of consecutive days for each user's login streak. Return user_id, streak_start, streak_end, streak_days, ordered by user_id and streak_start ascending.

Expected output (user_id and streak_start ascending):

WITH numbered AS (
  -- Number login dates in ascending order for each user
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY     login_date
    ) AS rn
  FROM   login_logs
),
grouped AS (
  -- Subtract the row-number offset → consecutive dates share grp
  SELECT
    user_id,
    login_date,
    login_date - (rn - 1)  AS grp  -- date - integer = date (island key)
  FROM   numbered
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)         AS streak_days
FROM   grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

/*
  Logical execution order:
  1. WITH numbered / CTE definition        → Define the CTE
  2. FROM login_logs                        → Read rows
  3. ROW_NUMBER() OVER (...)               → Evaluate the window function (preserve rows)
  4. WITH grouped / CTE definition         → Define the CTE (calculate grp)
  5. FROM grouped                           → Read rows
  6. GROUP BY user_id, grp                  → Form groups
  7. SELECT                                 → Evaluate aggregates (MIN, MAX, COUNT)
  8. ORDER BY user_id, streak_start        → Sort and output
*/

WITH RECURSIVE is a self-referencing CTE. The anchor member (non-recursive term) returns the first row, and the recursive member references prior results to add rows repeatedly. UNION ALL repeats this process until the WHERE stopping condition is reached.

WITH RECURSIVE date_series AS (
  -- Anchor member: the first row
  SELECT '2024-01-01'::date AS dt
  UNION ALL
  -- Recursive member: advance one day from the prior result
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-05'::date   -- Required stopping condition
)

From users, calculate the daily new-user count and cumulative registered-user count from 2024-01-01 through 2024-01-05. Display days with no registrations as 0 (zero filling). Return date, new_users, cumulative_users, ordered by date ascending.

Expected output (date ascending):

WITH RECURSIVE date_series AS (
  -- Anchor member: generate one row for the aggregation start date
  SELECT '2024-01-01'::date AS dt
  UNION ALL
  -- Recursive member: advance one day until the stopping condition
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-05'::date   -- Required stopping condition
),
daily_new AS (
  -- Count new registrations by day (no row for a day with no registrations)
  SELECT
    registered_at::date  AS dt,
    COUNT(*)              AS new_users
  FROM   users
  GROUP BY registered_at::date
)
SELECT
  ds.dt                         AS date,
  COALESCE(dn.new_users, 0)     AS new_users,        -- Replace NULL with 0 for zero filling
  SUM(COALESCE(dn.new_users, 0)) OVER (
    ORDER BY ds.dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                             AS cumulative_users  -- Running sum through the current row
FROM   date_series ds
LEFT JOIN daily_new dn ON dn.dt = ds.dt
ORDER BY ds.dt;

/*
  Logical execution order:
  1. CTE date_series (recursive):
     Evaluate the anchor (start point)
     Expand dates one day at a time recursively
     Stop when no additional row is produced
  2. CTE daily_new:
     FROM users → GROUP BY registered_at::date → group and count
  3. FROM date_series ds            → Read rows
     LEFT JOIN daily_new dn         → Join while retaining every left-side row
  4. SUM(...) OVER (...)            → Evaluate the window function (preserve rows)
  5. ORDER BY ds.dt                 → Sort and output
*/

NTILE(n) is a window function that divides rows into n buckets and assigns each row a bucket number from 1 to n. It is useful for classifying users into quartiles based on purchase amount.

NTILE(4) OVER (
  ORDER BY total_amount ASC   -- Low amounts are quartile=1; high amounts are quartile=4
) AS quartile

From orders, calculate each user's total purchase amount, classify users into quartiles with NTILE(4), and attach a segment label. Return user_id, total_amount, quartile, segment (Bronze through Platinum), ordered by total_amount ascending.

Expected output (total_amount ascending):

WITH user_totals AS (
  -- Aggregate total purchase amount for each user
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM   orders
  GROUP BY user_id
),
segmented AS (
  -- Sort by total amount ascending and split into four quartiles
  SELECT
    user_id,
    total_amount,
    NTILE(4) OVER (
      ORDER BY total_amount ASC  -- Assign quartile=1 from the lowest amounts
    ) AS quartile
  FROM   user_totals
)
SELECT
  user_id,
  total_amount,
  quartile,
  CASE quartile
    WHEN 1 THEN 'Bronze'
    WHEN 2 THEN 'Silver'
    WHEN 3 THEN 'Gold'
    WHEN 4 THEN 'Platinum'
  END AS segment
FROM   segmented
ORDER BY total_amount;

/*
  Logical execution order:
  1. WITH user_totals / CTE definition → Define the CTE
  2. FROM orders                      → Read rows
  3. GROUP BY user_id                 → Form groups
  4. SELECT                           → Evaluate the aggregate (SUM)
  5. WITH segmented / CTE definition  → Define the CTE
  6. NTILE(4) OVER (...)              → Evaluate the window function (preserve rows)
  7. SELECT                           → Evaluate columns (assign CASE labels)
  8. ORDER BY total_amount            → Sort and output
*/