Practice Amazon SQL Question | Real Coding Interviews

Quick Summary

Aggregate user signups into calendar-month cohorts and track their subsequent monthly activity.
Calculate retention by dividing distinct active users per activity month by the original cohort size.
Ensure each user counts only once per activity month and filter out activities before signup.

What This Tests

Date grouping and formatting strategies
Handling duplicates within aggregation windows
Cross-cohort denominator calculations
Filtering temporal relationships between datasets

Common Patterns

Aggregation

Inner Join

Deduplication

Date Arithmetic

Table: Users

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| signup_date | date |
+-------------+------+
user_id is the primary key unless the input contains event-level duplicates.

Table: Activity

+---------------+------+
| Column Name   | Type |
+---------------+------+
| activity_id   | int  |
| user_id       | int  |
| activity_date | date |
+---------------+------+

activity_id is the primary key unless the input contains event-level duplicates.

Test Cases

Input

Output

Expected