Compare Average Survey Ratings of New and Tenured Users in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
An app logs survey impressions and responses in a `survey` table and each user's registration in a `users` table. Compare the average survey rating of new users with that of tenured users, where a user is **new** if they registered within the 30 days ending on the reporting date and **tenured** otherwise.
In the report, the interviewer asks how a new user should be defined; registration within the last 30 days, with every other user tenured, is the definition the candidate proposed and the interviewer accepted. The report leaves "today" implicit, so this version fixes the reporting date at 2025-06-30 to make the result deterministic.
### Input Table(s)
**`survey`**: one row per survey event. The table has no primary key; identical rows are separate events.
| Column | Type | Meaning |
| --- | --- | --- |
| `userid` | INTEGER | The user who was shown or answered the survey; references `users.userid` |
| `date` | DATE | Calendar date of the event |
| `survey_event` | TEXT | `impression` when the survey was shown, `response` when the user answered it |
| `response` | INTEGER | The rating from 1 to 5 on `response` rows; NULL on `impression` rows |
**`users`**: one row per user.
| Column | Type | Meaning |
| --- | --- | --- |
| `userid` | INTEGER | Primary key |
| `reg_date` | DATE | Date the user registered |
| `country` | TEXT | The user's country; not needed for this question |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns exactly one row with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `new_avg_rating` | numeric | Average `response` over all `response` rows of new users, rounded to 2 decimal places |
| `tenured_avg_rating` | numeric | Average `response` over all `response` rows of tenured users, rounded to 2 decimal places |
The result always has exactly one row, so no `ORDER BY` is needed. A column is NULL when its group has no `response` rows, including when the tables are empty.
### Example
`users`:
| userid | reg_date | country |
| --- | --- | --- |
| 1 | 2025-06-10 | US |
| 2 | 2025-06-01 | IN |
| 3 | 2025-05-31 | US |
| 4 | 2024-11-20 | BR |
| 5 | 2025-06-20 | US |
`survey`:
| userid | date | survey_event | response |
| --- | --- | --- | --- |
| 1 | 2025-06-12 | impression | NULL |
| 1 | 2025-06-12 | response | 4 |
| 1 | 2025-06-25 | impression | NULL |
| 1 | 2025-06-25 | response | 5 |
| 2 | 2025-06-03 | impression | NULL |
| 2 | 2025-06-03 | response | 2 |
| 3 | 2025-06-05 | impression | NULL |
| 3 | 2025-06-05 | response | 3 |
| 4 | 2025-01-15 | impression | NULL |
| 4 | 2025-01-15 | response | 5 |
| 4 | 2025-06-18 | impression | NULL |
| 4 | 2025-06-18 | response | 4 |
| 5 | 2025-06-22 | impression | NULL |
Expected result:
| new_avg_rating | tenured_avg_rating |
| --- | --- |
| 3.67 | 4.00 |
- New users are 1, 2 (registered on 2025-06-01, the first day of the window) and 5. Their response rows carry ratings 4, 5 and 2, so the average is 11/3, which rounds to 3.67. User 5 has no response and does not affect the average.
- Tenured users are 3 (registered on 2025-05-31, one day before the window) and 4. Their ratings are 3, 5 and 4, so the average is 12/3 = 4.00.
- Averaging each user's own mean first would give 3.25 and 3.75 instead, which is not what is asked.
### Constraints and Clarifications
- The reporting date is 2025-06-30. A user is new if `reg_date` is from 2025-06-01 through 2025-06-30, inclusive (the 30 days ending on the reporting date); a user whose `reg_date` is on or before 2025-05-31 is tenured.
- No date in either table is after 2025-06-30.
- A user's group depends only on `reg_date`, not on the date of the survey event.
- Every `response` row counts once, so a user who answered several surveys contributes each rating. `impression` rows are not part of either average.
- `survey_event` is always exactly `impression` or `response`, in lowercase. `response` is NULL on every `impression` row and an integer from 1 to 5 on every `response` row; no other column is NULL.
- Every `userid` in `survey` exists in `users`. Users with no `response` rows do not affect the result.
```hint Which rows carry a rating
Only `response` rows have a rating. Check which rows your average actually sees, and that each one is matched to its user's group.
```
Overview: A SQL exercise that joins a survey event table to a users table to compare the average survey rating of new users, registered in the 30 days ending on a fixed reporting date, with that of tenured users. It tests labeling users by registration date, inclusive date boundaries and averaging only the rows that carry a rating.
Read the full Meta Data Scientist interview experience this question came from