Compare Survey Satisfaction for New and Established Users

Problem Statement

Meta's User Experience Research team wants to compare survey satisfaction scores between newly registered users and established users across different countries.

You are provided with two tables: users and surveys.

Tables

users

• user_id (integer, primary key): Unique identifier for the user.
• signup_date (date): The date the user created their account.
• country (varchar): The country code of the user (e.g., 'US', 'GB', 'IN').

surveys

• survey_id (integer, primary key): Unique identifier for the survey submission.
• user_id (integer, foreign key): References users.user_id.
• survey_date (date): The date the survey was taken.
• satisfaction_score (integer): User rating from 1 to 5. Can be NULL if skipped.
• is_test (boolean): TRUE if generated by an internal test account, FALSE otherwise.

Requirements

Write a PostgreSQL query to calculate survey statistics grouped by country and user_cohort under the following rules:

1. Data Cleaning & Filtering:
   • Exclude any test entries (is_test = TRUE).
   • Exclude records where satisfaction_score IS NULL.
   • Exclude anomalous events where survey_date < signup_date.
2. Cohort Classification:
   • New: Surveys completed within 30 days of registration (survey_date - signup_date 30).
3. Metrics Computed:
   • country: User country.
   • user_cohort: Either 'New' or 'Established'.
   • unique_respondents: Count of unique users who submitted at least one valid survey in that cohort.
   • total_responses: Total number of valid survey submissions.
   • avg_satisfaction: Average satisfaction score rounded to 2 decimal places.
4. Ordering: Order the results by country ascending, then user_cohort ascending.

Example Input

users table:

| user_id | signup_date | country |
|---------|-------------|---------|
| 101     | 2024-01-01  | US      |
| 102     | 2024-01-01  | US      |
| 103     | 2024-02-15  | US      |
| 104     | 2024-03-01  | GB      |

surveys table:

| survey_id | user_id | survey_date | satisfaction_score | is_test |
|-----------|---------|-------------|--------------------|---------|
| 1         | 101     | 2024-01-15  | 4                  | false   |
| 2         | 101     | 2024-02-20  | 5                  | false   |
| 3         | 102     | 2024-02-10  | 3                  | false   |
| 4         | 103     | 2024-02-20  | null               | false   |
| 5         | 103     | 2024-02-22  | 2                  | true    |
| 6         | 104     | 2024-03-05  | 5                  | false   |

Example Output

| country | user_cohort  | unique_respondents | total_responses | avg_satisfaction |
|---------|--------------|--------------------|-----------------|------------------|
| GB      | New          | 1                  | 1               | 5.00             |
| US      | Established  | 2                  | 2               | 4.00             |
| US      | New          | 1                  | 1               | 4.00             |

Explanation:

• For GB, user 104 completed survey 6 four days after signup (New, score 5.00).
• For US:
  • User 101 survey 1 is within 14 days (New, score 4).
  • User 101 survey 2 is 50 days after signup (Established, score 5).
  • User 102 survey 3 is 40 days after signup (Established, score 3).
  • User 103 responses are either null score or is_test = true, so both are omitted.
  • Established cohort for US has scores 5 and 3, averaging 4.00 across 2 unique respondents.