Katkat
Back

Find Users with Seven Consecutive Impression Days

AirbnbData Engineer · Onsite
MediumData EngineerOnsiteData Manipulation (SQL/Python)

Last updated

Tests your ability to identify consecutive calendar-day activity streaks in PostgreSQL using window functions, date arithmetic, and deduplication.

Problem Statement

At Airbnb, tracking sustained guest and host engagement is crucial for understanding user retention. You are given a table named user_impressions that logs every impression event across listing search results.

Write a PostgreSQL query to find all unique user_ids who have had at least one impression on at least seven consecutive calendar days. If a user has a streak longer than seven days (e.g., 10 consecutive days), they should still be returned, but each qualifying user_id should appear only once in the final output.

Table Schema: user_impressions

Column NameTypeDescription
impression_idBIGINTUnique identifier for each impression event
user_idBIGINTIdentifier of the user viewing the listing
impression_timestampTIMESTAMPTimestamp when the impression occurred (UTC)

Output Format

Return a single column named user_id sorted in ascending order:

Column NameType
user_idBIGINT

Constraints

  • The table may contain multiple impressions for the same user on the same calendar day. These should be treated as a single active day.
  • Calendar days are evaluated in UTC based on impression_timestamp::DATE.
  • Streaks must be continuous calendar days without gaps.
  • 1 <= Number of rows <= 10,000,000

Example

Input

user_impressions:

 impression_id | user_id | impression_timestamp 
---------------+---------+----------------------
 1             | 101     | 2024-03-01 10:15:00
 2             | 101     | 2024-03-01 18:30:00
 3             | 101     | 2024-03-02 09:00:00
 4             | 101     | 2024-03-03 14:22:00
 5             | 101     | 2024-03-04 11:05:00
 6             | 101     | 2024-03-05 16:45:00
 7             | 101     | 2024-03-06 20:10:00
 8             | 101     | 2024-03-07 08:00:00
 9             | 202     | 2024-03-01 12:00:00
 10            | 202     | 2024-03-02 12:00:00
 11            | 202     | 2024-03-04 12:00:00

Output

 user_id 
---------
 101

Explanation:

  • User 101 logged impressions every day from 2024-03-01 through 2024-03-07 (7 consecutive days, with duplicate impressions on 2024-03-01 correctly counted as one day).
  • User 202 was active on March 1, 2, and 4, missing March 3, so their longest streak is only 2 consecutive days.

Hints

Hint 1

Deduplicate the dataset first by extracting distinct pairs of user_id and impression date.

Hint 2

Use either the 'difference between date and row_number()' technique (gaps-and-islands) or a window function like LEAD(..., 6) ordered by date.

Hint 3

Remember to wrap the final query with DISTINCT or GROUP BY to ensure users with streaks longer than 7 days appear only once.

Follow-up questions

  • How does the performance of LEAD/LAG compare with the gaps-and-islands (ROW_NUMBER) pattern when scaling to hundreds of millions of rows?
  • How would you handle local time zones if each impression recorded a listing or user timezone offset?
  • If this query needed to run daily in production, how would you model an incremental aggregate table to avoid scanning the entire historical table?