Coding questions · Airbnb
Find Users with Seven Consecutive Impression Days
Find users with impressions on seven consecutive calendar days using PostgreSQL, accounting for duplicate days and longer activity streaks.
A user qualifies as a super user if they have at least one impression on each day of any run of seven consecutive calendar days. Find every qualifying user using PostgreSQL.
Input Table
impressions
| Column | Type | Meaning |
|---|---|---|
impression_id | INTEGER | Unique impression identifier; primary key |
user_id | INTEGER | Non-null user identifier |
impression_date | DATE | Non-null calendar date of the impression |
Output Contract
Write one read-only PostgreSQL query returning a single column, user_id, with each qualifying user exactly once, ordered by user_id ascending.
Example
Suppose user 10 has impressions on January 1, 2, 3, 4, 5, 6, and 7 of the same year, with two impressions on January 3. User 20 has impressions on January 1, 2, 3, 5, 6, 7, and 8.
Expected result:
| user_id |
|---|
| 10 |
User 20 has seven distinct impression dates, but no seven-day consecutive run because January 4 is missing.
Constraints and Clarifications
- The exercise uses the supplied calendar dates directly; no timestamp-to-time-zone conversion is needed.
- Several impressions on the same day count as one active day for that user.
- A run may cross a month or year boundary.
- A run longer than seven days qualifies, and multiple qualifying runs still produce one user row.
- Search the entire table history; this is not a requirement to be active on the seven days immediately preceding today.
Hint: Compare consecutive active dates
Deduplicate each user's active dates before measuring runs. A streak is a property of consecutive dates, not simply the number of impression rows.
Find Super Users With Seven Consecutive Active Days
Table impressions
| column | type | constraints |
|---|---|---|
| impression_id | INTEGER | PRIMARY KEY |
| user_id | INTEGER | NOT NULL |
| impression_date | DATE | NOT NULL |
Sample data:
| impression_date | impression_id | user_id |
|---|---|---|
| 2025-03-01 | 1 | 105 |
| 2025-03-02 | 2 | 105 |
| 2025-03-03 | 3 | 105 |
| 2025-03-04 | 4 | 105 |
| 2025-03-05 | 5 | 105 |
| 2025-03-06 | 6 | 105 |
| 2025-03-07 | 7 | 105 |
| 2025-03-08 | 8 | 105 |
| 2025-03-09 | 9 | 105 |
| 2025-03-10 | 10 | 105 |
| 2025-04-01 | 11 | 105 |
| 2025-04-02 | 12 | 105 |
| 2025-04-03 | 13 | 105 |
| 2025-04-04 | 14 | 105 |
| 2025-04-05 | 15 | 105 |
| 2025-04-06 | 16 | 105 |
| 2025-04-07 | 17 | 105 |
| 2025-01-01 | 18 | 101 |
| 2025-01-02 | 19 | 101 |
| 2025-01-03 | 20 | 101 |
| 2025-01-04 | 21 | 101 |
| 2025-01-05 | 22 | 101 |
| 2025-01-06 | 23 | 101 |
| 2025-01-07 | 24 | 101 |
| 2025-01-03 | 25 | 101 |
| 2024-12-28 | 26 | 104 |
| 2024-12-29 | 27 | 104 |
| 2024-12-30 | 28 | 104 |
| 2024-12-31 | 29 | 104 |
| 2025-01-01 | 30 | 104 |
| 2025-01-02 | 31 | 104 |
| 2025-01-03 | 32 | 104 |
| 2023-06-01 | 33 | 99 |
| 2023-06-02 | 34 | 99 |
| 2023-06-03 | 35 | 99 |
| 2023-06-04 | 36 | 99 |
| 2023-06-05 | 37 | 99 |
| 2023-06-06 | 38 | 99 |
| 2023-06-07 | 39 | 99 |
| 2024-02-14 | 40 | 99 |
| 2025-01-05 | 41 | 99 |
| 2025-01-10 | 42 | 103 |
| 2025-01-11 | 43 | 103 |
| 2025-01-12 | 44 | 103 |
| 2025-01-13 | 45 | 103 |
| 2025-01-14 | 46 | 103 |
| 2025-01-15 | 47 | 103 |
| 2025-01-11 | 48 | 103 |
| 2025-01-13 | 49 | 103 |
| 2025-01-01 | 50 | 102 |
| 2025-01-02 | 51 | 102 |
| 2025-01-03 | 52 | 102 |
| 2025-01-05 | 53 | 102 |
| 2025-01-06 | 54 | 102 |
| 2025-01-07 | 55 | 102 |
| 2025-01-08 | 56 | 102 |
| 2025-01-01 | 57 | 106 |
| 2025-01-02 | 58 | 106 |
| 2025-01-02 | 59 | 106 |
| 2025-01-04 | 60 | 106 |
Expected output
| user_id |
|---|
| 99 |
| 101 |
| 104 |
| 105 |
Hints
- Deduplicate each user's active dates before measuring runs. A streak is a property of consecutive dates, not simply the number of impression rows.